15 years of experience with Identity columns
51June 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
SQL Studies 
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.
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?
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!
[…] 15 years of experience with identity columns […]
what about IDENT_CURRENT (‘tblname’)?
Excellent addition! I’ll add it.
Would you believe it was in there (I was surprised I’d missed it). I had just fat fingered it and put “SCOPE_CURRENT”. That’s what you get when you copy and paste I guess. Thanks again!
Reblogged this on Erudition as a SQL DBA & Developer and commented:
A succinct refresher on SQL Server IDENTITY columns.
I believe a Truncate Table statement will reset the identity value back to the original seed. Thanks for the list!
Your absolutely right. I’ve added it. Between all of us we may get a definitive list yet 🙂
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).
In a memory-optimized table, the only allowed value for both seed and increment is 1
Thanks Anthony and Ypercube. I’ve added your additions to the list.
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
Thanks! Sorry about the confusion. I’ve never used it before :). It’s corrected. At least I hope I got it right this time.
Better use OUTPUT to get the values.
I agree. I talked about that here: https://sqlstudies.com/2013/06/26/using-output-to-back-up-a-change/ I’m not sure what you mean in this context though?
OUTPUT is the best and safest way to get the identity values.
See for example:
http://support.microsoft.com/kb/2019779
“Microsoft recommends that you do not use either of these functions in your queries when parallel plans are involved as they are not always reliable. Instead use the OUTPUT clause of INSERT statement to retrieve the identity value as shown in the example below.”
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/f7c8b3ee-1b03-46c6-a446-af87a6a2f5ea/tsql-output-clause-vs-scopeidentity
etc.
[…] https://sqlstudies.com/2014/06/04/15-years-of-experience-with-identity-columns/ […]
Great list!
Thanks!
“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.
Very good point. Better to say “A primary key that’s an identity column is usually a surrogate key.” I’ll get it changed. Thanks for the comment!
[…] 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 […]
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.
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.
[…] while back I wrote a post on everything I knew about identity columns. In it I mentioned the […]
Oi! Nenhum BR aqui?
[…] .. technically there is no native way to do it. I even went through my notes on identity columns. No […]
[…] while back I did a checklist of a whole bunch of things I know about identity columns. I’ve been working with them for a long time so it was a pretty long list. If you look in the […]
Does the identity column jump records in SQL Server 2014 or is this issue just with 2012? Thank you!
I haven’t noticed it jump records in 2014 or 2012? Now there is no guarantee that it will be sequential, and there are a number of reasons why it might not be (deletes etc) but I’ve never seen it just randomly skip a range of values.
We have SQL Server 2012. We use a software called INFOR EAM and many entities in EAM are based on Sequences with Identity columns in SQL Server 2012. The last time we restarted the server, our Work Order numbers incremented by 10k. I am trying to find out if upgrading to SQL Server 2014 eliminates this issue.
I’d try to figure out why the increment. I can’t think of any reason why a restart of the server would change the increment of an identity column. Or a sequence for that matter. Is it possible there was a piece of code running when the server was restarted that had requested a range from a sequence?
Microsoft says it is a “feature” of 2012. 😦
Do you have a link for that?
yes! https://social.msdn.microsoft.com/Forums/sqlserver/en-US/cf63d145-7084-4371-bde0-eb3b917c7163/identity-big-jump-100010000-a-feature?forum=transactsql
Thanks a lot for the list. Very helpful. 🙂
Is identity value jumping issue also in SQL server 2008 instance restart?
Honestly, I don’t think it’s changed even in the current versions. I’ll try to get around to adding that to the list though 🙂
And I just realized you were probably asking if that jump also occurred in previous versions of SQL. As I understand it yes, it’s always been this way.
[…] couple of years back I did a list of things I’d learned in my 15 years of experience with identity columns. It’s a pretty good list if I do say so myself. Well, this week I’ve learned something […]
[…] simple as they appear on the surface identity columns are fairly complicated things. BULK INSERT, on the other hand, is pretty complicated even on the […]
[…] Now, in my research for this post I did find where I’d mentioned this function briefly in my somewhat comprehensive identity post. Technically I didn’t mention so much as it was mentioned to me in the comments so I added it […]
[…] the Users table. There are no constraints other than the primary key. The Id column is an identity column with a seed of 1 so presumably there won’t be negative values. (I checked, there is a -1 […]
Is there a function or indicator that IDENTITY_INSERT is turned on, and what table?
Ok, that was a really fun question so I just did a blog post for an answer 🙂 https://sqlstudies.com/2022/06/24/how-do-i-tell-if-identity_insert-is-turned-on-and-if-so-what-table/
[…] I just had someone ask me this question. It’s an interesting question and I had absolutely no clue, so I did a bit of research. It can be done. There are probably other ways to do this (all code related) but here’s the one I came up with. […]