December 17, 2008 at 7:38 am
In SQL Server 2000, I have an identity column, which course auto increments each time a row is added.
Is there an easy way / SQL to see what rows are missing?
I am trying to find the gaps, so if I have the column values 1,2,3,4,5,7,8,9 I am trying to find 6. The table in question has about two hundred thousand rows.
Thanks.
December 17, 2008 at 8:05 am
First of all, please post SQL 2000 topics in the appropriate forum. This is a 2K5 forum. (Don't repost this, just try to post in the right one in the future).
Secondly, Yes, there are easy ways to do this, but what is the end goal of discovering these gaps?
December 17, 2008 at 7:36 pm
As Seth's good question implies, the idea of reusing numbers or recovering missing numbers in an IDENTITY sequence is something you should consider not worrying about at all. Contiguous sequences should not be important to the success of a table with only rare exceptions that are easily rebuilt (Tally, Numbers, and Calendar tables, for example).
That not withstanding, here's a fully supported method of "gap" detection including some test data generation code the demonstrates the code in question against a million rows of data...
First, the test data...
--===== Create and populate a 1,000,000 row test table.
-- Column "MyID" has a range of 1 to 1,000,000 unique numbers starting at 82011000000001
-- Jeff Moden
SELECT TOP 1000000
MyID = IDENTITY(BIGINT,82011000000001,1)
INTO #MyTest
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
--===== A table is not properly formed unless a Primary Key has been assigned
-- Takes about 3 seconds to execute.
ALTER TABLE #MyTest
ADD PRIMARY KEY CLUSTERED (MyID)
--===== Now, let's add a calculated column to display leading zeros in a "field" of 15 characters
-- like the original problem.
ALTER TABLE #MyTest
ADD DisplayMyID AS RIGHT('000000000000000'+CAST(MyID AS VARCHAR(15)),15)
--===== Delete some know rows to demo the gap detection code
-- This deletes 50 rows spaced 2000 apart in the given range
-- to demo small gaps
DELETE #MyTest
WHERE MyID BETWEEN 82011000500001 AND 82011000600000
AND MyID %2000 = 0
-- This deletes 100,000 rows in a given range to demo large gaps
DELETE #MyTest
WHERE MyID BETWEEN 82011000600001 AND 82011000700000
... and then, two slightly different solutions... the details are in the comments in the code...
--===== Find the "gap ranges"
-- Finds leading edge of "islands" and then computes the gaps
-- This assumes that gaps include any whole number greater than 0
SELECT GapStart = (SELECT ISNULL(MAX(b.MyID),0)+1
FROM #MyTest b
WHERE b.MyID < a.MyID),
GapEnd = MyID - 1
FROM #MyTest a
WHERE a.MyID NOT IN (SELECT MyID + 1 FROM #MyTest)
--===== Find the "gap ranges" not including the first gap that starts at 1
-- Finds leading edge of "islands" and then computes the gaps
-- This assumes that gaps include only from the first number on
-- and does so without slowing the code down
SELECT *
FROM (--==== Find all the gaps like before
SELECT GapStart = (SELECT ISNULL(MAX(b.MyID),0)+1
FROM #MyTest b
WHERE b.MyID < a.MyID),
GapEnd = MyID - 1
FROM #MyTest a
WHERE a.MyID NOT IN (SELECT MyID + 1 FROM #MyTest)
)g
WHERE g.GapStart > 1
Both are nasty fast in the presence of the correct usage of a Clustered PK (the default) or Clustered Index.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 17, 2008 at 8:32 pm
Thank you very much for the response. I will try it out.
As to the reason for asking....a client has suggested that they suspect rows are mysteriously disappearing from a table.
Since there is no delete functionality in my app, I figure if I find gaps in the identity column I can validate the allegation and research further based on any patterns.
December 17, 2008 at 11:30 pm
Way cool... finally, a good use for such a thing. Might I suggest you "baseline" the table by saving the output of the query I gave you in a table and then, at a later time or date, you can simply compare the output of another run with the contents of the table.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 18, 2008 at 5:52 am
ak (12/17/2008)
Thank you very much for the response. I will try it out.As to the reason for asking....a client has suggested that they suspect rows are mysteriously disappearing from a table.
Since there is no delete functionality in my app, I figure if I find gaps in the identity column I can validate the allegation and research further based on any patterns.
Keep in mind that deleted rows are not the only reason an identity value would be skipped. Anything that causes a failed insert or transaction rollback (such as a trigger condition failing or error'ing) would also use an identity value.
December 18, 2008 at 1:27 pm
Yes, that makes sense. Thanks again for your help.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply