set identity on the column

Question

How can I modify table and set identity on PK column using T-SQL?

thanks for help

Answer 1

You can't modify an existing column to have the IDENTITY "property" - you have to:

• create a new table with the same structure (but with IDENTITY set up),
• turn on IDENTITY_INSERT for this new table,
• insert rows from the old table into the new table,
• drop the old table, and,
• rename the new table to have the old table name.

If there are foreign keys involved, you need to fix those up also.

Answer 2

The problem with most solutions to this question is that they require either adding a new column to the table or completely rebuilding the table.

Both can require large amounts of locking and logging activity which I have always found annoying as this is a metadata only change and shouldn't necessitate touching the data pages at all (Indeed it is possible to update the metadata directly by starting the instance in single user mode and messing around with some columns in sys.syscolpars but this is undocumented/unsupported.)

However the workaround posted on this connect item shows a completely supported way of making this into a metadata only change using ALTER TABLE...SWITCH (credit SQLKiwi)

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 3

Is it the answer you are looking for?

DBCC CHECKIDENT( 
    'DBName.dbo.TableName'
    ,RESEED --[, new_reseed_value ]
)

Example use:

DBCC CHECKIDENT( 
    'DBName.dbo.TableName'
)

Checking identity information: current identity value '1', current column value '1211031236'. DBCC execution completed. If DBCC printed error messages, contact your system administrator.

DBCC CHECKIDENT( 
    'DBName.dbo.TableName'
    ,RESEED --[, new_reseed_value ]
)

Checking identity information: current identity value '1211031236', current column value '1211031236'. DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Answer 4

In fact, you can modify the IDENTITY on a column. Please read through this article http://www.sqlmag.com/article/tsql3/adding-the-identity-property-to-an-existing-column.aspx

It will need a lot more code than ALTER TABLE tab ALTER COLUMN col SET IDENTITY, though

Answer 5

You need to use the ALTER TABLE command - always test first in dev or pre-production!

The example G seems closest to your requirement:

CREATE TABLE dbo.doc_exe ( column_a INT CONSTRAINT column_a_un UNIQUE) ; GO ALTER TABLE dbo.doc_exe ADD

-- Add a PRIMARY KEY identity column. column_b INT IDENTITY CONSTRAINT column_b_pk PRIMARY KEY,

See http://msdn.microsoft.com/en-us/library/ms190273.aspx

Answer 6

Since you can only ignore identity columns for insert, not for update, you'll need an intermediate table. Here's an example:

create table TestTable (pk int constraint PK_TestTable primary key, 
    name varchar(30))
create table TestTable2 (pk int constraint PK_TestTable identity primary key, 
    name varchar(30))
set identity_insert TestTable2 on
insert TestTable2 (pk, name) select pk, name from TestTable
set identity_insert TestTable2 off
drop table TestTable
exec sp_rename 'TestTable2', 'TestTable'