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.
