SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Setting the Identity Specification to 'Yes' with data in the table


Setting the Identity Specification to 'Yes' with data in the table

Author
Message
SQL_ME_RICH
SQL_ME_RICH
SSC Eights!
SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)

Group: General Forum Members
Points: 966 Visits: 1596
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
SQL_ME_RICH
SQL_ME_RICH
SSC Eights!
SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)

Group: General Forum Members
Points: 966 Visits: 1596
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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search