RSS Feed

Missing Values in IDENTITY Column


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.