SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On



An Efficient Set-based Solution for Islands and Gaps Expand / Collapse
Author
Message
Posted Thursday, November 11, 2004 2:51 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, January 04, 2010 1:31 PM
Points: 246, Visits: 38
Comments posted to this topic are about the content posted at http://www.sqlservercentra


Post #145928
Posted Tuesday, September 27, 2005 7:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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

Post #223869
Posted Tuesday, October 25, 2005 11:19 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen 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

Post #232096
Posted Wednesday, January 02, 2008 3:04 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

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/
Post #437872
« Prev Topic | Next Topic »


Permissions Expand / Collapse