15 years of experience with Identity columns

33

June 4, 2014 by Kenneth Fisher

  • An identity column is an auto incrementing column
  • An identity column is typically used as a primary key
  • A primary key that’s an identity column is usually a surrogate key
  • A surrogate key is one that is not related to the contents of the row in any way
  • An identity column must be NOT NULL
  • You can tell if a column is an identity column by looking at the is_identity column of sys.columns or using the COLUMNPROPERTY function (TableObjectId, ColumnName, ‘IsIdentity’)
  • An identity column has three parts. Data type, Seed, and Increment
  • The data type of an identity column is typically an INT but can be most numeric data types. ie tinyint, smallint, int, bigint, decimal(p,0), or numeric(p,0)
  • The seed defaults to 1 and is usually 1. It can however been any value that fits in the data type.
  • IDENT_SEED returns the original seed value of a table
  • To change the seed of a table use DBCC CHECKIDENT
  • The increment is how much the identity column increases each time a new row is added
  • If the increment is negative then the identity values do in fact go down
  • Negative increments can be handy if your identity column has reached the max value of the data type. Reseed to 0 (assuming that is where you started) and set your increment to -1.
  • IDENT_INCR returns the increment of a table
  • Identity columns can have gaps in the sequence
  • @@IDENTITY returns the last identity value generated within the current session but ignores scope
  • Don’t use @@IDENTITY unless you know you should
  • SCOPE_IDENTITY returns the last identity value generated within the current session and scope
  • IDENT_CURRENT returns the last identity value generated for a specific table or view
  • Use SCOPE_IDENTITY when you want the identity value for the row you just inserted
  • Use IDENT_CURRENT to get the most recent identity value from a table before you do an insert
  • If you don’t have permissions on the table then IDENT_CURRENT will return NULL — Thanks Anthony
  • Don’t use @@IDENTITY <- Worth repeating twice
  • In general you don’t list the identity column (the insert will fail even if there is a null value being inserted into the column)
  • If you want to insert a specific value into the identity column you have to use the IDENTITY_INSERT setting.
  • SET IDENTITY_INSERT TableName ON
  • To insert a value into the identity column you must list the columns in the insert statement.
  • Make sure you turn IDENTITY_INSERT back off when you are done. Only one table in a session can have IDENTITY_INSERT turned on at a time
  • SET IDENTITY_INSERT TableName OFF
  • When doing a SELECT INTO you can use the IDENTITY function –Thanks SQLAndy & Jeff
    SELECT IDENTITY(Int, 1,1) AS Col_Name INTO TableName 
    FROM sys.databases
  • If you truncate a table it resets the seed. –Thanks Kevin
  • If a table has less than 1000 rows it will be reseeded to 1000 after a system restart — Thanks Anthony
  • In a memory-optimized table, the only allowed value for both seed and increment is 1 — Thanks Ypercube

33 thoughts on “15 years of experience with Identity columns

  1. gserdijn says:

    Nice list.
    You could add: “Using identity columns as PK might incur the wrath of Mr. Celko”. 🙂

    One thing is not correct I think. “To insert a row into a table with an identity column you must list the columns”.

    CREATE TABLE RandomTableName (id INT IDENTITY(1,1), sometext VARCHAR(10), somenumber INT);
    INSERT INTO RandomTableName SELECT ‘TEST’, 1;
    DROP TABLE RandomTableName;
    GO

    One always should list the columns though, but SQL Server won’t mind if one omits it.

    • Actually that was a typo. It was supposed to be “To insert a value into the identity column you must list the columns in the insert statement.” I’ll get it changed. Thanks for pointing it out.

      Mr. Celko and I have disagreed in the past so I’m not to worried about it :). I will say you should use identity columns as the PK only when it’s appropriate. Also that seeing an identity column that isn’t the PK has been fairly unusual in my experience.

  2. SQLAndy says:

    Good list. Might add about using identity in a select into. Another, more distant, would be identity ranges in transactional replication.

    • Would you believe in 15+ years of SQL I’ve never dealt with identity ranges in transactional replication much. Also I’m not entirely sure what you mean by the SELECT INTO? Would you mind commenting or emailing me a couple of bullet points for me to add?

      • SQLAndy says:

        It’s a way to add an identity to a table you’re creating on fly, instead of having to define it first. Something like SELECT identity(int, 1,1) as MyID, name from sys.databases would create the table with MyID as an identity.

        Identity in replication is not bad, system handles for you, but it does some tricks to reserve ranges and if you run out of keys in the range, then inserts stop – has to phone home for new range, or you get to live big and pick one, clean up the mess later!

  3. […] 15 years of experience with identity columns […]

  4. Amichai says:

    what about IDENT_CURRENT (‘tblname’)?

  5. Aalam Rangi says:

    Reblogged this on Erudition as a SQL DBA & Developer and commented:
    A succinct refresher on SQL Server IDENTITY columns.

  6. Kevin says:

    I believe a Truncate Table statement will reset the identity value back to the original seed. Thanks for the list!

  7. Anthony says:

    IDENT_CURRENT() should be used with tremendous caution. Aside from the obvious concurrency problems, unless you have been granted permission on that table (not recommended in a Production environment), the function will return NULL.

    Also, you should be aware of the infamous gaps when the IDENTITY is reseeded by 1000 every time you restart server (SQL Server 2012 and 2014).

  8. ypercube says:

    In a memory-optimized table, the only allowed value for both seed and increment is 1

  9. Thanks Anthony and Ypercube. I’ve added your additions to the list.

  10. Jeff Moden says:

    Great summary article.

    Take a look at the following URL about the identity function, though. The code you list in the article is incorrect. The IDENTITY function only works in a SELECT/INTO and it always requires at least the datatype.

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

  11. Alex Friedman says:

    Better use OUTPUT to get the values.

  12. David says:

    “A primary key that’s an identity column is called a surrogate key”. Not necessarily. A surrogate is a key that is not used as an identifier in the business domain. IDENTITY is frequently used as a surrogate but IDENTITY columns are sometimes also used for business identifiers such as order numbers – i.e. business keys, rather than surrogates. I’m not saying that’s a good idea, IMO it generally isn’t, but we can’t assume that an IDENTITY column is necessarily a surrogate.

  13. […] can remember about batch files (basically a text file that ends with .bat). Everyone seemed to like 15 years of experience with identity columns so I’m doing this in that same format. Also in the same way please feel free to comment with […]

  14. Parthiban says:

    What if two simultaneous(same time) inserts happen into a table with Identity column, will it create different Identities for each inserts or will it reject/fail in case of simultaneous/parallel inserts.

    • Technically there can’t be two simultaneous inserts. Even if it seems like it to us only one can be written at a time. One or the other will get a lock on the page containing the next ID value. It will get that value, write the row, and then release the lock. In the mean time the other insert will be waiting on the lock to be released. Once it has then it will get it’s next identity value.

  15. JezPerth says:

    Is it possible to get the Identity field value before it commits so that a value based on it can be loaded into another field? e.g. Identity field is IncidentN and is about to get the value 234 when the insert commits, and at the same time I want to insert 00000234 into the IncidentID field which is Not Null and Unique. Effectively what I’m asking is at what point does the Identity field get its value. (I’m a MS Access programmer used to having an AutoNumber field value as soon as the user starts to create a new record in a form .. i.e. OnInsert and prior to the commit).

    • I believe the identity value is created during the actual insert. Which means that at best you will need to insert a false value and then update it (even if you use a trigger). Another option is to use a Sequence object. It will let you get the next value before your insert. It’s similar to using an identity column but is not tied to the table and requires some additional manual work. Probably best for what you are trying to do however.

  16. […] while back I wrote a post on everything I knew about identity columns. In it I mentioned the […]

  17. Eduardo Mirandola says:

    Oi! Nenhum BR aqui?

  18. […] .. technically there is no native way to do it. I even went through my notes on identity columns. No […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,646 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: