Dird,

I am not aware of any bugs with the generation of identity values. There are 3 ways you can get gaps in identity values:

1. Failed inserts. This causes gaps because of the way identity values are generated. Basically on an insert SQL Server goes out and grabs the next identity value and increments it and then does the insert. If the insert fails and the transaction has to roll back the identity value is not rolled back. Here's a simple script that shows the behavior:

IF OBJECT_ID('dbo.test', 'U') IS NOT NULL

BEGIN

DROP TABLE test;

END

GO

CREATE TABLE test

(

id INT IDENTITY(1, 1) ,

colA TINYINT

);

GO

/* Identity Value is NULL */

DBCC CHECKIDENT('dbo.test', NORESEED);

GO

/* INsert fail because 5000 is bigger than tinyint*/

INSERT INTO test

( cola )

VALUES ( 5000 );

GO

/* Idneityt value is now 1 */

DBCC CHECKIDENT('dbo.test', NORESEED);

GO

/* insert succeds */

INSERT INTO test

( cola )

VALUES ( 5 );

GO

/* Identity Value is now 2 */

DBCC CHECKIDENT('dbo.test', NORESEED);

GO

2. Deletes

3. Manually create the identity value using SET IDENTITY INSERT

ON;. Here's a simple example of that:

IF OBJECT_ID('dbo.test', 'U') IS NOT NULL

BEGIN

DROP TABLE test;

END

GO

CREATE TABLE test

(

id INT IDENTITY(1, 1) ,

colA TINYINT

);

GO

/* IDentity Value is NULL */

DBCC CHECKIDENT('dbo.test', NORESEED);

GO

/* INsert a row*/

INSERT INTO test

( cola )

VALUES ( 5 );

GO

/* Idneityt value is now 1 */

DBCC CHECKIDENT('dbo.test', NORESEED);

GO

/* insert a row setting the id value explicitly */

SET IDENTITY_INSERT dbo.test ON;

INSERT INTO test

( id, cola )

VALUES ( 5000000, 6 );

SET IDENTITY_INSERT dbo.test ON;

GO

/* Identity Value is now 5,000,000 */

DBCC CHECKIDENT('dbo.test', NORESEED);

GO

My guess is that the app has either 1 or 2 going on, creating the gaps.

Identity is not exactly like Oracle's Sequence and in 2012 SQL Server added a SEQUENCE. THis gets quite a few people because no one expects gaps.