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

Identity Column Expand / Collapse
Author
Message
Posted Wednesday, November 17, 2010 1:35 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 9:11 PM
Points: 1,752, Visits: 1,324
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.
Post #1022450
Posted Wednesday, November 17, 2010 1:44 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #1022457
Posted Thursday, November 18, 2010 10:00 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 5, 2011 6:02 AM
Points: 2,242, Visits: 208
it is identity(x,y)

x = start positon
y = value to increament
Post #1023295
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse