Subscribe to this blog
Briefcase
Print
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:
If I now do this:
INSERT mytable DEFAULT VALUES
GO
SELECT * FROM mytable
GO
I get
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.
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.