Ok, I know that creating a time dimension down to the millisecond is extremely rare.  After all, we are talking about  86,400,000 records.  In my case, it is needed for a customer with a 50TB database that contains stock trades that are recorded down to the millisecond (we are using a Parallel Data Warehouse).  You can imagine how long it would take to build this table using a loop with inserts.  But my friend Martin Lee came up with a very fast solution using a cross join (cartesian product).  Check out the code:

/* CREATE TIME PART TABLES TO DO A CROSS JOIN CARTESIAN PRODUCT TO CTAS INTO DIMTIME */--CREATE A SINGLE VALUE TABLE TO USE AS DUMMY FROM TABLE.
IF NOT EXISTS (SELECT 1 FROM DirectEdgeDW.sys.tables WHERE name = 'Singleton' AND type = 'U')
BEGIN
CREATE TABLE DirectEdgeDW.dbo.Singleton
WITH (DISTRIBUTION = REPLICATE)
AS
SELECT DISTINCT 1 AS VALUE FROM SYS.DATABASES
END
--GENERATE MILLISECOND TABLE
IF EXISTS (SELECT 1 FROM DirectEdgeDW.sys.tables WHERE name = 'MCL_TimePart_MS' AND type = 'U')
BEGIN
DROP TABLE DirectEdgeDW.dbo.MCL_TimePart_MS
END
CREATE TABLE DirectEdgeDW.dbo.MCL_TimePart_MS
WITH (DISTRIBUTION = REPLICATE)
AS
SELECT TOP 999 ROW_NUMBER() OVER (ORDER BY REQUEST_ID) AS Millisecond
FROM sys.dm_pdw_exec_requests
INSERT INTO MCL_TimePart_MS
VALUES (0)
--SELECT * FROM MCL_TimePart_MS
IF EXISTS (SELECT 1 FROM DirectEdgeDW.sys.tables WHERE name = 'MCL_TimePart_SS' AND type = 'U')
BEGIN
DROP TABLE DirectEdgeDW.dbo.MCL_TimePart_SS
END
CREATE TABLE DirectEdgeDW.dbo.MCL_TimePart_SS
WITH (DISTRIBUTION = REPLICATE)
AS
SELECT Millisecond AS Second FROM DirectEdgeDW.dbo.MCL_TimePart_MS WHERE Millisecond < 60
--select * from MCL_TimePart_SS
IF EXISTS (SELECT 1 FROM DirectEdgeDW.sys.tables WHERE name = 'MCL_TimePart_MM' AND type = 'U')
BEGIN
DROP TABLE DirectEdgeDW.dbo.MCL_TimePart_MM
END
CREATE TABLE DirectEdgeDW.dbo.MCL_TimePart_MM
WITH (DISTRIBUTION = REPLICATE)
AS
SELECT second AS Minute FROM DirectEdgeDW.dbo.MCL_TimePart_SS
--select * from MCL_TimePart_MM
IF EXISTS (SELECT 1 FROM DirectEdgeDW.sys.tables WHERE name = 'MCL_TimePart_HH' AND type = 'U')
BEGIN
DROP TABLE DirectEdgeDW.dbo.MCL_TimePart_HH
END
CREATE TABLE DirectEdgeDW.dbo.MCL_TimePart_HH
WITH (DISTRIBUTION = REPLICATE)
AS
SELECT second AS Hour FROM DirectEdgeDW.dbo.MCL_TimePart_SS WHERE Second < 24
--select * from MCL_TimePart_HH
--CROSS JOIN
IF EXISTS (SELECT 1 FROM DirectEdgeDW.sys.tables WHERE name = 'DimTime' AND type = 'U')
BEGIN
DROP TABLE DirectEdgeDW.dbo.DimTime
END
CREATE TABLE DirectEdgeDW.dbo.DimTime
WITH (CLUSTERED INDEX(TimeKey),DISTRIBUTION = REPLICATE)
AS
SELECT CAST(CAST(hh.Hour AS VARCHAR(2)) + RIGHT('0' + CAST(mm.Minute AS VARCHAR(2)),2) + RIGHT('0' + CAST(ss.Second AS VARCHAR(2)),2) + RIGHT('00' + CAST(ms.Millisecond AS VARCHAR(3)),3) AS INT) AS TimeKey
, hh.Hour, mm.Minute, ss.Second, ms.Millisecond
FROM
DirectEdgeDW.dbo.MCL_TimePart_MS ms
CROSS JOIN DirectEdgeDW.dbo.MCL_TimePart_SS ss
CROSS JOIN DirectEdgeDW.dbo.MCL_TimePart_MM mm
CROSS JOIN DirectEdgeDW.dbo.MCL_TimePart_HH hh
INSERT INTO dbo.DimDate (DateKey,FullDateAltKey,DayNumberOfWeek,DayNameOfWeek,DayNumberOfMonth,DayNumberOfYear,WeekNumberOfYear,WeekNameOfYear,[MonthName],MonthNumberOfYear,MonthFlag,QuarterNumber,QuarterName,QuarterFlag,SemesterNumber,SemesterName,SemesterFlag,[Year])
SELECT -1, CAST('1/1/2100' AS DATE), 0, 'Unknown', 0, 0, 0, 'Unknown', 'Unknown', 0, 0, 0, 'Unknown', 0, 0, 'Unknown', 0, 0
FROM [DirectEdgeDW].[dbo].[UnknownMembers]
DROP TABLE DirectEdgeDW.dbo.MCL_TimePart_MS
DROP TABLE DirectEdgeDW.dbo.MCL_TimePart_SS
DROP TABLE DirectEdgeDW.dbo.MCL_TimePart_MM
DROP TABLE DirectEdgeDW.dbo.MCL_TimePart_HH