A friend had an interesting problem today. A really big table (multiple millions of rows) and no primary key. He then ran into an issue where he had to have one. The easiest thing is to create a new int column with an identity column right? Unfortunately in this case because of the size of the table, the log growth on adding an identity column was too much. So what to do?

Well, it would be nice if we could add an int column, populate it in chunks, then make it an identity column. Unfortunately, you can’t add identity to an existing column.

CREATE TABLE IdentAdd(Col1 char(10), ID INT NOT NULL);
GO
ALTER TABLE IdentAdd ALTER COLUMN ID INT NOT NULL IDENTITY(1,1);
GO

Msg 156, Level 15, State 1, Line 5

Incorrect syntax near the keyword ‘IDENTITY’.

So that doesn’t work, but obviously, I’ve got a way to do it right? Or this would be a really pointless post. Turns out, yet again that the identity property is not one of those things that needs to be identical when doing a partition switch.

-- Add some data so we can see what's going on
INSERT INTO IdentAdd (Col1,ID) VALUES
('a',1),('a',2),('a',3),('a',4),('a',5),('a',6);
GO
-- Create the switch table with the identity column.
-- Make sure the seed is the max id value + the increment.
CREATE TABLE IdentAddSwitch (Col1 char(10), ID INT NOT NULL IDENTITY(7,1));
GO
-- Switch the tables
ALTER TABLE IdentAdd SWITCH TO IdentAddSwitch;
GO
-- Drop the now empty table
DROP TABLE IdentAdd;
GO
-- Rename the switch(ed) table to the correct name
EXEC sp_rename 'IdentAddSwitch','IdentAdd';
GO
-- Confirm that there are rows & the identity column.
SELECT COUNT(1) FROM IdentAdd;
EXEC sp_help IdentAdd;
GO

Filed under: Microsoft SQL Server, Partitioning, SQLServerPedia Syndication, T-SQL Tagged: Identity columns, microsoft sql server, Partitioning