How can I change the identity property of a column without setting the wrong seed?

I have a column which should have been set as an Identity column but was not.  I need to change the value of the seed to the next available ID.  I have tried as per the sample below which generates an error - is there a way to do this, ideally in one line?

Chris Bray
ALTER TABLE TaxCodes ALTER COLUMN [TaxCodeId] BIGINT NOT NULL IDENTITY ((SELECT Max(TaxCodeId)+1 FROM TaxCodes), 1);

Open in new window

LVL 3
chrisbrayAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
indeed, you are correct. I missed that this feature is possible visibly since sql 2005, in prior versions it did not work that way.
so, good day, I learned something new!

for your question, you won't come around dynamic sql to first query the value, or to reseed (which is what I woud do):
http://technet.microsoft.com/en-us/library/ms176057.aspx

without the reseed value specified, it will take the "max" value already stored...

ALTER TABLE TaxCodes ALTER COLUMN [TaxCodeId] BIGINT NOT NULL IDENTITY (1, 1);
DBCC CHECKIDENT (TaxCodes, RESEED)

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you cannot do it "simply".
to start with: you cannot change a column from non-identity to identity (or back) without dropping/recreating it.
so, you will need to proceed with several steps to do so.
depending on the database "state" (aka is it a production database or a development database), you might want to rename the table, create it with the correct settings, and re-insert the database (check out SET IDENTITY_INSERT ON statement.)
0
 
chrisbrayAuthor Commented:
angelIII:

Yes you can!  This code works for me:

ALTER TABLE TaxCodes ALTER COLUMN [TaxCodeId] BIGINT NOT NULL IDENTITY (1, 1);

The only problem I have is that 1 is not the correct seed...

Chris Bray
0
 
chrisbrayAuthor Commented:
Thank you for the pointer.  It was accurate and to the point, but I was actually using a T-SQL compliant database rather than SQL Server and it does not support DBCC.

I used another workaround, basically rebuilding the table from scratch in a script.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.