How to make a date dimension

So I need to create a date dimension, and I refuse to use RBAR-y loops or cursors to do it.

That means I'm limiting myself, intentionally, to using a tally table (also sometimes called a numbers table).

I found the script by DustinRyan, URL included inline below, that did most of what I needed. However, the Day Of The Week In Month (DoWInMonth) column was filled by a loop, and that was going to violate the requirements for making this dimension.

Also, the election day query was using a nested loop, and that was going to violate the requirements for the making of this dimension.

Found below is my update of his script to use a tally table to dynamically generate the required column. On my development virtual machine, running on a laptop spinning at 5400 RPM, generating and inserting all 300 years of this data as an insert takes less than 5 seconds. Not bad.

Not bad at all.

-- Find 300 years of dates
-- This version created by brad mckuhen
-- http://www.bradmckuhen.com
-- Original from a date dimension script by DustinRyan, found here:
-- http://www.bidn.com/blogs/DustinRyan/bidn-blog/2667/create-date-dimension-script
PRINT convert(VARCHAR, getdate(), 113) --USED FOR CHECKING RUN TIME.

-- Need a DB everybody has
USE tempdb;
GO

-- Prevent unneeded network traffic
SET NOCOUNT ON;
GO

-- Clear any previous runs
IF object_id(N'tempdb..dimDate') IS NOT NULL
 DROP TABLE dimDate;

CREATE TABLE [dbo].[DimDate] (
 --[DateSK] [int] IDENTITY(1,1) NOT NULL--Use this line if you just want an autoincrementing counter AND COMMENT BELOW LINE 
 [DateSK] [int] NOT NULL --TO MAKE THE DateSK THE YYYYMMDD FORMAT USE THIS LINE AND COMMENT ABOVE LINE. 
 , [FullDate] [datetime] NOT NULL
 , [Day] [tinyint] NOT NULL
 , [DaySuffix] [varchar](4) NOT NULL
 , [DayOfWeek] [varchar](9) NOT NULL
 , [DayOfWeekNumber] [int] NOT NULL
 , [DayOfWeekInMonth] [tinyint] NOT NULL
 , [DayOfYearNumber] [int] NOT NULL
 , [RelativeDays] INT NOT NULL
 , [WeekOfYearNumber] [tinyint] NOT NULL
 , [WeekOfMonthNumber] [tinyint] NOT NULL
 , [RelativeWeeks] INT NOT NULL
 , [CalendarMonthNumber] [tinyint] NOT NULL
 , [CalendarMonthName] [varchar](9) NOT NULL
 , [RelativeMonths] INT NOT NULL
 , [CalendarQuarterNumber] [tinyint] NOT NULL
 , [CalendarQuarterName] [varchar](6) NOT NULL
 , [RelativeQuarters] INT NOT NULL
 , [CalendarYearNumber] INT NOT NULL
 , [RelativeYears] INT NOT NULL
 , [StandardDate] [varchar](10) NULL
 , [WeekDayFlag] BIT NOT NULL
 , [HolidayFlag] BIT NOT NULL
 , [OpenFlag] BIT NOT NULL
 , [FirstDayOfCalendarMonthFlag] BIT NOT NULL
 , [LastDayOfCalendarMonthFlag] BIT NOT NULL
 , [HolidayText] [varchar](50) NULL CONSTRAINT [PK_DimDate] PRIMARY KEY CLUSTERED ([DateSK] ASC) WITH (
  PAD_INDEX = OFF
  , STATISTICS_NORECOMPUTE = OFF
  , IGNORE_DUP_KEY = OFF
  , ALLOW_ROW_LOCKS = ON
  , ALLOW_PAGE_LOCKS = ON
  , FILLFACTOR = 100
  )
 ON [PRIMARY]
 ) ON [PRIMARY]
GO

--you could also set this line to getdate() if you wanted 300 years from today
DECLARE @CurrentDate DATE = '1/1/1800';
DECLARE @StartDate DATETIME = cast('12/31/1799' AS DATE);

INSERT INTO DimDate (
 [DateSK]
 , --TO MAKE THE DateSK THE YYYYMMDD FORMAT UNCOMMENT THIS LINE... Comment for autoincrementing. 
 [FullDate]
 , [Day]
 , [DaySuffix]
 , [DayOfWeek]
 , [DayOfWeekNumber]
 , [DayOfWeekInMonth]
 , [DayOfYearNumber]
 , [RelativeDays]
 , [WeekOfYearNumber]
 , [WeekOfMonthNumber]
 , [RelativeWeeks]
 , [CalendarMonthNumber]
 , [CalendarMonthName]
 , [RelativeMonths]
 , [CalendarQuarterNumber]
 , [CalendarQuarterName]
 , [RelativeQuarters]
 , [CalendarYearNumber]
 , [RelativeYears]
 , [StandardDate]
 , [WeekDayFlag]
 , [HolidayFlag]
 , [OpenFlag]
 , [FirstDayOfCalendarMonthFlag]
 , [LastDayOfCalendarMonthFlag]
 )
SELECT CONVERT(VARCHAR, calcdate, 112) AS [DateSK]
 , calcDate AS [FullDate]
 , DATEPART(DAY, calcDate) AS [Day]
 , CASE 
  WHEN DATEPART(DAY, calcDate) IN (
    11
    , 12
    , 13
    )
   THEN CAST(DATEPART(DAY, calcDate) AS VARCHAR) + 'th'
  WHEN RIGHT(DATEPART(DAY, calcDate), 1) = 1
   THEN CAST(DATEPART(DAY, calcDate) AS VARCHAR) + 'st'
  WHEN RIGHT(DATEPART(DAY, calcDate), 1) = 2
   THEN CAST(DATEPART(DAY, calcDate) AS VARCHAR) + 'nd'
  WHEN RIGHT(DATEPART(DAY, calcDate), 1) = 3
   THEN CAST(DATEPART(DAY, calcDate) AS VARCHAR) + 'rd'
  ELSE CAST(DATEPART(DAY, calcDate) AS VARCHAR) + 'th'
  END AS [DaySuffix]
 , CASE DATEPART(DW, CalcDate)
  WHEN 1
   THEN 'Sunday'
  WHEN 2
   THEN 'Monday'
  WHEN 3
   THEN 'Tuesday'
  WHEN 4
   THEN 'Wednesday'
  WHEN 5
   THEN 'Thursday'
  WHEN 6
   THEN 'Friday'
  WHEN 7
   THEN 'Saturday'
  END AS [DayOfWeek]
 , DATEPART(DW, CalcDate) AS [DayOfWeekNumber]
 --Occurance of this day in this month. If Third Monday then 3 and DOW would be Monday.
 , rank() OVER (
  PARTITION BY yearno
  , monthno
  , dow ORDER BY calcdate
  ) AS DOWInMonth
 , DATEPART(dy, CalcDate) AS [DayOfYearNumber] --Day of the year. 0 - 365/366
 , DATEDIFF(dd, @CurrentDate, CalcDate) AS [RelativeDays]
 , DATEPART(ww, CalcDate) AS [WeekOfYearNumber] --0-52/53
 , DATEPART(ww, CalcDate) + 1 - DATEPART(ww, CAST(DATEPART(mm, CalcDate) AS VARCHAR) + '/1/' + CAST(DATEPART(yy, CalcDate) AS VARCHAR)) [WeekOfMonthNumber]
 , DATEDIFF(ww, @CurrentDate, CalcDate) AS [RelativeWeeks]
 , DATEPART(MONTH, CalcDate) AS [CalendarMonthNumber] --To be converted with leading zero later.
 , DATENAME(MONTH, CalcDate) AS [CalendarMonthName]
 , DATEDIFF(MONTH, @CurrentDate, CalcDate) AS [RelativeMonths]
 , DATEPART(qq, CalcDate) AS [CalendarQuarterNumber] --Calendar quarter
 , CASE DATEPART(qq, CalcDate)
  WHEN 1
   THEN 'First'
  WHEN 2
   THEN 'Second'
  WHEN 3
   THEN 'Third'
  WHEN 4
   THEN 'Fourth'
  END AS [CalendarQuarterName]
 , DATEDIFF(qq, @CurrentDate, CalcDate) AS [RelativeQuarters]
 , DATEPART(YEAR, CalcDate) AS [CalendarYearNumber]
 , DATEDIFF(YEAR, @CurrentDate, CalcDate) AS [RelativeYears]
 , RIGHT('0' + convert(VARCHAR(2), MONTH(CalcDate)), 2) + '/' + Right('0' + convert(VARCHAR(2), DAY(CalcDate)), 2) + '/' + convert(VARCHAR(4), YEAR(CalcDate))
 , CASE DATEPART(DW, CalcDate)
  WHEN 1
   THEN 0
  WHEN 2
   THEN 1
  WHEN 3
   THEN 1
  WHEN 4
   THEN 1
  WHEN 5
   THEN 1
  WHEN 6
   THEN 1
  WHEN 7
   THEN 0
  END AS [WeekDayFlag]
 , 0 AS HolidayFlag
 , CASE DATEPART(DW, CalcDate)
  WHEN 1
   THEN 0
  WHEN 2
   THEN 1
  WHEN 3
   THEN 1
  WHEN 4
   THEN 1
  WHEN 5
   THEN 1
  WHEN 6
   THEN 1
  WHEN 7
   THEN 1
  END AS OpenFlag
 , CASE DATEPART(dd, CalcDate)
  WHEN 1
   THEN 1
  ELSE 0
  END AS [FirstDayOfCalendarMonthFlag]
 , CASE 
  WHEN DateAdd(day, - 1, DateAdd(month, DateDiff(month, 0, CalcDate) + 1, 0)) = CalcDate
   THEN 1
  ELSE 0
  END AS [LastDayOfCalendarMonthFlag]
FROM (
 -- To fuel the RANK() I use to get the dynamic 
 -- DoWinMonth value, I calculate the necessary pieces here
 SELECT [Surrogate Key] AS IDNo
  , @StartDate + [Surrogate Key] AS CalcDate
  , datepart(DW, @StartDate + [Surrogate Key]) AS DoW
  , year(@StartDate + [Surrogate Key]) AS YearNo
  , month(@StartDate + [Surrogate Key]) AS MonthNo
 FROM (
  -- This is the tally table on which the whole thing is built
  SELECT TOP (
    SELECT cast(365.25 * 300 AS BIGINT)
    ) ROW_NUMBER() OVER (
    ORDER BY getdate()
    ) AS [Surrogate Key]
  FROM sys.syscolumns s1
   , sys.syscolumns s2
  ) j
 ) k
-- Get the results in a predictable order
ORDER BY [DateSK];

--Add HOLIDAYS --------------------------------------------------------------------------------------------------------------
-- New Years Day --------------------------------------------------------------------------------------------- 
UPDATE dbo.DimDate
SET HolidayText = 'New Year''s Day'
 , HolidayFlag = 1
 , OpenFlag = 0
WHERE [CalendarMonthNumber] = 1
 AND [DAY] = 1

--Set OpenFlag = 0 if New Year's Day is on weekend 
UPDATE dbo.DimDate
SET OpenFlag = 0
WHERE DateSK IN (
  SELECT CASE 
    WHEN DayOfWeek = 'Sunday'
     THEN DATESK + 1
    END
  FROM DimDate
  WHERE CalendarMonthNumber = 1
   AND [DAY] = 1
  )

--Martin Luther King Day --------------------------------------------------------------------------------------- 
--Third Monday in January starting in 1983 
UPDATE DimDate
SET HolidayText = 'Martin Luther King Jr. Day'
 , HolidayFlag = 1
 , OpenFlag = 0
WHERE [CalendarMonthNumber] = 1 --January 
 AND [Dayofweek] = 'Monday'
 AND CalendarYearNumber >= 1983 --When holiday was official 
 AND [DayOfWeekInMonth] = 3 --Third X day of current month. 
GO

--President's Day --------------------------------------------------------------------------------------- 
--Third Monday in February. 
UPDATE DimDate
SET HolidayText = 'President''s Day'
 , HolidayFlag = 1
 , OpenFlag = 0
WHERE [CalendarMonthNumber] = 2 --February 
 AND [Dayofweek] = 'Monday'
 AND [DayOfWeekInMonth] = 3 --Third occurance of a monday in this month. 
GO

--Memorial Day ---------------------------------------------------------------------------------------- 
--Last Monday in May 
UPDATE dbo.DimDate
SET HolidayText = 'Memorial Day'
 , HolidayFlag = 1
 , OpenFlag = 0
FROM DimDate
WHERE DateSK IN (
  SELECT MAX([DateSK])
  FROM dbo.DimDate
  WHERE [CalendarMonthName] = 'May'
   AND [DayOfWeek] = 'Monday'
  GROUP BY CalendarYearNumber
   , [CalendarMonthNumber]
  )

--4th of July --------------------------------------------------------------------------------------------- 
UPDATE dbo.DimDate
SET HolidayText = 'Independance Day'
 , HolidayFlag = 1
 , OpenFlag = 0
WHERE [CalendarMonthNumber] = 7
 AND [DAY] = 4

--Set OpenFlag = 0 if July 4th is on weekend 
UPDATE dbo.DimDate
SET OpenFlag = 0
WHERE DateSK IN (
  SELECT CASE 
    WHEN DayOfWeek = 'Sunday'
     THEN DATESK + 1
    END
  FROM DimDate
  WHERE CalendarMonthNumber = 7
   AND [DAY] = 4
  )

--Labor Day ------------------------------------------------------------------------------------------- 
--First Monday in September 
UPDATE dbo.DimDate
SET HolidayText = 'Labor Day'
 , HolidayFlag = 1
 , OpenFlag = 0
FROM DimDate
WHERE DateSK IN (
  SELECT MIN([DateSK])
  FROM dbo.DimDate
  WHERE [CalendarMonthName] = 'September'
   AND [DayOfWeek] = 'Monday'
  GROUP BY CalendarYearNumber
   , [CalendarMonthNumber]
  )

--Columbus Day------------------------------------------------------------------------------------------ 
--2nd Monday in October 
UPDATE dbo.DimDate
SET HolidayText = 'Columbus Day'
 , HolidayFlag = 1
 , OpenFlag = 0
FROM DimDate
WHERE DateSK IN (
  SELECT MIN(DateSK)
  FROM dbo.DimDate
  WHERE [CalendarMonthName] = 'October'
   AND [DayOfWeek] = 'Monday'
   AND [DayOfWeekInMonth] = 2
  GROUP BY CalendarYearNumber
   , [CalendarMonthNumber]
  )

--Veteran's Day -------------------------------------------------------------------------------------------------------------- 
UPDATE DimDate
SET HolidayText = 'Veteran''s Day'
 , HolidayFlag = 1
 , OpenFlag = 0
WHERE DateSK IN (
  SELECT CASE 
    WHEN DayOfWeek = 'Saturday'
     THEN DateSK - 1
    WHEN DayOfWeek = 'Sunday'
     THEN DateSK + 1
    ELSE DateSK
    END AS VeteransDateSK
  FROM DimDate
  WHERE [CalendarMonthNumber] = 11
   AND [DAY] = 11
  )
GO

--THANKSGIVING -------------------------------------------------------------------------------------------------------------- 
--Fourth THURSDAY in November. 
UPDATE DimDate
SET HolidayText = 'Thanksgiving Day'
 , HolidayFlag = 1
 , OpenFlag = 0
WHERE [CalendarMonthNumber] = 11
 AND [DAYOFWEEK] = 'Thursday'
 AND [DayOfWeekInMonth] = 4
GO

--CHRISTMAS ------------------------------------------------------------------------------------------- 
UPDATE dbo.DimDate
SET HolidayText = 'Christmas Day'
 , HolidayFlag = 1
 , OpenFlag = 0
WHERE [CalendarMonthNumber] = 12
 AND [DAY] = 25

--Set OpenFlag = 0 if Christmas on weekend 
UPDATE dbo.DimDate
SET OpenFlag = 0
WHERE DateSK IN (
  SELECT CASE 
    WHEN DayOfWeek = 'Sunday'
     THEN DATESK + 1
    WHEN Dayofweek = 'Saturday'
     THEN DateSK - 1
    END
  FROM DimDate
  WHERE CalendarMonthNumber = 12
   AND DAY = 25
  )

-- Valentine's Day --------------------------------------------------------------------------------------------- 
UPDATE dbo.DimDate
SET HolidayText = 'Valentine''s Day'
WHERE CalendarMonthNumber = 2
 AND [DAY] = 14

-- Saint Patrick's Day ----------------------------------------------------------------------------------------- 
UPDATE dbo.DimDate
SET HolidayText = 'Saint Patrick''s Day'
WHERE [CalendarMonthNumber] = 3
 AND [DAY] = 17
GO

--Mother's Day --------------------------------------------------------------------------------------- 
--Second Sunday of May 
UPDATE DimDate
SET HolidayText = 'Mother''s Day' --select * from DimDate 
WHERE [CalendarMonthNumber] = 5 --May 
 AND [Dayofweek] = 'Sunday'
 AND [DayOfWeekInMonth] = 2 --Second occurance of a monday in this month. 
GO

--Father's Day --------------------------------------------------------------------------------------- 
--Third Sunday of June 
UPDATE DimDate
SET HolidayText = 'Father''s Day' --select * from DimDate 
WHERE [CalendarMonthNumber] = 6 --June 
 AND [Dayofweek] = 'Sunday'
 AND [DayOfWeekInMonth] = 3 --Third occurance of a monday in this month. 
GO

--Halloween 10/31 ---------------------------------------------------------------------------------- 
UPDATE dbo.DimDate
SET HolidayText = 'Halloween'
WHERE [CalendarMonthNumber] = 10
 AND [DAY] = 31

--Election Day-------------------------------------------------------------------------------------- 
--The first Tuesday after the first Monday in November. 
UPDATE tues
SET HolidayFlag = 1
 , HolidayText = 'Election Day'
FROM dimdate AS tues
INNER JOIN dimdate AS mon
 ON tues.fulldate = mon.fulldate + 1
WHERE mon.CalendarMonthNumber = 11
 AND mon.[DayOfWeek] = 'Monday'
 AND mon.DayOfWeekInMonth = 1
GO

-------------------------------------------------------------------------------------------------------- 
PRINT CONVERT(VARCHAR, GETDATE(), 113) --USED FOR CHECKING RUN TIME.

--DimDate indexes--------------------------------------------------------------------------------------------- 
CREATE UNIQUE NONCLUSTERED INDEX [IDX_DimDate_Date] ON [dbo].[DimDate] ([FullDate] ASC)
 WITH (
   PAD_INDEX = OFF
   , STATISTICS_NORECOMPUTE = OFF
   , SORT_IN_TEMPDB = OFF
   , IGNORE_DUP_KEY = OFF
   , DROP_EXISTING = OFF
   , ONLINE = OFF
   , ALLOW_ROW_LOCKS = ON
   , ALLOW_PAGE_LOCKS = ON
   , FILLFACTOR = 90
   ) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IDX_DimDate_Day] ON [dbo].[DimDate] ([Day] ASC)
 WITH (
   PAD_INDEX = OFF
   , STATISTICS_NORECOMPUTE = OFF
   , SORT_IN_TEMPDB = OFF
   , IGNORE_DUP_KEY = OFF
   , DROP_EXISTING = OFF
   , ONLINE = OFF
   , ALLOW_ROW_LOCKS = ON
   , ALLOW_PAGE_LOCKS = ON
   , FILLFACTOR = 90
   ) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IDX_DimDate_DayOfWeek] ON [dbo].[DimDate] ([DayOfWeek] ASC)
 WITH (
   PAD_INDEX = OFF
   , STATISTICS_NORECOMPUTE = OFF
   , SORT_IN_TEMPDB = OFF
   , IGNORE_DUP_KEY = OFF
   , DROP_EXISTING = OFF
   , ONLINE = OFF
   , ALLOW_ROW_LOCKS = ON
   , ALLOW_PAGE_LOCKS = ON
   , FILLFACTOR = 90
   ) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IDX_DimDate_DOWInMonth] ON [dbo].[DimDate] ([DayOfWeekInMonth] ASC)
 WITH (
   PAD_INDEX = OFF
   , STATISTICS_NORECOMPUTE = OFF
   , SORT_IN_TEMPDB = OFF
   , IGNORE_DUP_KEY = OFF
   , DROP_EXISTING = OFF
   , ONLINE = OFF
   , ALLOW_ROW_LOCKS = ON
   , ALLOW_PAGE_LOCKS = ON
   , FILLFACTOR = 90
   ) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IDX_DimDate_DayOfYear] ON [dbo].[DimDate] ([DayOfYearNumber] ASC)
 WITH (
   PAD_INDEX = OFF
   , STATISTICS_NORECOMPUTE = OFF
   , SORT_IN_TEMPDB = OFF
   , IGNORE_DUP_KEY = OFF
   , DROP_EXISTING = OFF
   , ONLINE = OFF
   , ALLOW_ROW_LOCKS = ON
   , ALLOW_PAGE_LOCKS = ON
   , FILLFACTOR = 90
   ) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IDX_DimDate_WeekOfYear] ON [dbo].[DimDate] ([WeekOfYearNumber] ASC)
 WITH (
   PAD_INDEX = OFF
   , STATISTICS_NORECOMPUTE = OFF
   , SORT_IN_TEMPDB = OFF
   , IGNORE_DUP_KEY = OFF
   , DROP_EXISTING = OFF
   , ONLINE = OFF
   , ALLOW_ROW_LOCKS = ON
   , ALLOW_PAGE_LOCKS = ON
   , FILLFACTOR = 90
   ) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IDX_DimDate_WeekOfMonth] ON [dbo].[DimDate] ([WeekOfMonthNumber] ASC)
 WITH (
   PAD_INDEX = OFF
   , STATISTICS_NORECOMPUTE = OFF
   , SORT_IN_TEMPDB = OFF
   , IGNORE_DUP_KEY = OFF
   , DROP_EXISTING = OFF
   , ONLINE = OFF
   , ALLOW_ROW_LOCKS = ON
   , ALLOW_PAGE_LOCKS = ON
   , FILLFACTOR = 90
   ) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IDX_DimDate_Month] ON [dbo].[DimDate] ([CalendarMonthNumber] ASC)
 WITH (
   PAD_INDEX = OFF
   , STATISTICS_NORECOMPUTE = OFF
   , SORT_IN_TEMPDB = OFF
   , IGNORE_DUP_KEY = OFF
   , DROP_EXISTING = OFF
   , ONLINE = OFF
   , ALLOW_ROW_LOCKS = ON
   , ALLOW_PAGE_LOCKS = ON
   , FILLFACTOR = 90
   ) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IDX_DimDate_MonthName] ON [dbo].[DimDate] ([CalendarMonthName] ASC)
 WITH (
   PAD_INDEX = OFF
   , STATISTICS_NORECOMPUTE = OFF
   , SORT_IN_TEMPDB = OFF
   , IGNORE_DUP_KEY = OFF
   , DROP_EXISTING = OFF
   , ONLINE = OFF
   , ALLOW_ROW_LOCKS = ON
   , ALLOW_PAGE_LOCKS = ON
   , FILLFACTOR = 90
   ) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IDX_DimDate_Quarter] ON [dbo].[DimDate] ([CalendarQuarterNumber] ASC)
 WITH (
   PAD_INDEX = OFF
   , STATISTICS_NORECOMPUTE = OFF
   , SORT_IN_TEMPDB = OFF
   , IGNORE_DUP_KEY = OFF
   , DROP_EXISTING = OFF
   , ONLINE = OFF
   , ALLOW_ROW_LOCKS = ON
   , ALLOW_PAGE_LOCKS = ON
   , FILLFACTOR = 90
   ) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IDX_DimDate_QuarterName] ON [dbo].[DimDate] ([CalendarQuarterName] ASC)
 WITH (
   PAD_INDEX = OFF
   , STATISTICS_NORECOMPUTE = OFF
   , SORT_IN_TEMPDB = OFF
   , IGNORE_DUP_KEY = OFF
   , DROP_EXISTING = OFF
   , ONLINE = OFF
   , ALLOW_ROW_LOCKS = ON
   , ALLOW_PAGE_LOCKS = ON
   , FILLFACTOR = 90
   ) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IDX_DimDate_Year] ON [dbo].[DimDate] ([CalendarYearNumber] ASC)
 WITH (
   PAD_INDEX = OFF
   , STATISTICS_NORECOMPUTE = OFF
   , SORT_IN_TEMPDB = OFF
   , IGNORE_DUP_KEY = OFF
   , DROP_EXISTING = OFF
   , ONLINE = OFF
   , ALLOW_ROW_LOCKS = ON
   , ALLOW_PAGE_LOCKS = ON
   , FILLFACTOR = 90
   ) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IDX_dim_Time_HolidayText] ON [dbo].[DimDate] ([HolidayText] ASC)
 WITH (
   PAD_INDEX = OFF
   , STATISTICS_NORECOMPUTE = OFF
   , SORT_IN_TEMPDB = OFF
   , IGNORE_DUP_KEY = OFF
   , DROP_EXISTING = OFF
   , ONLINE = OFF
   , ALLOW_ROW_LOCKS = ON
   , ALLOW_PAGE_LOCKS = ON
   , FILLFACTOR = 90
   ) ON [PRIMARY]

PRINT convert(VARCHAR, getdate(), 113) --USED FOR CHECKING RUN TIME.
/*
-- Holiday Validation Query
select count(1), HolidayText 
from dimdate 
where HolidayFlag = 1
group by HolidayText
having count(1) >= 1

*/