Missing Values in IDENTITY Column

5

December 9, 2010 by Mike Hillwig

Somebody in the tech support department of our ERP vendor has to be hating me right now. I’m kind of okay with that.

In a recent audit, the auditors wanted to demonstrate the integrity of our ERP system’s audit log and prove it hasn’t been tampered with. Considering the primary key on the table is an IDENTITY column, this should be really simple, right? Nope.

Considering that this is an IDENTITY column, the only way we should have a gap in the sequence is if someone is deleting records or perhaps a deadlock. If someone or something is deleting out of an audit table, I’m going to have a fit. I’m the only person in the company who has both the access to the database back end AND the skills to delete a record from a table.

So how did I find the gaps?
select id
from tablename
where CreateDate BETWEEN @p_startdate and @p_enddate
and id + 1 NOT IN
(select id from tablename
WHERE CreateDate BETWEEN @p_startdate and @p_enddate)

It may not be optimal code, but it served the purpose. With a little more tweaking, I was able to calculate the number of records in the gap, the time of the gap, and even provide a variable number of records before and after the gap. What I found is that it’s mostly the same four processes causing gaps in our audit log and these gaps are rarely more than one second.

Either I’m off my rocker, or somebody has some explaining to do.

5 thoughts on “Missing Values in IDENTITY Column

  1. John Sansom says:

    Sounds like you must be off your rocker Mike 😉

    Dodgy code or even worse tampering with the database, surely not……

  2. Dwii says:

    Im getting the exact same error. It awlyas happens when you try to add a second product to the cart. Im also using SQL Server. Ive commented out the options from the cart for now to get it running, but Im going to need this feature. Has a resolution to this problem been found yet?[]

  3. That’s the thinking of a creative mind

  4. 24/11/2010Anonymousoh my god, sabemos que sim esse blog é um blog humoristico, porém há coisas que realmente nao devem ser contadas assim, por acaso alguem ja viu um medico postar isso? ou dedurar outro medico? eu nao, o mesmo autor do blog postou em seu twitter essa semana que a classe odontologica deve se unir, mas com esse post realmente quis causar é muita discordia na classe  

  5. There is nothing better than following a very long time of, come home and also learn a fresh write-up on your blog site. That restoration helps my mood, tends to make me personally sense much more living. I want to thank what you do for all those!

Leave a Reply

Your email address will not be published. Required fields are marked *