November 17, 2010 at 1:35 pm
Couple of questions on adding an identity column to an existing table
1. ALTER TABLE, is this the best practice and fastest for a large table (200,000 rows)
2. How is the Identity record assigned, I mean, does it assign 1 to the first record in the table and 2 to the second and so on?
2a. Is there a way to order the records first before assigning the identity record
Thanks
Chris Powell
George: You're kidding.
Elroy: Nope.
George: Then lie to me and say you're kidding.
November 17, 2010 at 1:44 pm
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
November 18, 2010 at 10:00 pm
it is identity(x,y)
x = start positon
y = value to increament
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply