Identity Column

  • 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.

  • 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

  • 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