Adding Identity to an existing column2
June 19, 2017 by Kenneth Fisher
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
Category: Microsoft SQL Server, Partitioning, SQLServerPedia Syndication, T-SQL | Tags: Identity columns, microsoft sql server, Partitioning
2 thoughts on “Adding Identity to an existing column”
Leave a Reply Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
[…] Kenneth Fisher shows a way of working around the difficulty of adding an identity column to an exist…: […]
[…] Kenneth Fisher has a great technique for switching out tables to avoid having to write a complicated…. So let’s try it out with limiting downtime in mind. […]