Post reply

add identity field to existing table with a clustered index

  • January 25, 2011 at 8:38 am

    #233400

    Currently have a prod oltp server which we were going to add an identity column to. It has an existing clustered index on it. Anyone ever run into performance degradation with this approach?

  • January 25, 2011 at 8:41 am

    #1277909

    None of the existing queries should suddenly slow down, if that's what you mean.

    The DDL command will probably need to take a schema lock on the table while it adds the column. Depending on the size, that might be fast or slow, and could block other queries while it finishes. Try it on a dev/test/QA copy of the database first, while running some concurrent queries through a load test or something like that.

    Have you done any testing on it?

    - 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

  • January 25, 2011 at 8:54 am

    #1277933

    Is this add a column or add the identity property to an existing column? If you add the column, are you populating it?

  • January 25, 2011 at 9:04 am

    #1277948

    new column will do diligent testing

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply