Identity Fields - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Identity Fields

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-08-15 : 09:10:45

Glen writes "Hi There

I have a table with an Identity field (Seed 1, increment 1). When I insert into the table the Identity field is incremented correctly. However, when the Insert fails because of a check constraint the strangest thing happens. The record is NOT inserted into the table but SQL Server increments the Identity value - this is because when the Insert statement is corrected and executed again (this time without violating the check constraint) the record is inserted but with a value higher than the expected Identity value.

Here's an Example:

Table: Test
TestID (Our Identity field)
TestData (just some other field)

A select from the table before:

TestID TestData
----------------
1 Hello
2 Friend
3 How
4 Are
5 You

The Insert statement:

INSERT Test (TestData) Values ('Viking')

Now, the insert fails because of a check contraint (maybe 'Viking' doesn't exist in KnownVocabulary table) - a select from the table would produce the same results as before (first Select).

Now we correct the INSERT statement (or add the record to the KnownVocabulary table) and then we execute it again - this time it works (check contraint is happy).

Now we select the records from the table again, the results appear similar to the following:

TestID TestData
----------------
1 Hello
2 Friend
3 How
4 Are
5 You
7 Viking

A gap was created without ever deleting records from the table - does SQL server first Insert the record and then use the check contraints to validate the data - decide that the data is bogus and then delete (rollback) the inserted record but somehow 'forget' to undo the identity increment?

Please help!"

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-08-15 : 09:25:44

a) You can use dbcc check_ident('tablename',reseed,n) if you like to reseed the identity to whatever you want.
b) Gaps in identity values shouldn't matter. It's not meaningful data anyway.

Jay White
{0}

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources