IDENTIY COLUMN Property behaviour

  • Hi All,

    I have a table with IDENTITY(1,1), it will generate a sequence number.

    How SQL Server is generating these sequences?

    Thanks,

    🙂

  • http://msdn.microsoft.com/en-us/library/ms186775.aspx

    IDENTITY(1,1), means start at 1 (1,x) and add 1 (x,1) every time a new row is inserted

    1

    2

    3

    4

    5

    6

    7

    IDENTITY(100,200) means start at 100 (100,x) and add 200 (x,200) every time a new row is inserted

    100

    300

    500

    700

    900

    1100

    1300

  • SQL* (6/21/2012)


    Hi All,

    I have a table with IDENTITY(1,1), it will generate a sequence number.

    How SQL Server is generating these sequences?

    Thanks,

    create table Intpk (intslno int identity (1,1), name varchar(100))

    When records are inserted the identity column will start incrementing from 1. Generating the sequence is a SQLserver architecture part.

    When records are deleted the sequence is not reseeded.

    When the table is truncated the table is reseeded with values back to 1 again.

    Still got doubts please refer back good old school book. SQLserver books online.

    Thank you.

  • Don't assume that these identities will always be consecutive though - there may be gaps in the sequence when you look at the data.

    An interesting article with examples of manipulating the identity column - http://www.simple-talk.com/sql/t-sql-programming/identity-columns/.

  • Thank you,

    I am aware of the statements what you have posted,

    but my doubt is how sql server knows the next identity?

    Ex: i have a table with identity, i have inserted 10 records so the identity column will contain 1 to 10 value (I assume that the sql server will check the identity property (seed , increment) and last inserted identity value by looking at the table, based on increment it will add the next identity.)

    Suppose we are inserting one more 11th record into the above table (so the identity column will be having the value 11) if this insert was rolled back then the table will not contain the 11th identity value. If we have inserted one more record into the table this time 12 will be inserted into the identity column.

    How SQL Server knows that the next Identity is 12 instead of 11?

    🙂

  • It's stored in the metadata of the table.

    One other point, don't assume identity columns are unique. There's nothing in the identity property that requires uniqueness, if it has to be unique put a unique or primary key constraint in place

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Hi,

    SQL stores the current identity value generated in last_value column of sys.identity_columns table with this value SQL knows which value to be generated next based the increment_value column.

    --Ravi.

    Regards,
    Ravi.

  • Sys.identity_columns isn't a table, it's a view of the internal metadata.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • When the table is truncated the table is reseeded with values back to 1 again.

    Technically, the table is set back so that the next row inserted gets the initial seed/starting value, which is usually 1 but could be something else.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • Is there a chart somewhere that shows the maximum number of identity values for the data type used when creating an identity field in a table?

    I've searched for this in the BOL but have not had any luck.

    We had someone design tables in a database to use the an identity column of datatype int for the orders and line item sequence numbers.

    If the seed value is 40,000,000 for orders with an increment of 1, how many orders can be created before the field has reached maximum value?

    Thank you.

  • Ellen-477471 (10/2/2012)


    Is there a chart somewhere that shows the maximum number of identity values for the data type used when creating an identity field in a table?

    I've searched for this in the BOL but have not had any luck.

    We had someone design tables in a database to use the an identity column of datatype int for the orders and line item sequence numbers.

    If the seed value is 40,000,000 for orders with an increment of 1, how many orders can be created before the field has reached maximum value?

    Thank you.

    An int can hold a value up to 2,147,483,647, so the number of values left from 40M woud be:

    2,107,483,647

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • Ellen-477471 (10/2/2012)


    Is there a chart somewhere that shows the maximum number of identity values for the data type used when creating an identity field in a table?

    I've searched for this in the BOL but have not had any luck.

    We had someone design tables in a database to use the an identity column of datatype int for the orders and line item sequence numbers.

    If the seed value is 40,000,000 for orders with an increment of 1, how many orders can be created before the field has reached maximum value?

    Thank you.

    It depends on the datatype of the column. Min and max values here: http://msdn.microsoft.com/en-us/library/ms187745.aspx

    If you need a truly huge number of identities in a table, and don't want to go with BigInt because of the storage amount, keep in mind that you can seed an Int Identity column to start at -2,147,483,648, increment by 1, and pretty much double the number of available IDs (as compared to starting at 1, which is the default). That allows for over 4-billion (US billion) rows with unique ID values in that table. Not quite enough for every person on the planet, but still pretty vast.

    BigInt, which takes twice the storage space as Int, can go from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807, which is enough for over 18-pentillion entries. About 2.6-billion rows per person on the planet.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Thank you both. There was an article somewhere referring to the limitations for identity column values with respect to replication. That is what sent me looking.

    The chart answers my concern. We can't use negative numbers but the int will suffice for order and master order numbers.

Viewing 13 posts - 1 through 12 (of 12 total)

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