–Make sure you set the Start and End Date below on row 58 and 59
–Create the tables
BEGIN TRY
DROP TABLE [DimDate];
END TRY
BEGIN CATCH
–DO NOTHING
END CATCH

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 = 90) ON [PRIMARY]
) ON [PRIMARY];

GO
–Populate Date dimension
TRUNCATE TABLE DimDate;

–IF YOU ARE USING THE YYYYMMDD format for the primary key then you need to comment out this line.
–DBCC CHECKIDENT (DimDate, RESEED, 60000) –In case you need to add earlier dates later.
DECLARE @tmpDOW TABLE (
DOW  INT,
Cntr INT); –Table for counting DOW occurance in a month

INSERT  INTO @tmpDOW (DOW, Cntr)
VALUES              (1, 0); –Used in the loop below

INSERT  INTO @tmpDOW (DOW, Cntr)
VALUES              (2, 0);

INSERT  INTO @tmpDOW (DOW, Cntr)
VALUES              (3, 0);

INSERT  INTO @tmpDOW (DOW, Cntr)
VALUES              (4, 0);

INSERT  INTO @tmpDOW (DOW, Cntr)
VALUES              (5, 0);

INSERT  INTO @tmpDOW (DOW, Cntr)
VALUES              (6, 0);

INSERT  INTO @tmpDOW (DOW, Cntr)
VALUES              (7, 0);

DECLARE @StartDate AS DATETIME,
@EndDate AS DATETIME,
@Date AS DATETIME,
@WDofMonth AS INT,
@CurrentMonth AS INT,
@CurrentDate AS DATE = getdate();

SELECT @StartDate = ‘1/1/2000’, — Set The start and end date
@EndDate = ‘1/01/2020’, –Non inclusive. Stops on the day before this.
@CurrentMonth = 1; –Counter used in loop below.

SELECT @Date = @StartDate;

WHILE @Date < @EndDate
BEGIN
IF DATEPART(MONTH, @Date) <> @CurrentMonth
BEGIN
SELECT @CurrentMonth = DATEPART(MONTH, @Date);
UPDATE  @tmpDOW
SET Cntr = 0;
END
UPDATE  @tmpDOW
SET Cntr = Cntr + 1
WHERE   DOW = DATEPART(DW, @DATE);
SELECT @WDofMonth = Cntr
FROM   @tmpDOW
WHERE  DOW = DATEPART(DW, @DATE);
INSERT INTO DimDate ([DateSK], [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], HolidayText) –TO MAKE THE DateSK THE YYYYMMDD FORMAT UNCOMMENT THIS LINE… Comment for autoincrementing.
SELECT CONVERT (VARCHAR, @Date, 112) AS [DateSK], –TO MAKE THE DateSK THE YYYYMMDD FORMAT UNCOMMENT THIS LINE COMMENT FOR AUTOINCREMENT
@Date AS [FullDate],
DATEPART(DAY, @DATE) AS [Day],
CASE
WHEN DATEPART(DAY, @DATE) IN (11, 12, 13) THEN CAST (DATEPART(DAY, @DATE) AS VARCHAR) + ‘th’
WHEN RIGHT(DATEPART(DAY, @DATE), 1) = 1 THEN CAST (DATEPART(DAY, @DATE) AS VARCHAR) + ‘st’
WHEN RIGHT(DATEPART(DAY, @DATE), 1) = 2 THEN CAST (DATEPART(DAY, @DATE) AS VARCHAR) + ‘nd’
WHEN RIGHT(DATEPART(DAY, @DATE), 1) = 3 THEN CAST (DATEPART(DAY, @DATE) AS VARCHAR) + ‘rd’ ELSE CAST (DATEPART(DAY, @DATE) AS VARCHAR) + ‘th’
END AS [DaySuffix],
CASE DATEPART(DW, @DATE)
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, @DATE) AS [DayOfWeekNumber],
@WDofMonth AS [DOWInMonth], –Occurance of this day in this month. If Third Monday then 3 and DOW would be Monday.
DATEPART(dy, @Date) AS [DayOfYearNumber], –Day of the year. 0 — 365/366
DATEDIFF(dd, @CurrentDate, @Date) AS [RelativeDays],
DATEPART(ww, @Date) AS [WeekOfYearNumber], –0-52/53
DATEPART(ww, @Date) + 1 – DATEPART(ww, CAST (DATEPART(mm, @Date) AS VARCHAR) + ‘/1/’ + CAST (DATEPART(yy, @Date) AS VARCHAR)) AS [WeekOfMonthNumber],
DATEDIFF(ww, @CurrentDate, @Date) AS [RelativeWeeks],
DATEPART(MONTH, @DATE) AS [CalendarMonthNumber], –To be converted with leading zero later.
DATENAME(MONTH, @DATE) AS [CalendarMonthName],
DATEDIFF(MONTH, @CurrentDate, @Date) AS [RelativeMonths],
DATEPART(qq, @DATE) AS [CalendarQuarterNumber], –Calendar quarter
CASE DATEPART(qq, @DATE)
WHEN 1 THEN ‘First’
WHEN 2 THEN ‘Second’
WHEN 3 THEN ‘Third’
WHEN 4 THEN ‘Fourth’
END AS [CalendarQuarterName],
DATEDIFF(qq, @CurrentDate, @Date) AS [RelativeQuarters],
DATEPART(YEAR, @Date) AS [CalendarYearNumber],
DATEDIFF(YEAR, @CurrentDate, @Date) AS [RelativeYears],
RIGHT(‘0’ + CONVERT (VARCHAR (2), MONTH(@Date)), 2) + ‘/’ + RIGHT(‘0’ + CONVERT (VARCHAR (2), DAY(@Date)), 2) + ‘/’ + CONVERT (VARCHAR (4), YEAR(@Date)),
CASE DATEPART(DW, @DATE)
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, @DATE)
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, @Date)
WHEN 1 THEN 1 ELSE 0
END AS [FirstDayOfCalendarMonthFlag],
CASE
WHEN DateAdd(day, -1, DateAdd(month, DateDiff(month, 0, @Date) + 1, 0)) = @Date THEN 1 ELSE 0
END AS [LastDayOfCalendarMonthFlag],
NULL AS HolidayText;
SELECT @Date = DATEADD(dd, 1, @Date);
END

— 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.
BEGIN TRY
DROP TABLE #tmpHoliday;
END TRY
BEGIN CATCH
–do nothing
END CATCH

CREATE TABLE #tmpHoliday
(
ID     INT      IDENTITY (1, 1),
DateID INT     ,
Week   TINYINT ,
YEAR   CHAR (4),
DAY    CHAR (2)
);

INSERT INTO #tmpHoliday (DateID, [YEAR], [DAY])
SELECT   [DateSK],
CalendarYearNumber,
[DAY]
FROM     dbo.DimDate
WHERE    [CalendarMonthNumber] = 11
AND [Dayofweek] = ‘Monday’
ORDER BY CalendarYearNumber, [DAY];

DECLARE @CNTR AS INT,
@POS AS INT,
@STARTYEAR AS INT,
@ENDYEAR AS INT,
@CURRENTYEAR AS INT,
@MINDAY AS INT;

SELECT @CURRENTYEAR = MIN([YEAR]),
@STARTYEAR = MIN([YEAR]),
@ENDYEAR = MAX([YEAR])
FROM   #tmpHoliday;

WHILE @CURRENTYEAR <= @ENDYEAR
BEGIN
SELECT @CNTR = COUNT([YEAR])
FROM   #tmpHoliday
WHERE  [YEAR] = @CURRENTYEAR;
SET @POS = 1;
WHILE @POS <= @CNTR
BEGIN
SELECT @MINDAY = MIN(DAY)
FROM   #tmpHoliday
WHERE  [YEAR] = @CURRENTYEAR
AND [WEEK] IS NULL;
UPDATE  #tmpHoliday
SET [WEEK] = @POS
WHERE   [YEAR] = @CURRENTYEAR
AND [DAY] = @MINDAY;
SELECT @POS = @POS + 1;
END
SELECT @CURRENTYEAR = @CURRENTYEAR + 1;
END

UPDATE  DT
SET HolidayText = ‘Election Day’
FROM    dbo.DimDate AS DT
INNER JOIN
#tmpHoliday AS HL
ON (HL.DateID + 1) = DT.DateSK
WHERE   [WEEK] = 1;

DROP TABLE #tmpHoliday;

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