IDENTITY Column produces gaps when insert fails

Hi

When an insert statement for a table having an identity column fails, there is a gap in the identity chain. Is this working 'as designed', or is there anything wrong with my configuration? I'm Using SQL 2005 Standard SP1.

Here's the script:

USE tempdb;
GO

-- Create Test Table
CREATE TABLE id_gaps
( id INT IDENTITY(1001,1)
, txt VARCHAR(10) NOT NULL UNIQUE
);
GO

-- Insert some useful data
INSERT INTO id_gaps (txt) VALUES ('Test 01');
INSERT INTO id_gaps (txt) VALUES ('Test 02');
INSERT INTO id_gaps (txt) VALUES ('Test 03');
INSERT INTO id_gaps (txt) VALUES ('Test 04');
GO

-- Insert a duplicate value unsing an explicit Transaction
-- It doesn't work using an implicit Transaction too.
BEGIN TRAN tr1
BEGIN TRY
INSERT INTO id_gaps (txt) VALUES ('Test 04');
COMMIT TRAN tr1;
END TRY
BEGIN CATCH
ROLLBACK TRAN tr1;
END CATCH
GO

-- Insert an additional row
INSERT INTO id_gaps (txt) VALUES ('Test 05');
GO

-- See the content of the table. There is a gap caused of the refused insert statement.
SELECT * FROM id_gaps;

-- Clean up
DROP TABLE id_gaps;

Thank you for any hints.

The following url may help you..

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1010520&SiteID=1

But it is not advisable.... Here i pasted the updated query (not recommanded)

USE tempdb;
GO

-- Create Test Table
CREATE TABLE id_gaps
( id INT IDENTITY(1001,1)
, txt VARCHAR(10) NOT NULL UNIQUE
);
GO

-- Insert some useful data
INSERT INTO id_gaps (txt) VALUES ('Test 01');
INSERT INTO id_gaps (txt) VALUES ('Test 02');
INSERT INTO id_gaps (txt) VALUES ('Test 03');
INSERT INTO id_gaps (txt) VALUES ('Test 04');
GO

-- Insert a duplicate value unsing an explicit Transaction
-- It doesn't work using an implicit Transaction too.
BEGIN TRAN tr1
BEGIN TRY
INSERT INTO id_gaps (txt) VALUES ('Test 04');
COMMIT TRAN tr1;
END TRY
BEGIN CATCH
ROLLBACK TRAN tr1;
END CATCH
GO

IF IDENT_CURRENT('id_gaps') = (Select max(id) from id_gaps)
Begin
SET IDENTITY_INSERT mytable OFF;
Insert INto id_gaps values('Test 05');
End
else
Begin
SET IDENTITY_INSERT id_gaps ON;
Insert INto id_gaps(id,txt) select max(id) + IDENT_INCR('id_gaps'),'Test 05' From id_gaps;
SET IDENTITY_INSERT id_gaps OFF;
End

-- Insert an additional row
INSERT INTO id_gaps (txt) VALUES ('Test 06');
GO

-- See the content of the table. There is a gap caused of the refused insert statement.
SELECT * FROM id_gaps;

-- Clean up
DROP TABLE id_gaps;

|||

It is not possible to maintain complete sequence control when using IDENTITY values. As you have discovered, there are many reasons an INSERT may fail.

If you must absolutely control a numerical sequence, then you should have a 'numbers' table, lock it, get the next number, increment, and then unlock -but only after you are certain that your insert will succeed.

|||

Thank you for these tips

It's not really necessary to have a continuous nubering, i'm just wondering if there's a mistake in my configuration. But i see it's the normal behavior the identity column works.