Brad McKuhen: SSMS

I am a responsible coder. Everywhere I’ve worked, if they haven’t had source control, I have championed the install of it. Even when it wasn’t possible, I have still campaigned to get it, because we all need insurance.

However.

This sense of responsibility? Not so much when in my test lab, but still, I do check in and check out to provide myself sanity. And prevent myself from stepping on myself. I just don’t do it as often as I would, since I’m not worried about other people getting in there.

(Well, I am, but that’s what firewalls are for.)

So, in trying to connect to Team Foundation Server, I was reminded that I’m going to need to install two MORE packages just to chat with my own TFS install from SSMS.

First up, I have:

Microsoft Visual Studio Team Explorer
http://www.microsoft.com/en-us/download/details.aspx?id=329

Then, I have to install:

Microsoft Visual Studio Team Foundation Server 2010 MSSCCI Provider 32-bit
http://visualstudiogallery.msdn.microsoft.com/bce06506-be38-47a1-9f29-d3937d3d88d6

Or, if I were on SQL 2012, I would need:

Microsoft Visual Studio Team Foundation Server 2012 MSSCCI Provider 32-bit
http://visualstudiogallery.msdn.microsoft.com/b5b5053e-af34-4fa3-9098-aaa3f3f007cd

And this is just so I can connect.

I want this built in – why isn’t this software on a web share as a part of the server install, so I don’t have to go back out to the internet and get it?

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

*/

Brad McKuhen

Tuesday, February 2, 2021

SSMS Error - An exception occurred while executing a Transact-SQL statement or batch.

Tuesday, October 9, 2018

Azure Data Studio

Tuesday, November 29, 2016

SQLCMD vs. Registered Servers

Tuesday, November 15, 2016

Another "Oh Nice!" Moment

Tuesday, November 1, 2016

The type initializer has failed ! Oh Noes!

Tuesday, April 7, 2015

Running SSMS as a non-domain user

Tuesday, August 27, 2013

What I wanted built-in, part 2

Tuesday, May 21, 2013

How to make a date dimension

About Me

Blog Archive