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

  • 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

  • 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

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply