--AddIdentity.sql

/******* REMEMBER TO SCRIPT-OUT THE TABLE FIRST *******/

--1 delete the old column
Alter Table [dbo].[sometable] 
Drop Column [somecolumn]
Go

--2 if it fails ... drop the primary key (or whatever)
ALTER TABLE [dbo].[sometable]
DROP CONSTRAINT PK_sometable
GO

--3 create the new identity column
Alter Table [dbo].[sometable]
Add [somecolumn] Int Identity(1, 1)
Go

--4 restore the primary key (if dropped)
ALTER TABLE [dbo].[sometable]
ADD CONSTRAINT PK_sometable PRIMARY KEY NONCLUSTERED 
(
	[somecolumn] ASC
)

--5 lastly reset the identity seed
DECLARE @nextid INT;
SET @nextid = (SELECT isnull(MAX([somecolumn]),0) FROM [sometable]);
DBCC CHECKIDENT ('dbo.sometable', RESEED, @nextid);
Advertisements

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