Home Forums SQL Server 2014 Development - SQL Server 2014 Get next primary key without identity RE: Get next primary key without identity

  • August 2, 2014 at 7:07 pm

    #1734647

    Erland Sommarskog (8/2/2014)

    Jeff Moden (8/2/2014)

    I agree that SEQUENCE has the advantage of being able to cover multiple tables and that IDENTITY requires SET IDENTITY INSERT if you want to update the identity column but, other than that, how is SEQUENCE any less problematic than IDENTITY?

    You cannot update an IDENTITY column at all. You can set explicit values on insert, but if you for some reason want to update the values, you have a headache.

    Also, if you find out later that you want gaps, and decide to roll your own, you can just drop the default that says NEXT VALUE FOR. Whereas with IDENTITY, you need to do the create-new-table/copy-over/drop-old/rename dance.

    With sequences, you don't run the risk to have this pain.

    Ah. Thanks, Erland. I'd forgotten about not being able to UPDATE an IDENTITY column. The last time I had to do such a thing was very, very long ago and I did it using a "poor man's update" of DELETE followed by an INSERT. Fortunately, there were no FKs pointing to that column or we'd have had to do the FK "Polka".

    --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)