If the only purpose is to find "gaps" in sequential numbers, I've not found anything faster than the following... first, a little bit of test data...

--===== Create and populate a...

ang (11/14/2008)

---

It worked!!!! Actually, the key was using dynamic sql. This is the end result..

DECLARE @ServerName nvarchar(30)

DECLARE @sql nvarchar(100)

select @ServerName = '[server-a\testdb]'

set @sql = @servername + '.master.dbo.xp_fixeddrives'

exec(@sql)

Thank you...

Got it... thanks, Clint.

Heh... you won't have 75% left because I'm...

Lynn Pettis (11/14/2008)

---

Jeff,

Just gotta know, has anyone every called you an anti-rbar fanatic? This seems to be the main area you really become "excited" about. And NOT that...

K. Brian Kelley (11/14/2008)

---

Let's look at this more closely. Good point, I didn't catch it was only showing the first iteration of both loops. Let's look at the cost per...

Heh... sorry... wrong graphic... SQL Server 2k5 won't even show the ACTUAL execution plan for anything inside a WHILE loop and the estimated execution plan shows everything with a cost...

Oh yeah... here's the proof that the Actual Execution Plan for the WHILE loop contains costs for only 1 row or, in this case, one character... that's what the wicked...

Also, I never suggested making a Tally table on the fly... a permanent Tally table is really the way to go, in most cases. Exception to that rule is...

Ok... here's the code I used to compare the WHILE loop solution you came up with against the Tally table version I posted. I had to increase the size...

Just to be sure, you're calling the WHILE loop the "procedural" code, right? If you're giving the "win" to the WHILE loop based on what you found in the...

Heh... you don't actually believe the costs in the execution plan, do you? The execution plan for WHILE loops and other forms of RBAR are only for the FIRST...

I believe I'd put a default of ' ' on the nullable column... that way, if an explicit NULL is inserted on the column, it will be NULL... if the...

BWAA-HAAA! CBAC would work! Maybe "Character Reading And Processing" fits better. 😉

Dooza (11/14/2008)

---

Msg 208, Level 16, State 1, Line 2

Invalid object name 'Master.dbo.SysColumns'.

I think my master database is screwed 🙁

We did have some problems a few weeks back with a maintenance...