SQL Server: Populate (Minute) Date Dimension table

Question

I'm working on a script to populate a very simple date dimension table whose granularity is down to the minute level. This table should ultimately contain a smalldatetime representing every minute from 1/1/2000 to 12/31/2015 23:59.

Here is the definition for the table:

CREATE TABLE [dbo].[REF_MinuteDimension] (
    [TimeStamp] SMALLDATETIME NOT NULL,
    CONSTRAINT [PK_REF_MinuteDimension] PRIMARY KEY CLUSTERED ([TimeStamp] ASC) WITH (FILLFACTOR = 100)
);

Here is the latest revision of the script:

DECLARE @CurrentTimeStamp AS SMALLDATETIME;
SELECT TOP(1) @CurrentTimeStamp = MAX([TimeStamp]) FROM [dbo].[REF_MinuteDimension];

IF @CurrentTimeStamp IS NOT NULL
    SET @CurrentTimeStamp = DATEADD(MINUTE, 1, @CurrentTimeStamp);
ELSE
    SET @CurrentTimeStamp = '1/1/2000 00:00';

ALTER TABLE [dbo].[REF_MinuteDimension] DROP CONSTRAINT [PK_REF_MinuteDimension];

WHILE @CurrentTimeStamp < '12/31/2050 23:59'
BEGIN

    ;WITH DateIndex ([TimeStamp]) AS
    (
        SELECT @CurrentTimeStamp
        UNION ALL
        SELECT DATEADD(MINUTE, 1, [TimeStamp]) FROM DateIndex di WHERE di.[TimeStamp] < dbo.fGetYearEnd(@CurrentTimeStamp)
    )
    INSERT INTO [dbo].[REF_MinuteDimension] ([TimeStamp])
        SELECT di.[TimeStamp] FROM DateIndex di
    OPTION (MAXRECURSION 0);

    SET @CurrentTimeStamp = DATEADD(YEAR, 1, dbo.fGetYearBegin(@CurrentTimeStamp))

END

ALTER TABLE [dbo].[REF_MinuteDimension] ADD CONSTRAINT [PK_REF_MinuteDimension] PRIMARY KEY CLUSTERED ([TimeStamp] ASC) WITH (FILLFACTOR = 100);

A couple of things to point out:

• I've added logic to drop and subsequently re-add the primary key constraint on the table, hoping to boost the performance.
• I've added logic to chunk the INSERTS into yearly batches to minimize the impact on the transaction log. On a side note, we're using the SIMPLE recovery model.

Performance is so-so and takes around 15-20 minutes to complete. Any hints/suggestions on how this script could be "tuned up" or improved?

Also, for completeness here are fGetYearBegin and fGetYearEnd:

CREATE FUNCTION dbo.fGetYearBegin
(
    @dtConvertDate datetime
)
RETURNS smalldatetime
AS
BEGIN
    RETURN DATEADD(YEAR, DATEDIFF(YEAR, 0, @dtConvertDate), 0) 
END


CREATE FUNCTION dbo.fGetYearEnd
(
    @dtConvertDate datetime
)
RETURNS smalldatetime
AS
BEGIN
    RETURN DATEADD(MINUTE, -1, DATEADD(YEAR, 1, dbo.fGetYearBegin(@dtConvertDate)))
END

Answer 1

This takes 11 seconds on my server...

If      Object_ID('tempdb..#someNumbers') Is Not Null Drop Table #someNumbers;
Create  Table #someNumbers (id Int);

Declare @minutes Int,
        @days Int;

Select  @minutes = DateDiff(Minute,'1/1/2000 00:00','1/2/2000 00:00'),
        @days = DateDiff(Day,'1/1/2000 00:00','1/1/2051 00:00');

With    Base As
(       
        Select  1 As seedID
        Union   All
        Select  1
),      Build As
(
        Select  seedID
        From    Base
        Union   All
        Select  b.seedID + 1
        From    Build b
        Cross   Join Base b2
        Where   b.SeedID < 14
)
Insert  #someNumbers
Select  Row_Number() Over (Order By seedID) As id
From    Build
Option (MaxRecursion 0);

If      Object_ID('tempdb..#values') Is Not Null Drop Table #values;
Create  Table #values ([TimeStamp] SmallDateTime NOT NULL);

With    Dates As
(
        Select  DateAdd(Day,id-1,'1/1/2000 00:00') As [TimeStamp]
        From    #someNumbers
        Where   id <= @days
)
Insert  #values
Select  Convert(SmallDateTime,DateAdd(Minute,id-1,[TimeStamp]))
From    Dates d
Join    #someNumbers sn
        On  sn.id <= @minutes
Order   By 1