[sql-server] SQL Server, How to set auto increment after creating a table without data loss?

I have a table table1 in SQL server 2008 and it has records in it.

I want the primary key table1_Sno column to be an auto-incrementing column. Can this be done without any data transfer or cloning of table?

I know that I can use ALTER TABLE to add an auto-increment column, but can I simply add the AUTO_INCREMENT option to an existing column that is the primary key?

Below script can be a good solution.Worked in large data as well.

ALTER DATABASE WMlive SET RECOVERY SIMPLE WITH NO_WAIT

ALTER TABLE WMBOMTABLE DROP CONSTRAINT PK_WMBomTable

ALTER TABLE WMBOMTABLE drop column BOMID

ALTER TABLE WMBOMTABLE ADD BomID int IDENTITY(1, 1) NOT NULL;

ALTER TABLE WMBOMTABLE ADD CONSTRAINT PK_WMBomTable PRIMARY KEY CLUSTERED (BomID);

ALTER DATABASE WMlive SET RECOVERY FULL WITH NO_WAIT

Yes, you can. Go to Tools > Designers > Table and Designers and uncheck "Prevent Saving Changes That Prevent Table Recreation".

SQL Server: How to set auto-increment on a table with rows in it:

This strategy physically copies the rows around twice which can take a much longer time if the table you are copying is very large.

You could save out your data, drop and rebuild the table with the auto-increment and primary key, then load the data back in.

I'll walk you through with an example:

Step 1, create table foobar (without primary key or auto-increment):

CREATE TABLE foobar(
    id int NOT NULL,
    name nchar(100) NOT NULL,
)

Step 2, insert some rows

insert into foobar values(1, 'one');
insert into foobar values(2, 'two');
insert into foobar values(3, 'three');

Step 3, copy out foobar data into a temp table:

select * into temp_foobar from foobar

Step 4, drop table foobar:

drop table foobar;

Step 5, recreate your table with the primary key and auto-increment properties:

CREATE TABLE foobar(
    id int primary key IDENTITY(1, 1) NOT NULL,
    name nchar(100) NOT NULL,
)

Step 6, insert your data from temp table back into foobar

SET IDENTITY_INSERT temp_foobar ON
INSERT into foobar (id, name) select id, name from temp_foobar;

Step 7, drop your temp table, and check to see if it worked:

drop table temp_foobar;
select * from foobar;

You should get this, and when you inspect the foobar table, the id column is auto-increment of 1 and id is a primary key:

1    one
2    two
3    three

If you don't want to add a new column, and you can guarantee that your current int column is unique, you could select all of the data out into a temporary table, drop the table and recreate with the IDENTITY column specified. Then using SET IDENTITY INSERT ON you can insert all of your data in the temporary table into the new table.

No, you can not add an auto increment option to an existing column with data, I think the option which you mentioned is the best.

Have a look here.

If you want to do this via the designer you can do it by following the instructions here "Save changes is not permitted" when changing an existing column to be nullable