Home Forums Article Discussions Article Discussions by Author Discuss content posted by Florian Reischl Fastes way to increment an id column RE: Fastes way to increment an id column

  • September 6, 2009 at 10:06 pm

    #1049965

    This isn't a true 'quirky' update unless there's a clustered index to determine the order of the ID assignment

    I kind of agree and kind of don't. In the code Flo posted, order meant nothing. A clustered index was not required to establish the order. And, it's still a "quirky update" in that a 3 part SET statement was used... if you don't think so, ask an Oracle user. 😉

    Paul White (9/6/2009)

    Jeff Moden (5/17/2009)

    .. if, for some reason, you cannot use IDENTITY and you're stuck with SQL Server 2000 or less which prevents the use of ROW_NUMBER and the like, then the "quirky" update Flo used is absolutely the fastest method available.

    Heh :laugh:

    Heh? Good idea and good code but you forgot to copy things like indexes, privs, restrictions, triggers, and named constraints to the new table. If I recall correctly, you may also need to find and recompile any views due to the table changes but that might only be if you change the number of columns or their datatypes. 😉

    I will admit that even with those additions, the copy job you built may be quicker. I just can't verify your findings for the next week because I won't have access to an SQL computer for that time.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)