Identity Field

  • Is there an upper limtit to an identity field? If so, is there a way around it?

    Thanks,

    CY

  • It is limited by the upper limit of the datatype used.  So the solution is to change the datatype, r even better, choose the correct one from the get go.

  • Our web guy is using an int...and was just wondering if down the line he might encounter problems.

    Thanks

  • Starting from 1, that's over 2 Billion rows inserted.  If you expect way more than that, then I'd suggest using bigint.

  • I've run out of INTs in some situations. Like SMS, constant scan type of stuff.

    Eventually it's an issue. BigINT gives you more space. If you run out, need to move to GUIDs, varchar, etc.

  • Thanks for the input...it's nice to prepare for disasters

    Marty

  • Run out of bigint???

     

    Any case scenario you can present?

  • In the case you do run out of ints, moving to bigint would require you to rebuild the table. Generally this would involve creating a temp table, moving the data while switching identity insert on, setting the new identity seed, deleting the old table, renaming the new one, while taking care of the dependent objects. Not too much though, but a bit more than just altering a table column. Careful planning can however save this future headache.

    Regards,

    Andras


    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • Heh!  Yeah, I'd like to see that, as well

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

Viewing 9 posts - 1 through 9 (of 9 total)

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