The best part of any blog is when readers ask each other questions. Better still, is when a reader takes the time to provide a detailed response.
A few days ago, one of my readers, Yasmin, asked a very interesting question:
How we can find the list of tables whose identity was missed (not is sequential order) within the entire database?
A big thank you to SQL Server Expert, Imran Mohammed, for his excellent response to this question. He also provided an extremely impressive script, which is well described and contains inline comments.
We will now see the same example with the solution to the above gap issue. On deleting records, table was reseeded with identity, which was deleted. Download complete SQL Script here.
This script is well worth keeping in your archive for future reference.
If you and use the script, please comment here and let me know about your experience.
If you know any other way that this can be accomplished or have a different solution, please post your comment here, or send me an email and I will publish it with due credit.
Reference : Pinal Dave (https://blog.sqlauthority.com)
41 Comments. Leave new
Very nice script and explanation.
Very interesting script.
I had recently a discussion with Pinal (while travelling to delhi for PASS chapter launch) about cases where missing identity values can create a problem. After a very long discussion we could not really identify any such case.
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.
It will be really interesting if any of the readers here can come up with some use cases where missing identity values create problems. I have seen a lot of people talking about missing identity values and that indicate that there may be some environments where IDENTITY columns are used in a certain manner that they should always be sequential. I am looking forward to know about a few such cases.
Thank
This is very good script.
IDENTITY is creating problem with me from last month.
Thanks
I would love to know what cases have been identified where a missing identity value is causing issues. I really can’t think of a case that this would happen. The identity column is basically just a random number – even though they come sequentially. Is this a case of someone building logic around the next number to be used should be 1 more than the last? I just can’t think how there would be a dependency on a random number being generated by the DB engine and creating problems in any other layer – other than 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 relying on the engine to add an arbitrary number to a record. Identity is not a record number – and should not be treated as such.
i also don’t face any problem yet where missing identity values
create some problem with anything, but yes this script is quite interesting, i never thought of this before. This one is really good.. so now hunt for the issue when missing identity create problems let’s see if we find something like this..
Thanks for script..
Jacob, thank you for pointing this. I think the same way so I was quite curious for what this script can be use in practice (for fun and sql learning is great). Identity is not “row number” column as a lot of beginners like to think ;)
But maybe someone will give us some practice example for identity column “recovery” needs.
Greetz
Its good script. but i am curious to know why Yasmin want this kind of script unless she has any business reason!!!
I am sure its a million dollars question :D
Hey pinal sir ji, Why don’t you open have poll on this!!! it would be interesting to see, if any one in this world has any good reason behind this!!!
I think most people in this thread share a common opinion. Pinal, your idea of writing a follow up post to bring more attention to this discussion, is excellent!
Hi Vijaya Kadiyala,
Yes, we have to know reson to identity identity columns in hole DB.
For example you have some transaction tables with foreignkeys but does not have primary key. Those table performances will be slow at the time of reading the data.
So, you want to find out whose tables does not have either identity or primary keys. At this time this script will be usefull.
Thank you,
Gangadhar.
Gangadhar Naidu,
Well, the question really is why we should bother about missing identity values. What if the value in my table is 1, 2, 3, 5, 6 etc where “4” is missing from the sequence.
Several people opined in this thread that identity values are expected to be ‘non-sequential’. So what is the importance of knowing whether a table has missing identity values or not?
regards
Jacob
i think till then developers don’t make assumptions like that
” Next order must be +1 then previous one ” or “customerid is always in sequence” till then these assumptions are not made by developer. Well still looking for some other problems arouses by Missing or Non-Sequential Identity value in identity columns. :)
Hello All,
Thanks Pinal for keeping this script to download on your blog.
Thanks for those who actually saw this script and gave missed comments.
Now response for all above questions.
I do not want to show off, But to be honest, When I see a question posted in this blog, I don’t give a second thought, Why, What, When, Where …. NO, But I straight away start, working on the question.
Few Questions that I find very good and if I feel I developed a good script for such a question, then I sent that script to Pinal requesting him to publish.
For this specific question, I did not think why user needed this info, I found this question challenging and I gave a solution.
There could be many reasons to find out why we need this info, 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.
~ IM.
Hi Imran,
You are right and most of us do the same on the forums. When I see a post, usually I provide a solution, without asking ‘why’ the OP actually needed it. And I think that is a decent way of supporting the community.
This discussion came not as a criticism to the ‘excellent’ script provided for download. But it was more focusing on a ‘learning’ excercise. IDENTITIES are discussed over a million times in SQL Server blogs. Though identities are looked as a trivial subject, it is not so. We once did a 1 hour Quiz session on IDENTITIES.
The discussion I started was with the intention to see if there are any REAL-LIFE example where a missing identity sequence can really create a problem.
regards
Jacob
Hi,
I couldn’t find any reason why we need to find out missing identity values. I have gone thru various forums but no luck.
Every SQL expert says Why do you care about missing values :D…
Yea also agreed from what Jacob Sir says usually we just start solving the things without asking why people need it, but its good to find a real world example where we really use it and do tell other peoples about it…
Thanks for the great tips. They make life so easy.
Keep up the great work.
i know the details about SQL from this blog. the above question is very useful to me. your blog is very useful to know the answer to that question. thank you so much. In SQl lot of unknown details are there. so i watch this site regularly to know lot.
i know the details about SQL from this blog. the above question is very useful to me. your blog is very useful to know the answer to that question. thank you so much. In SQl lot of unknown details are there. so i watch this site regularly to know lot. SQL server is very useful database when compared with other
Hmmm… it’s overkill.
SELECT ID+1 FROM Tablename T1
WHERE NOT EXISTS
( SELECT ID FROM Tablename T2 WHERE T2.ID = T1.ID+1 )
KISS, isn’t it?;)
// You may add additional check to exclude last value (it equals identcurrent for Tablename)
As for one example where a missing identity column definition can cause problems…if you have a script doing an INSERT INTO you MUST have all of the columns defined in the insert statement or an error will result–EXCEPT if one of the columns is an IDENTITY column. In fact, if this script is inside a stored procedure the sp will not even compile, telling you that a column is missing. Of course, you can screw around in your script generating the next number for an insert for the column and make sure it’s in the list of columns of the INSERT INTO…but setting the column property correctly is sure a lot easier and more efficient.
Now I had a different problem that was solved by this script. I was trying to rebuild an entire database manually. Don’t ask why, I just had to do it. I was able to script out all of the tables, procedures, keys, functions, etc., and import all of the data. But I needed to know what tables in the source db had identity columns. This script gave me the answer. Excellent work!