Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Voice of the DBA

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 – Dropouts and Spaces

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

It 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.MyTable

You’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
GO

That will give me these results:
ident_d
If I now do this:
INSERT mytable DEFAULT VALUES
GO
SELECT * FROM mytable
GO

I get
ident_e
My 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.

Leave a Comment

Please register or log in to leave a comment.