Post reply

The Identity Limit

  • January 20, 2015 at 4:47 am

    #1771370

    for a second i thought i was the only one 😛

  • January 20, 2015 at 5:00 am

    #1771379

    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".

  • January 20, 2015 at 6:04 am

    #1771416

    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]

  • January 20, 2015 at 6:29 am

    #1771421

    Thank you, Steve.

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

  • January 20, 2015 at 6:50 am

    #1771432

    Tsk, tsk.

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

  • January 20, 2015 at 6:51 am

    #1771433

    More tsk, tsk.

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

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

  • January 20, 2015 at 7:08 am

    #1771439

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

    Tom

  • January 20, 2015 at 7:19 am

    #1771445

    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

  • January 20, 2015 at 8:38 am

    #1771481

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

    Points awarded back, answers changed.

  • January 20, 2015 at 10:55 am

    #1771534

    Well, got this one wrong. Hmmm

  • January 21, 2015 at 3:31 am

    #1771733

    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 & Best Regards,
    Hany Helmy
    SQL Server Database Consultant

  • January 21, 2015 at 10:31 am

    #1771925

    Thanks for the question, I learned something today.

  • January 22, 2015 at 2:38 am

    #1772106

    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/

  • January 23, 2015 at 2:09 am

    #1772525

    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

  • April 20, 2016 at 7:21 am

    #1873315

    matthew.flower (1/20/2015)

    The explanation script is definitely wrong - it defines the columns as being numeric(17,0) i.e. 17 digits long and then tries to insert a 19 digit number, which obviously fails.

    Change myid to NUMERIC(38,0) and the script works without error - even going as far as this:

    INSERT dbo.id_test2 (myid) VALUES (99999999999999999999999999999999999990)

    But to prove that Identity is still working I modified the table to include a second column and inserted additional rows letting SQL Server populate the ID column. Only when trying to add the next row after these did an error occur:

    myid b

    99999999999999999999999999999999999990NULL

    999999999999999999999999999999999999911

    999999999999999999999999999999999999922

    999999999999999999999999999999999999933

    999999999999999999999999999999999999944

    999999999999999999999999999999999999955

    999999999999999999999999999999999999966

    999999999999999999999999999999999999977

    999999999999999999999999999999999999988

    999999999999999999999999999999999999999

    Thanks for the visual. It is clear to me that none of the tables we have will ever have an identity value that large. So nothing to worry about.

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

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