Identity Column Question

Question

Identity Column Question

tygun

SSCrazy

Points: 2381
More actions
September 11, 2002 at 1:08 pm

#158945

I have a primary key (identity 1,1) in one of my tables. I also have another column with a constraint on it. I just noticed that if I attempt to insert a new column into the table and the entry does not obey the constraint i get an error (this i expect due to the constraint) however, I realize that I lose the next number in the identity. Once I correct the problem and insert I get a different identity # than the one I should have had.
Example. If I have 3 rows in the table I have identities 1, 2, and 3 so the next should be 4, however with the above explanation I would get identity # 5 and 4 would never occur.
Why is this? Is there anything I can do to make it stay in sequence?
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 1

This may not be the exact way SQL Server does it, but it's similar enough to understand what's going on: SQL Server is going through the INSERT to the point of getting the new IDENTITY value and incrementing it. However, the constraint error is causing a rollback of the INSERT.

To make it stay in sequence you can force the insert through a stored procedure which does the work of the constraint. I know sometimes auditors get real nit-picky when it comes to skipped numbers ("Did you delete something?" they always ask). If that's the case, your better off checking before the insert.

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

K. Brian Kelley
@kbriankelley

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 2

This may not be the exact way SQL Server does it, but it's similar enough to understand what's going on: SQL Server is going through the INSERT to the point of getting the new IDENTITY value and incrementing it. However, the constraint error is causing a rollback of the INSERT.

To make it stay in sequence you can force the insert through a stored procedure which does the work of the constraint. I know sometimes auditors get real nit-picky when it comes to skipped numbers ("Did you delete something?" they always ask). If that's the case, your better off checking before the insert.

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

K. Brian Kelley
@kbriankelley

Prakash Heda SSCertifiable Points: 5539 More actions · Answer 3

This is a known problem and it does exists in sybase also.

after all records inserted you can run DBCC CHECKIDENT and it will rectify the identity values....

Cheers,

Prakash

Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA

Herve Roggero Ten Centuries Points: 1230 More actions · Answer 4

You may be really surprised about this (as I was), but there is a way...... actually I found this by mistake.

Create an instead of trigger, and recreate the INSERT statement. That's it.

I created a table with an identity column and a varchar that does not allow NULLs. When no triggers are on it, it performs as described in your question. I loose unassigned IDs whenever I add a new record that violates the unique constraint.

HOWEVER, if I create a super simple instead of trigger, for whatever reason, I no longer loose my IDs... That's the trigger I created:

CREATE TRIGGER tr_test ON [dbo].[Table3]

INSTEAD OF INSERT

AS

-- Retrieve the value I was going to add

-- Note @s-2 may contain a NULL...

Declare @s-2 varchar(10)

SET @s-2 = (SELECT Name FROM Inserted)

-- INSERT it now...

INSERT INTO Table3 VALUES (@s)

-- Et voila! If this fails, the ID won't increase. Amazing.

Herve

Herve Roggero
hroggero@pynlogic.com
MCDBA, MCSE, MCSD
SQL Server Database Proxy/Firewall and Auditing

Jorge Cordero Ten Centuries Points: 1144 More actions · Answer 5

Yes, this is the identity 'normal' behavior. The generated identity is not included in a transaction. A rollback won’t have any effect, so a failed attempt will anyway increase the identity.

quote:
I have a primary key (identity 1,1) in one of my tables. I also have another column with a constraint on it. I just noticed that if I attempt to insert a new column into the table and the entry does not obey the constraint i get an error (this i expect due to the constraint) however, I realize that I lose the next number in the identity. Once I correct the problem and insert I get a different identity # than the one I should have had.
Example. If I have 3 rows in the table I have identities 1, 2, and 3 so the next should be 4, however with the above explanation I would get identity # 5 and 4 would never occur.
Why is this? Is there anything I can do to make it stay in sequence?
Thanks