• Alter Table is the only way to add an identity column to an existing table.

    Other methods involve copying the table (usually to a temp table), dropping it, recreating it, and then adding the data back in. That's not really "adding" a column to an existing table, but it achieves much the same result.

    The advantage to Alter Table is it doesn't require dropping constraints, keys, existing indexes, etc., you just add the column.

    The advantage to the second method is you can specify the order of rows when you re-insert into the new table. HOWEVER, this doesn't guarantee the sequence of the Identity column. It will probably end up the sequence you want, but not necessarily. Setting the insert to MaxDop 0 will limit it to one CPU, which makes it more likely that you'll get your desired sequence, but again does not guarantee it.

    Identity columns aren't meant to guarantee sequence in a table. It's just a way to uniquely identify rows if you haven't got something better.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon