Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Setting the Identity Specification to 'Yes' with data in the table Expand / Collapse
Author
Message
Posted Thursday, January 31, 2013 4:42 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 12:32 PM
Points: 476, Visits: 1,330
Please help settle a debate. I have been told that there is no option to apply the Identity Column setting to your Primary Key'd column when data is residing in the table. Another colleague of mine claims that you can, and swears that he has. Problem is - he is unable to show me this (because every time we try - whether in the table design portion of the GUI in SSMS or through T-SQL - it fails).

Which is correct?

Thank you!

SQL_ME_RICH
Post #1414395
Posted Thursday, January 31, 2013 5:02 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 12:32 PM
Points: 476, Visits: 1,330
Nevermind - I didn't look hard enough. Found this nugget of wisdom from Lowell...

for reference, you cannot alter a column to turn on the identity property once it has been created. you have to rebuild the table, which is exactly what the gui would do behind the scenes.

the GUI will do the following:
· build a new table to the correct specification
· migrate the data fromt he old table to the new.
· drop all the constraints against the original table
· recreate all the constraints against the new table
· drop the original table
· rename the new table to the original name
Post #1414401
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse