Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2009-12-19
4
Medium Priority
?
387 Views
Last Modified: 2012-05-08
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

0
Comment
Question by:chrisbray
  • 2
  • 2
4 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 26086664
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
 
LVL 3

Author Comment

by:chrisbray
ID: 26087377
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 26087544
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
 
LVL 3

Author Closing Comment

by:chrisbray
ID: 31668067
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question