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

Question

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?

Answer 1

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 TRY
BEGIN 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

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

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.

Answer 6

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.

Answer 7

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