SQL 2008 CTE for date/time dimension table

Here is a quick CTE to get date/time dimension data for a SQL 2008 data warehouse. You could use the built in SQL 2008 time feature but it does not give you hourly granularity so this may come in handy for you if you need that level of detail. You could easily just add a few more DATEPART() calls if you needed minutes etc.

This was originally taken from a post here and I modified it to support hour_of_day.
http://blogs.conchango.com/jamiethomson/archive/2007/01/11/T_2D00_SQL_3A00_-Generate-a-list-of-dates.aspx



---------------------------------
-- Get a list of Dates via CTE --
---------------------------------
with mycte as
(
select cast('2008-01-01' as datetime) fldTimeID
union all
SELECT DATEADD(hh, 1, fldTimeID)
--select DateValue + 1
from mycte
where fldTimeID + 1 < '2040-12-31'
)
select fldTimeID as fldHourID,
DATEADD(dd, DATEDIFF(dd,0,fldTimeID), 0) AS fldDay,
DATEADD(mm, DATEDIFF(mm,0,fldTimeID), 0) as fldMonth,
DATEADD(yy, DATEDIFF(yy,0,fldTimeID), 0) as fldYear,
DATEPART(m, fldTimeID) as month_of_year,
DATEPART(dd, fldTimeID) as day_of_month,
DATEPART(hh, fldTimeID) as hour_of_day
--INTO time
from mycte
OPTION (MAXRECURSION 0)

This will produce 289249 records so bring down the 2040-12-31 date if you don't need it to go out that far.

Batching with SQL Server 2005

ok, this is a really cool feature of SQL 2005 that I used the other day to quickly populate some test data....

SQL 2005 has a new "Batching" feature of the syntax that let's you repeat a statement any number of times you'd like by simply adding an integer to the end of the statement.

you can insert 100 test records into a table by issuing this command.

--Issue this command first.
CREATE TABLE dbo.TestTable (ID INT IDENTITY (1,1), RowGUID uniqueidentifier, InsertTime datetime)

--Now issue this command to insert the 100 records.
INSERT INTO dbo.TestTable (RowGUID, InsertTime) VALUES (NEWID(), GETDATE())
GO 100

isn't that cool?

If you execute both together it will try to do the CREATE TABLE 100 times and it will fail but you can see the power of running multiple lines together though...

Building SaaS Software with SQL Server...

I have been working as a Database Architect for a SaaS software company for almost 2 years now and it's been an exciting challenge building software that is "alway's on". The technology stack that we've used in our e-commerce product line is mostly Microsoft stuff with a new sprinkling of opensource. (Linux and PostgreSQL) I have to admit, interoperabiliy of the Microsoft world and the linux world has come a long way. We have applications that can span both technologies and that is pretty cool. More to come on that topic...

Hyperthreading or Not

I have been dealing with some Hyperthreading questions latley regarding their Pro's and Con's. I currently have HT turned on for our 4-Way SQL Servers but am not convinced that it's the best way to go. This coupled with our Parallelism configurations (Defaut) might be contributing to some CPU thrashing that we are seeing.

For instance, when I run a sp_who2, I notice that some threads of a spid are waiting on the infamous CXPACKET waittype. This is telling me that while the leading thread is still running, these are just waiting for it to finish. Probably not getting the best possible performance.

I will test with these off during Q1 of next year and post my results. Anyone else have any comments regarding this?