Adding Identity to an existing column

1

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

One thought on “Adding Identity to an existing column

  1. […] Kenneth Fisher shows a way of working around the difficulty of adding an identity column to an exist…: […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,134 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: