About a week ago, SQL Server Expert, Imran Mohammed, provided a script, which will list all the missing identity values of a table in a database. In this post, I asked my readers if any could write a similar or better script. The results were interesting. While no one provided a new script, my question sparked a very active discussion that is still ongoing.
When providing the script, Imran asked me if I knew of any specific circumstances in which this kind of query could be useful, as he could not think of an instance where it would be necessary to find a missing identity. I was unable to think of a single reason for listing missing identities in a table. I posted Imran’s script on the assumption that someone would come up with an improved script, but as mentioned earlier, nobody did. Instead, we have been able to follow a very interesting discussion on subject of the need, if any, for listing Missing Identity values.
So, the question is this: “Do you know a real-world scenario where a Missing Identity value in any table can create problems?“
I have already received some extremely interesting comments from many experts, and all have posed the above question in one form or another. At this moment, I am still trying to think of an example from my own experience, but have yet to find one. Imran has since come up with one good example. Here is what he and other experts have suggested so far.
Jacob Sebastian – IDENTITY values are not expected to be sequential and there are all chances of having missing identity values, the most common cause is transaction rollbacks.
Simon Worth – The identity column is basically just a random number – even though they come sequentially. A developer making an assumption that the next record inserted will have an identity that is 1 more than the last inserted record. And if this is the case – then there are flaws in the logic of the developer.
Jacob Sebastian – What if the value in my table is 1, 2, 3, 5, 6 etc where “4″ is missing from the sequence. So what is the importance of knowing whether a table has missing identity values or not?
Imran Mohammed – If you use Identity property as your most unique column and Transaction Identifier, then definitely you would want to know why few transaction did not completely, Is there any specific fashion these transaction fails (Can be found out looking at missing values of identity)… Could be helpful to debug.
Now it is your turn. Let us have your thoughts.
Reference : Pinal Dave (https://blog.sqlauthority.com)
6 Comments. Leave new
Nice information. I am a student of Software Engineering. I am very much interested in search papers, but i have no one to guid me. will you help me that how can i start?
Yes I know a real-world scenario where a Missing Identity value in any table can create problems.
At the time of data migration from one server to another server.
see the example:
server1:
table1(ID,Name) where ID is identity
table2(ID,MultipleAddress) where ID is foreignkey from table1.
example Data:
table1:
1, Gangadhar
3, Imran
4, pinaldave
table2:
1, Address1
1, Address2
3, Address1
3, Address2
3, Address3
4, Address1
Now i started migration from server1 to server2
migrated: server1.table1 to server2.table1
The data migrated like below:
1, Gangadhar
2, Imran
3, pinaldave
migrated: server1.table2 to server2.table2
table2:
1, Address1
1, Address2
3, Address1
3, Address2
3, Address3
4, Address1
Now how do you get address for relevent ID.
Gangadhar,
When migrating data with identity values use SET IDENTITY_INSERT IdentityTable ON.
I agree with Ron. Any data migration project should maintain the same Primary Key values. In a serious application there may be hundreds of tables where an ID is linked and if you re-sequence it, the whole data will be a mess.
Hi all,We rececntly encountered the problem in one of the application.
We have a table with identity column. Its referenced by a foreignkey constraint from another table.
Table1(Id,event,time) ID is identity.
Table2(Id,table1_ID,message). Id is identity. talbe1_ID is the foreignkey that references Id from table1.
Our application was getting error messages(below) when they were trying to insert a particular value in table2 because the value was missing in table1.
Error 547, Level 16, State 0, Procedure storedprocedure, Line x, Message: The INSERT statement conflicted with the FOREIGN KEY constraint “FK_table1_ID”. The conflict occurred in database “xxx”, table “dbo.table1”, column ‘Id’..
Later we found out some identity values were missing in the primary tables due to some transactions that were rolled back.
Thanks
Jay
Most applications will obtain a valid key from the table (either by a query or calling SCOPE_IDENTITY() after a new insert) before performing an insert operation to a referenced table. This makes sure that only valid references are inserted to the referenced table.
the question being discussed here is the implications of having non-sequential IDENTITY values. What does it matter if the customer table has identity values ‘1, 2, 3’ and ‘5’. (missing 4). In which real-life scenario some one will assume that customer ‘4’ exists and try to perform an operation?