The Identity Limit

  • for a second i thought i was the only one 😛

  • Yet another question with a completely wrong answer, to a really simple question. And, it was easy to spot why. The proof, also showing use of a large numeric identity, tested on all version 2005 to 2014:

    create table tbl_test_identity(numeric_id numeric(38,0) identity(1,1), test_int tinyint)

    set identity_insert tbl_test_identity on

    insert tbl_test_identity(numeric_id, test_int)

    values(18446744073709552000, 1)

    insert tbl_test_identity(numeric_id, test_int)

    values(9999999999999999500000000000000000000, 2)

    set identity_insert tbl_test_identity off

    dbcc checkident('tbl_test_identity', RESEED, 9999999999999999000000000000000000000)

    insert tbl_test_identity(test_int)

    values(3)

    select * from tbl_test_identity

    drop table tbl_test_identity

    This DBA says - "It depends".

  • This was removed by the editor as SPAM

  • I guess the point is that there's a misconception about using BIGINT as an identity column and the IDENTITY specification itself.

    Many use INT or BIGINT as identity cols, but in todays big data world, perhap we should use a NUMERIC(38,0) instead 🙂

    Agree that the posed question is abiguous...

    [font="Courier New"]sqlmunkee[/font]
    [font="Courier New"]Bringing joy and happiness via SQL Server since 1998[/font]

  • Thank you, Steve.

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • Tsk, tsk.


    Just because you're right doesn't mean everybody else is wrong.

  • More tsk, tsk.

    There, now I've got the two points.:hehe:


    Just because you're right doesn't mean everybody else is wrong.

  • An identity has no limit itself. It depends on the datatype used for the field.

    Tom

  • OCTom (1/20/2015)


    An identity has no limit itself. It depends on the datatype used for the field.

    Tom

    Yes, but there are a limited number of possible data types and they all have limits, so it's easy to devise an upper limit.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Apologies. Not sure why I had numeric as 17 and not 38 for the scale.

    Points awarded back, answers changed.

  • Well, got this one wrong. Hmmm

  • Steve Jones - SSC Editor (1/20/2015)


    Apologies. Not sure why I had numeric as 17 and not 38 for the scale.

    Points awarded back, answers changed.

    Well, thanx anyway, I am sure the intent of the question was good enough 😉

  • Thanks for the question, I learned something today.

  • After reading the discussion, I guess it's a good thing I didn't have time to answer when the question was first released.

    Got it right, thanks to other people complaining and Steve correcting the question before I got here. 🙂


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • h.tobisch (1/20/2015)


    10 ^38 > 8 ^21 = 2 ^63

    +1

    Great question. Could have considered the above in order to get the question right.

    Igor Micev,
    My blog: www.igormicev.com

Viewing 15 posts - 16 through 30 (of 30 total)

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