Primary Key Identity

Question:
I have installed Service Pack 4 for SQL Server and Service Pack 4 for NT. I still receive this same error. Your Q&A suggests that this problem was fixed in one of the service packs. I suppose the only fix is a scheduled DBCC check on each table in the database?

Answer:
An article (Q163446)in the Microsoft Knowledge Base discusses a problem with the @@identity global variable. Basically, the value can change if other people insert into other. This could cause the wrong values to be inserted into a table.

If the problem is severe enough, consider the cost/benefit side to implementing your own identity scheme by saving the last-used value in a table of your own and bumping up the value after every insert. Besides the cost of changing the tables and codes, you might also run into locking conflicts on the additional table.

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

Recent Articles: