http://www.sqlservercentral.com/blogs/steve_jones/2010/01/27/identity-values-_1320_-dropouts-and-spaces/

Printed 2014/08/30 06:28AM

Identity Values – Dropouts and Spaces

By Steve Jones, 2010/01/27

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.

Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.