Friday, February 12, 2010

Gaps in identites

Holes in the values of an identity column can cause data discrepencies in come cases as i have found in the past. Currently I am working on an issue with a 20,000 record table, with a current identity value above 180,000. To resolve this issue, I first need to know where the gaps in my data are coming from. The script below will display all of the gaps in my data to allow me to pinpoint when and where the problem may exist. I thought I would share this with the world.

WITH Gaps (ID,RecordNumber)
AS ( SELECT IdentityColumn AS ID,
ROW_NUMBER() OVER(ORDER BY IdentityColumn) AS RecordNumber
FROM dbo.Table )

SELECT g2.ID, g2.ID - g1.ID Gap_Afterwards
FROM Gaps g1
JOIN Gaps g2
ON g2.RecordNumber - 1 = g1.RecordNumber
WHERE g2.ID - g1.ID > 1
ORDER BY g2.ID - g1.ID

No comments:

Post a Comment