Create and Populate a Date Dimension Table

  • Comments posted to this topic are about the item Create and Populate a Date Dimension Table

  • Nice - But you have missed President's day as a Holiday. Third Monday of February.

  • Thank you for pointing that out. I based the holidays on a list from the company where I worked at the time, and they didn't take that one off. It would be easy enough to add the code for that.

    After MLK Day, insert:

    --President's Day (3rd Monday)

    ([Month] = 2

    AND [DAY_OF_WEEK] = 2

    AND DATE_ID IN (SELECT

    MIN(DATE_ID) + 14

    FROM

    DATE_DIM

    WHERE

    [Month] = 2

    AND [DAY_OF_WEEK] = 2

    GROUP BY

    [Year])

    )

  • Thank you for the informative script.

    I noticed that many of the fields are defined as SMALLINT, even those that would never go over a TINYINT value. These include DAY_OF_WEEK (max 7), MONTH (max 12), and QUARTER (max 4). Is there a reason you chose SMALLINT over TINYINT in these cases?

    Also, the SQL_DATE column is defined as a DATETIME (8 bytes) rather than a DATE (3 bytes), even though it appears that only whole dates will be used.

    Changing the values to the smallest datatype necessary to hold the values changes the row width from 263 bytes to 93 bytes (by my calculations). I expect that would result in better performance because of decreased page reads. How would using the larger datatypes be a benefit?

  • I originally learned the technique against SQL Server 2000, and never thought to improve it. Thanks for the suggestion! It's not bad for 25 years or so, but I can see it getting to be a worthwhile benefit if someone wanted to run a century and add other reporting fields.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply