Advertisement
Advertisement


Adding an identity to an existing column


Question

I need to change the primary key of a table to an identity column, and there's already a number of rows in table.

I've got a script to clean up the IDs to ensure they're sequential starting at 1, works fine on my test database.

What's the SQL command to alter the column to have an identity property?

2011/08/23
1
450
8/23/2011 12:15:31 AM

Accepted Answer

You can't alter the existing columns for identity.

You have 2 options,

  1. Create a new table with identity & drop the existing table

  2. Create a new column with identity & drop the existing column

Approach 1. (New table) Here you can retain the existing data values on the newly created identity column. Note that you will lose all data if 'if not exists' is not satisfied, so make sure you put the condition on the drop as well!

CREATE TABLE dbo.Tmp_Names
    (
      Id int NOT NULL
             IDENTITY(1, 1),
      Name varchar(50) NULL
    )
ON  [PRIMARY]
go

SET IDENTITY_INSERT dbo.Tmp_Names ON
go

IF EXISTS ( SELECT  *
            FROM    dbo.Names ) 
    INSERT  INTO dbo.Tmp_Names ( Id, Name )
            SELECT  Id,
                    Name
            FROM    dbo.Names TABLOCKX
go

SET IDENTITY_INSERT dbo.Tmp_Names OFF
go

DROP TABLE dbo.Names
go

Exec sp_rename 'Tmp_Names', 'Names'

Approach 2 (New column) You can’t retain the existing data values on the newly created identity column, The identity column will hold the sequence of number.

Alter Table Names
Add Id_new Int Identity(1, 1)
Go

Alter Table Names Drop Column ID
Go

Exec sp_rename 'Names.Id_new', 'ID', 'Column'

See the following Microsoft SQL Server Forum post for more details:

How to alter column to identity(1,1)

2020/07/23
487
7/23/2020 11:24:30 AM

In SQL 2005 and above, there's a trick to solve this problem without changing the table's data pages. This is important for large tables where touching every data page can take minutes or hours. The trick also works even if the identity column is a primary key, is part of a clustered or non-clustered index, or other gotchas which can trip up the the simpler "add/remove/rename column" solution.

Here's the trick: you can use SQL Server's ALTER TABLE...SWITCH statement to change the schema of a table without changing the data, meaning you can replace a table with an IDENTITY with an identical table schema, but without an IDENTITY column. The same trick works to add IDENTITY to an existing column.

Normally, ALTER TABLE...SWITCH is used to efficiently replace a full partition in a partitioned table with a new, empty partition. But it can also be used in non-partitioned tables too.

I've used this trick to convert, in under 5 seconds, a column of a of a 2.5 billion row table from IDENTITY to a non-IDENTITY (in order to run a multi-hour query whose query plan worked better for non-IDENTITY columns), and then restored the IDENTITY setting, again in less than 5 seconds.

Here's a code sample of how it works.

 CREATE TABLE Test
 (
   id int identity(1,1),
   somecolumn varchar(10)
 );

 INSERT INTO Test VALUES ('Hello');
 INSERT INTO Test VALUES ('World');

 -- copy the table. use same schema, but no identity
 CREATE TABLE Test2
 (
   id int NOT NULL,
   somecolumn varchar(10)
 );

 ALTER TABLE Test SWITCH TO Test2;

 -- drop the original (now empty) table
 DROP TABLE Test;

 -- rename new table to old table's name
 EXEC sp_rename 'Test2','Test';

 -- update the identity seed
 DBCC CHECKIDENT('Test');

 -- see same records
 SELECT * FROM Test; 

This is obviously more involved than the solutions in other answers, but if your table is large this can be a real life-saver. There are some caveats:

  • As far as I know, identity is the only thing you can change about your table's columns with this method. Adding/removing columns, changing nullability, etc. isn't allowed.
  • You'll need to drop foriegn keys before you do the switch and restore them after.
  • Same for WITH SCHEMABINDING functions, views, etc.
  • new table's indexes need to match exactly (same columns, same order, etc.)
  • Old and new tables need to be on the same filegroup.
  • Only works on SQL Server 2005 or later
  • I previously believed that this trick only works on the Enterprise or Developer editions of SQL Server (because partitions are only supported in Enterprise and Developer versions), but Mason G. Zhwiti in his comment below says that it also works in SQL Standard Edition too. I assume this means that the restriction to Enterprise or Developer doesn't apply to ALTER TABLE...SWITCH.

There's a good article on TechNet detailing the requirements above.

UPDATE - Eric Wu had a comment below that adds important info about this solution. Copying it here to make sure it gets more attention:

There's another caveat here that is worth mentioning. Although the new table will happily receive data from the old table, and all the new rows will be inserted following a identity pattern, they will start at 1 and potentially break if the said column is a primary key. Consider running DBCC CHECKIDENT('<newTableName>') immediately after switching. See msdn.microsoft.com/en-us/library/ms176057.aspx for more info.

If the table is actively being extended with new rows (meaning you don't have much if any downtime between adding IDENTITY and adding new rows, then instead of DBCC CHECKIDENT you'll want to manually set the identity seed value in the new table schema to be larger than the largest existing ID in the table, e.g. IDENTITY (2435457, 1). You might be able to include both the ALTER TABLE...SWITCH and the DBCC CHECKIDENT in a transaction (or not-- haven't tested this) but seems like setting the seed value manually will be easier and safer.

Obviously, if no new rows are being added to the table (or they're only added occasionally, like a daily ETL process) then this race condition won't happen so DBCC CHECKIDENT is fine.

2019/09/10

You cannot alter a column to be an IDENTITY column. What you'll need to do is create a new column which is defined as an IDENTITY from the get-go, then drop the old column, and rename the new one to the old name.

ALTER TABLE (yourTable) ADD NewColumn INT IDENTITY(1,1)

ALTER TABLE (yourTable) DROP COLUMN OldColumnName

EXEC sp_rename 'yourTable.NewColumn', 'OldColumnName', 'COLUMN'

Marc

2017/01/10

There is cool solution described here: SQL SERVER – Add or Remove Identity Property on Column

In short edit manually your table in SQL Manager, switch the identity, DO NOT SAVE changes, just show the script which will be created for the changes, copy it and use it later.

It is huge time saver, because it (the script) contains all the foreign keys, indices, etc. related to the table you change. Writting this manually... God forbid.

2013/12/13

Consider to use SEQUENCE instead of IDENTITY.

IN sql server 2014 (I don't know about lower versions) you can do this simply, using sequence.

CREATE SEQUENCE  sequence_name START WITH here_higher_number_than_max_existed_value_in_column INCREMENT BY 1;

ALTER TABLE table_name ADD CONSTRAINT constraint_name DEFAULT NEXT VALUE FOR sequence_name FOR column_name

From here: Sequence as default value for a column

2018/11/23

Simple explanation

Rename the existing column using sp_RENAME

EXEC sp_RENAME 'Table_Name.Existing_ColumnName' , 'New_ColumnName', 'COLUMN'

Example for Rename :

The existing column UserID is renamed as OldUserID

EXEC sp_RENAME 'AdminUsers.UserID' , 'OldUserID', 'COLUMN'

Then add a new column using alter query to set as primary key and identity value

ALTER TABLE TableName ADD Old_ColumnName INT NOT NULL PRIMARY KEY IDENTITY(1,1)

Example for Set Primary key

The new created column name is UserID

ALTER TABLE Users ADD UserID INT NOT NULL PRIMARY KEY IDENTITY(1,1)

then Drop the Renamed Column

ALTER TABLE Table_Name DROP COLUMN Renamed_ColumnName

Example for Drop renamed column

ALTER TABLE Users DROP COLUMN OldUserID

Now we've adding a primarykey and identity to the existing column on the table.

2014/12/29

Source: https://stackoverflow.com/questions/1049210
Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Email: [email protected]