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 (
Col1 INT NOT NULL,
Col2 VARCHAR(10) NOT NULL,
CONSTRAINT pkAddIdentity PRIMARY KEY (Col1)
);
/*
A temporary table, with the schema identical to the AddIdentity table,
except that the Col1 column has the IDENTITY property
*/
CREATE TABLE AddIdentityTemp (
Col1 INT NOT NULL IDENTITY(1,1),
Col2 VARCHAR(10) NOT NULL,
CONSTRAINT pkAddIdentityTemp PRIMARY KEY (Col1)
);
— Insert test data
INSERT INTO AddIdentity (Col1, Col2)
VALUES (1, ‘a’);
— Switch data into temporary table
ALTER TABLE AddIdentity SWITCH TO AddIdentityTemp;
— Look at the switched data
SELECT Col1, Col2
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
DBCC CHECKIDENT (AddIdentity, RESEED);
— Insert test data
INSERT INTO AddIdentity (Col2)
VALUES (‘b’);
— Confirm that a new IDENTITY value has been generated
SELECT Col1, Col2
FROM AddIdentity;
© 2019 Microsoft. All rights reserved.