Wednesday, September 19, 2007

Iterate Over a List of Dates using a Numbers table in SQL

by gstark via Gregg Stark on SQL Server on 9/19/2007 1:40:30 PM

Recently I was helping Steve archive off a bunch of data from one of his tables. Unfortunately he didn't have a ton of diskspace to use some of the techniques I normally would.  Steve recently posted a solution using powershell to do this.  I told him about an easy way to do this using a cursor and a numbers table and he told me to blog it.  This is an excellent use of a numbers table which if you don't have one of these in your database, you should create one. 

Here is a script to create a numbers table.

CREATE TABLE dbo.Numbers
(
    Number int IDENTITY(1, 1) PRIMARY KEY
)
GO

declare @MaxNumber int
set @MaxNumber = 65535
WHILE 1 = 1
BEGIN
    INSERT INTO dbo.Numbers DEFAULT VALUES
    
    IF scope_identity() = @MaxNumber 
    BEGIN
        BREAK
    END
END
GO

So basically Steve wanted to delete all data out of a table one day at a time for a date range and then shrink down the log to avoid .  The easiest way I could think of to do this is to create a cursor of all the dates in that date range and then loop through them.  Notice the join to the number table for all Numbers between 0 and the datediff of the start and end date.  Here is the code...

declare @startdate datetime
declare @enddate datetime
-- Declare the dates 
set @startdate = '01/01/2007'
set @enddate = '10/01/2007'

declare @period datetime

declare datecursor cursor forward_only 
    for Select dateadd(d,Number,@startdate) from Numbers
          where Number <= datediff(d, @startdate, @enddate)
          order by Number
open datecursor

while (1=1)
begin
  fetch next from datecursor into @period
  if @@fetch_status <> 0
    break;
  DELETE stats WHERE period = @Period

  BACKUP LOG stats WITH NO_LOG
  DBCC SHRINKFILE (stats_log, 2)        

end
close datecursor
deallocate datecursor

Here is a link to a good post on other uses of a numbers table.  As always feel free to comment on any improvements or ask any questions.