Identity Column

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server Newbies » Identity Column

Identity Column

Rate Topic

Display Mode

Topic Options

Author

Message

Meet George Jetson

Posted Wednesday, November 17, 2010 1:35 PM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 7:47 AM
Points: 1,271, Visits: 1,203

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

GSquared

Posted Wednesday, November 17, 2010 1:44 PM

SSCoach

Group: General Forum Members
Last Login: Yesterday @ 1:55 PM
Points: 15,442, Visits: 9,571

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

Shanalise

Posted Thursday, November 18, 2010 10:00 PM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, July 05, 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 »

Permissions