Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

Identity values are very useful, but there can be misconceptions about the way in which they work. One of the more interesting things that I see people assuming is that an identity value will ensure you have a continuous range of values for that column. They assume that this code:CREATE TABLE MyTable ( id INT IDENTITY(1,1) ) GO INSERT mytable DEFAULT VALUES INSERT mytable DEFAULT VALUES INSERT mytable DEFAULT VALUES INSERT mytable DEFAULT VALUES INSERT mytable DEFAULT VALUES GO SELECT * FROM mytable GO DROP TABLE dbo.MyTableIt will, and it will get give you a list of 5 rows, with continuous values. Assuming that this pattern continues in your application, you will have a continuous list of values. What about this code?CREATE TABLE MyTable ( id INT IDENTITY(1,1) ) GO INSERT mytable DEFAULT VALUES INSERT mytable DEFAULT VALUES INSERT mytable DEFAULT VALUES INSERT mytable DEFAULT VALUES INSERT mytable DEFAULT VALUES GO UPDATE mytable SET id = 12 WHERE id = 3 go SELECT * FROM mytable GO DROP TABLE dbo.MyTableYou’ll actually get an error, since you can’t specify identity values directly, but you can do this:CREATE TABLE MyTable ( id INT IDENTITY(1,1) ) GO INSERT mytable DEFAULT VALUES INSERT mytable DEFAULT VALUES INSERT mytable DEFAULT VALUES INSERT mytable DEFAULT VALUES INSERT mytable DEFAULT VALUES GO DELETE mytable WHERE id = 3 GO SELECT * FROM mytable GOThat will give me these results:If I now do this:INSERT mytable DEFAULT VALUES GO SELECT * FROM mytable GOI getMy next value doesn’t fill in the gaps.An identity value is strictly a “what’s the next number” process. It does nothing to manage the existing values, and it does nothing about filling in gaps. That’s important to know, and to be aware of when designing your database around identity values.

Comments

Posted by Dugi on 28 January 2010

Very nice and short explanation...for beginner users

Posted by Tim Mitchell on 28 January 2010

Good info. Another thing that surprises people is that the rollback of a transaction doesn't reset the identity setting to its pre-transaction value. So if you insert ten rows into your sample table inside a transaction, and then roll back the transaction, the next row inserted will be ten greater than the last, resulting in a 10-number gap even though no rows were deleted.

Posted by Vijaya Kadiyala on 28 January 2010

It is short and simple...

Posted by Steve Jones on 28 January 2010

Got another blog coming on the transaction items. That is one that people seem to miss often.