|
|
|
SSC Veteran
 
Group: General Forum Members
Last Login: Monday, January 04, 2010 1:31 PM
Points: 246,
Visits: 38
|
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Sunday, February 21, 2010 12:50 PM
Points: 9,
Visits: 48
|
|
Hi there Is there any way to apply the same principle to a series of records with start date / end dates and then to determine where the gaps are. Regards Peet
|
|
|
|
|
Ten Centuries
Group: General Forum Members
Last Login: Today @ 9:13 AM
Points: 1,287,
Visits: 596
|
|
OK - here's a question - why are holes and islands such a bad thing in an identity field? Should the always be 'closed up' ? Mark
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: Today @ 3:30 PM
Points: 20,022,
Visits: 13,596
|
|
I happened to be doing a little research on different methods for finding "gaps" and ran across this article. Here's a much faster way to find gaps... and, it doesn't use Temp Tables, Table Variables, or UDF's... No, there's nothing missing. This is the "find gap" code in it's entirety. :P
SELECT GapStart = (SELECT ISNULL(MAX(b.ID),0)+1
FROM yourtable b
WHERE b.ID < a.ID),
GapEnd = ID - 1
FROM yourtable a
WHERE a.ID - 1 NOT IN (SELECT ID FROM yourtable)
AND a.ID - 1 > 0
My poor "old" computer is a 1.8GHz single cpu P5 and was built in 2002. This method is so fast that even when I return the gaps to the screen in the Grid mode, I still get at least 7 times the performance as follows...
Upper bound Author's Method This Method Performance Ratio
----------- --------------- ----------- -----------------
1,000,000 89 11 8.1:1
2,000,000 188 19 9.9:1
3,000,000 354 33 10.7:1
4,000,000 490 45 10.8:1
5,000,000 648 54 12.0:1
6,000,000 799 57 14:0:1
Of course, all times assume that you have a decent index, preferably a clustered one, on the ID column.
I agree with Mark's statement above, though... it's normally a "Bozo-no-no" to even think about reusing ID's especially if they're of the IDENTITY flavor.
Peet,
If your dates are "whole" dates, this method will also work for your date problem. Just make sure you have an index on the date column.
--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."
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
|
|
|
|
