You can also find missing dates with a Numbers table more easily than is outlined in the article.

To set up the test:

create table MissingDates (

Date datetime primary key)

go

insert into dbo.missingdates(date)

select dateadd(second, subtime.number, subdate.date)

from

(select dateadd(day, number, '1/1/08') as Date

from common.dbo.Numbers

where number <= 100) SubDate

cross join

(select number

from common.dbo.BigNumbers

where number <= (3600 * 24) - 1) SubTime

go

delete from dbo.missingdates

where date between '1/12/08' and '1/13/08'

go

delete from dbo.missingdates

where date between '1/30/08' and '1/31/08'

This time, I tried my usual Numbers table method:

select dateadd(day, number, '1/1/08')

from common.dbo.numbers

left outer join dbo.missingdates

on date >= dateadd(day, number, '1/1/08')

and date < dateadd(day, number + 1, '1/1/08')

where date is null

number between 0 and 31

------------------

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 1 ms.

(2 row(s) affected)

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'MissingDates'. Scan count 32, logical reads 5638, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Numbers'. Scan count 3, logical reads 52, 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 = 688 ms, elapsed time = 687 ms.

------------------

Then I tried the method recommended in the article:

SELECT

CAST('2008-01-01' AS DATETIME) + Number-1

FROM dbo.GetNumbers(1, 30)

where CAST('2008-01-01' AS DATETIME) + Number-1 not in

(select cast(convert(varchar(100), date, 101) as datetime)

from dbo.missingdates)

(The cast-convert is necessary because without that, it will simply detect missing midnights, not whole missing days.)

I killed that process after it had been running for 3 minutes. Not sure how long it would have taken in all.

Converted it to:

select dateadd(day, number, '1/1/08')

from dbo.getnumbers(1,31)

left outer join dbo.missingdates

on date >= dateadd(day, number-1, '1/1/08')

and date < dateadd(day, number, '1/1/08')

where date is null

Which is pretty much identical to the query used with the Numbers table.

----------------

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 1 ms.

(2 row(s) affected)

Table '#28D10FF3'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'MissingDates'. Scan count 31, logical reads 5418, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, 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 = 673 ms, elapsed time = 366 ms.

-----------------

This time, the total elapsed time was shorter, but the CPU time was nearly identical. One less scan count on the MissingDates table, and the server was able to split the process across more than 1 CPU (the computer I'm testing this on is a dual-core Pentium D).

Some advantage to the CTE, but in a loaded up server, it won't make much difference.