Adding the IDENTITY property to a column of an existing table

Until SQL Server 2005, it was not possible to alter a column in an existing table to add the IDENTITY property. To achieve that, it was necessary to create a new table with an IDENTITY column, and move the data into that table. For large tables, this could be problematic.

With the introduction of table partitioning in SQL Server 2005, there is a neat solution to this problem. As described in Transferring Data Efficiently by Using Partition Switching, the schemas of the source and destination tables in the ALTER TABLE … SWITCH statement have to match exactly (loosely speaking - the specific requirements are quite detailed). However, and that's the key part here, the topic says that "The IDENTITY property is not considered." In other words, the target table can have the IDENTITY property on a column, even though the source table does not have it. Therefore, we can switch the data from the source table into the target table, which is a very fast metadata-only operation, and gain the IDENTITY property without having to physically move data from one table to another. The target table and associated constraints can then be renamed to match the original source table.

Here's a script that demonstrates this approach:

A table without an IDENTITY column. 
We want to add the IDENTITY property to the Col1 column
CREATE TABLE AddIdentity (

A temporary table, with the schema identical to the AddIdentity table, 
except that the Col1 column has the IDENTITY property
CREATE TABLE AddIdentityTemp (

-- Insert test data
INSERT INTO AddIdentity (Col1Col2)
VALUES (1'a');

-- Switch data into temporary table
ALTER TABLE AddIdentity SWITCH TO AddIdentityTemp;

-- Look at the switched data
FROM AddIdentityTemp;

-- Drop the original table, which is now empty
DROP TABLE AddIdentity;

-- Rename the temporary table, and all constraints, to match the original table
EXEC sp_rename 'AddIdentityTemp''AddIdentity''OBJECT';
EXEC sp_rename 'pkAddIdentityTemp''pkAddIdentity''OBJECT';

-- Reseed the IDENTITY property to match the maximum value in Col1

-- Insert test data
INSERT INTO AddIdentity (Col2)
VALUES ('b');

-- Confirm that a new IDENTITY value has been generated
FROM AddIdentity;

Comments (4)

  1. saravana says:

    It's nice and very usefull article.

  2. Nice and very useful article..

    Except that it would be difficult in case of tables with more number of columns..

  3. ManishKumar1980 says:

    Nice article.

    Great :)

  4. megha says:

    very much useful

Skip to main content