Missing sequence number
-
WayneS (8/26/2010)
Jeff Moden (8/26/2010)
drew.allen (8/25/2010)
This is not a complete solution. It SEEMS to work here, because all of the gaps in your sample data are one ID. If you had gaps larger than one ID, this would only give the first one in the gap. For a complete solution, you need to use the tally table as others have suggested.Drew
So, do you have a Tally table that goes from -2,147,483,648 to 2,147,483,647 because that's the only way the proposed Tally table methods are going to provide a supposedly "complete" solution for this problem. 😉
ALTER TABLE dbo.TALLY ALTER COLUMN N bigint
now, you need a MUCH bigger range.
But the point is - you need to base your solution on what you'll be working with. Tally tables of just 10,000 rows may be completely adequate for your use - but only you will know that.
Heh... the point is that finding missing values in a sequence using a join with a Tally table is the wrong way to do this no matter what the range of numbers is. There are much faster and much more scalable methods. (I'll post it as soon as I hear from the OP).
I'm waiting to hear back from the OP of what he wants to do if a couple of million row were to be returned because I'd like to know why a range of missing numbers couldn't be used. I also want to know why were passing this stuff back and forth as comma delimited values instead of returning the answer as a set based result set.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff Moden (8/26/2010)
I'm waiting to hear back from the OP of what he wants to do if a couple of million row were to be returned because I'd like to know why a range of missing numbers couldn't be used. I also want to know why were passing this stuff back and forth as comma delimited values instead of returning the answer as a set based result set.Now returning a range of numbers, one row per inclusive range, would be nice.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2 -
WayneS (8/27/2010)
Jeff Moden (8/26/2010)
I'm waiting to hear back from the OP of what he wants to do if a couple of million row were to be returned because I'd like to know why a range of missing numbers couldn't be used. I also want to know why were passing this stuff back and forth as comma delimited values instead of returning the answer as a set based result set.Now returning a range of numbers, one row per inclusive range, would be nice.
WITH CTE AS (
SELECT Barcode,
Barcode-ROW_NUMBER() OVER(ORDER BY Barcode) AS rnDiff
FROM dbo.ExistingBarCodes
),
Grps AS (
SELECT MIN(Barcode) AS GrpStart,
MAX(Barcode) AS GrpEnd,
ROW_NUMBER() OVER(ORDER BY MIN(Barcode)) AS rn
FROM CTE
GROUP BY rnDiff)
SELECT s.GrpEnd+1 AS GapStart,e.GrpStart-1 AS GapEnd
FROM Grps s
INNER JOIN Grps e ON e.rn=s.rn+1 AND e.GrpStart>s.GrpEnd;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Jeff,
Yes, Here the purpose is I will get bill_ids as a string from application.. I want which id's are missing in between.. Just for reporting Purpose which we are missed we need logic..
Really I feel great for the replies.
Trying to build new logic with your suggestions.
Thanks,
Ram
🙂
-
Sorry for late reply to every body.. I just need missing numbers only which i got as a text from application..
In fact, For time being I am getting id's around 1000, But in future I may not sure of number id s will get.
🙂
-
Mark-101232 (8/27/2010)
WayneS (8/27/2010)
Jeff Moden (8/26/2010)
I'm waiting to hear back from the OP of what he wants to do if a couple of million row were to be returned because I'd like to know why a range of missing numbers couldn't be used. I also want to know why were passing this stuff back and forth as comma delimited values instead of returning the answer as a set based result set.Now returning a range of numbers, one row per inclusive range, would be nice.
WITH CTE AS (
SELECT Barcode,
Barcode-ROW_NUMBER() OVER(ORDER BY Barcode) AS rnDiff
FROM dbo.ExistingBarCodes
),
Grps AS (
SELECT MIN(Barcode) AS GrpStart,
MAX(Barcode) AS GrpEnd,
ROW_NUMBER() OVER(ORDER BY MIN(Barcode)) AS rn
FROM CTE
GROUP BY rnDiff)
SELECT s.GrpEnd+1 AS GapStart,e.GrpStart-1 AS GapEnd
FROM Grps s
INNER JOIN Grps e ON e.rn=s.rn+1 AND e.GrpStart>s.GrpEnd;
Thanks. Doesn't work very well without some readily consumable test data, though. If you can provide some, I'll test your method against my own and post the results.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Ram:) (8/27/2010)
Jeff,Yes, Here the purpose is I will get bill_ids as a string from application.. I want which id's are missing in between.. Just for reporting Purpose which we are missed we need logic..
Really I feel great for the replies.
Trying to build new logic with your suggestions.
Thanks,
Ram
Am at work... if all you want to know is missing ID's then I'll have a treat for you later tonight (if I ever get out of work). 😛
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
You folks already have the split thing pretty much down pat so I'll just demo another method for detecting and displaying "gaps". I haven't tested every method in the world for detecting "gaps" but the following seems to be the quickest of the group that I have. First, let's build some test data. Of course, we'll build a LOT of test data and we'll build in not only a lot of gaps, but we'll build in some big gaps as well. As usually, the details are in the comments in the code...
--===== Create and populate a 1,000,000 row test table.-- This first SELECT creates a range of 1 to 1,000,000 unique numbers starting at 10,000,001
SELECT TOP 1000000
MyID = ISNULL(CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) + 10000000 AS BIGINT),0)
INTO #MyTest
FROM Master.sys.All_Columns ac1,
Master.sys.All_Columns ac2
-- This second SELECT creates a range of 1 to 1,000,000 unique numbers starting at 82,011,000,000,001
UNION ALL
SELECT TOP 1000000
MyID = ISNULL(CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) + 82011000000000 AS BIGINT),0)
FROM Master.sys.All_Columns ac1,
Master.sys.All_Columns ac2
--===== Create the quintessential Primary Key
-- Takes about 3 seconds to execute.
ALTER TABLE #MyTest
ADD PRIMARY KEY CLUSTERED (MyID)
--===== 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 82011000400001 AND 82011000500000
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
Here's the gap-detection code that I'm talking about... despite its looks, it's nasty fast and is the reason why I said not to use a Tally table to find missing sequence numbers...
--===== Find the "gap ranges" --This takes 3 seconds on my 8 year old machine
-- Finds trailing edge of "islands" and then computes the gaps
-- This assumes that gaps include any whole number greater than 0
SELECT GapStart = ISNULL((SELECT MAX(m.MyID+1) FROM #MyTest m WHERE m.MyID < lo.MyID),1),
GapEnd = lo.MyID-1
FROM #MyTest lo
WHERE lo.MyID NOT IN (SELECT MyID+1 FROM #MyTest)
AND lo.MyID > 1
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff Moden (8/27/2010)
You folks already have the split thing pretty much down pat so I'll just demo another method for detecting and displaying "gaps". I haven't tested every method in the world for detecting "gaps" but the following seems to be the quickest of the group that I have. First, let's build some test data. Of course, we'll build a LOT of test data and we'll build in not only a lot of gaps, but we'll build in some big gaps as well. As usually, the details are in the comments in the code...--===== Find the "gap ranges" --This takes 3 seconds on my 8 year old machine
Now you have to know I was going to test this. 😉
All I can say is... you really need to get a new machine! :w00t:
Table '#MyTest_____________________________________________________________________________________________________________000000000007'. Scan count 55, logical reads 8185, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 951 ms, elapsed time = 955 ms.
VERY impressive sir. My hat's off to you. Yet another bit of fabulous code to keep in the toolbox.
I have to admit, my first thought was to use the tally table.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2 -
WayneS (8/27/2010)
Jeff Moden (8/27/2010)
You folks already have the split thing pretty much down pat so I'll just demo another method for detecting and displaying "gaps". I haven't tested every method in the world for detecting "gaps" but the following seems to be the quickest of the group that I have. First, let's build some test data. Of course, we'll build a LOT of test data and we'll build in not only a lot of gaps, but we'll build in some big gaps as well. As usually, the details are in the comments in the code...--===== Find the "gap ranges" --This takes 3 seconds on my 8 year old machine
Now you have to know I was going to test this. 😉
All I can say is... you really need to get a new machine! :w00t:
Table '#MyTest_____________________________________________________________________________________________________________000000000007'. Scan count 55, logical reads 8185, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 951 ms, elapsed time = 955 ms.
VERY impressive sir. My hat's off to you. Yet another bit of fabulous code to keep in the toolbox.
I have to admit, my first thought was to use the tally table.
Judging by the posts on this thread, I'd have to say it was everyone's first instinct. Obviously, I love Tally table code but it just isn't the right thing to do for this type of code even if it's a Tally CTE like the split function has in it.
Anyway, thanks for the feedback and I didn't mean to keep folks waiting. Oh... yeah... almost forgot. I love programming on this old machine... if I can make it run fast on the old machine, things pretty much cook when someone runs it on a fast machine. Helps me work a real treat for folks. 🙂
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff Moden (8/27/2010)
I love programming on this old machine... if I can make it run fast on the old machine, things pretty much cook when someone runs it on a fast machine. Helps me work a real treat for folks. 🙂Edit: Unless you're working with Phil Factor... then you'll seem to swap back and forth...
I understand... I used to deliberately run SQL server (6.5/7) on underpowered NT servers for just this reason. But then, once MS made it where you could install SS on a workstation... well, I haven't even fired up those servers in 7+ years.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2 -
Hey folks. Wayne S. found the occurance of a bug in the code that I previously posted on this thread. I've repaired that code on this thread. Apologies for the error.
The error in my code was that if the number 1 was present whether a gap started at 2 or not, a gap of 1,0 was listed which, of course, is incorrect. That's the good part... the bad part is that I swear I tested for this. :blush: I hope I'm older than Wayne so I can blame it on that. 😀
Thanks for the testing, Wayne. +100 and I owe you at least one cold one when we finally meet. Heh... and, no, I'm not talking about a cold pork chop, either. 😀
@Wayne... I'll let you tell them where else you found a similar error.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply