SQL Server, How to set auto increment after creating a table without data loss?

Changing the IDENTITY property is really a metadata only change. But to update the metadata directly requires starting the instance in single user mode and messing around with some columns in sys.syscolpars and is undocumented/unsupported and not something I would recommend or will give any additional details about.

For people coming across this answer on SQL Server 2012+ by far the easiest way of achieving this result of an auto incrementing column would be to create a SEQUENCE object and set the next value for seq as the column default.

Alternatively, or for previous versions (from 2005 onwards), the workaround posted on this connect item shows a completely supported way of doing this without any need for size of data operations using ALTER TABLE...SWITCH. Also blogged about on MSDN here. Though the code to achieve this is not very simple and there are restrictions - such as the table being changed can't be the target of a foreign key constraint.

Example code.

Set up test table with no identity column.

CREATE TABLE dbo.tblFoo (bar INT PRIMARY KEY,filler CHAR(8000),filler2 CHAR(49))INSERT INTO dbo.tblFoo (bar)SELECT TOP (10000) ROW_NUMBER() OVER (ORDER BY (SELECT 0))FROM master..spt_values v1, master..spt_values v2

Alter it to have an identity column (more or less instant).

BEGIN TRY;    BEGIN TRANSACTION;    /*Using DBCC CHECKIDENT('dbo.tblFoo') is slow so use dynamic SQL to      set the correct seed in the table definition instead*/    DECLARE @TableScript nvarchar(max)    SELECT @TableScript =     '    CREATE TABLE dbo.Destination(        bar INT IDENTITY(' +                      CAST(ISNULL(MAX(bar),0)+1 AS VARCHAR) + ',1)  PRIMARY KEY,        filler CHAR(8000),        filler2 CHAR(49)        )        ALTER TABLE dbo.tblFoo SWITCH TO dbo.Destination;    '           FROM dbo.tblFoo    WITH (TABLOCKX,HOLDLOCK)    EXEC(@TableScript)    DROP TABLE dbo.tblFoo;    EXECUTE sp_rename N'dbo.Destination', N'tblFoo', 'OBJECT';    COMMIT TRANSACTION;END TRYBEGIN CATCH    IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;    PRINT ERROR_MESSAGE();END CATCH;

Test the result.

INSERT INTO dbo.tblFoo (filler,filler2) OUTPUT inserted.*VALUES ('foo','bar')

Gives

bar         filler    filler2----------- --------- ---------10001       foo       bar      

Clean up

DROP TABLE dbo.tblFoo

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 ONINSERT 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    one2    two3    three

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