T-SQL: How to generate a set of dates (time dimension) in a View itself?

Today I wanted to add all Saturdays and Sundays to our so called NonBusinessDays-table.
An easy join with a Time dimension/table would be fairly simple, but it also creates a dependency between the View and the Table itself. I didn’t want that, what I wanted to accomplish is to generate that mini Time dimension in the View itself (so without any other dependencies except for the actual so called NonBusinessDays-table).

So imagine something like:

SELECT * FROM dbo.tbl_NonBusinessDays
UNION
(
SELECT 'all Saturdays and Sundays between 2010 and 2020'
)

Normally a simple query like this would do the trick (inspired by this thread):

WITH AllWeekendDates(WeekendDate)
AS
(
SELECT CAST('2010-01-01 00:00:00.000' AS DATETIME) --From the year 2010
UNION ALL
SELECT DATEADD(DAY,1,WeekendDate) FROM AllWeekendDates
WHERE DATEPART(YEAR,DATEADD(DAY,1,WeekendDate)) < 2020 --Until the year 2020
)

SELECT
WeekendDate
,DATENAME(WEEKDAY,WeekendDate) AS DayName
FROM AllWeekendDates
WHERE DATENAME(WEEKDAY,WeekendDate) IN ('Saturday','Sunday')
OPTION (MAXRECURSION 0)

The results:

CTE-WeekendDays-Results

But! You’re not allowed to use the OPTION-statement in a View 🙂

OPTION (MAXRECURSION limit) is not allowed in view and UDF definitions

An easy thing to do is to move the ‘OPTION (MAXRECURSION 0)’ from inside the View to the select query that’s using the View:
http://benchmarkitconsulting.com/colin-stasiuk/2010/04/12/maxrecursion-with-a-cte-in-a-view/

Like this:

SELECT [WeekendDate]
FROM [dbo].[vw_WeekendDates]
OPTION (MAXRECURSION 0)

A trick that I found while Googling is to use numbers (like for example the surrogate key of a large table or by using ROW_NUMBER()):
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=139267 (bottom post)

SELECT DATEADD(d,number,'9/11/2009')AS Date
FROM master..spt_values
WHERE number BETWEEN 1 AND
DATEDIFF(d,'9/11/2009',GETDATE())
AND type = 'P'

Now, let’s combine the original query (that included the OPTION (MAXRECURSION 0) statement) with this fancy trick.
The ideal query to use in a View will be this one:


SELECT DATEADD(d, [Row],'2010-01-01') AS Date --From the year 2010
FROM (
SELECT TOP 10000 [Row] = ROW_NUMBER() OVER(ORDER BY v1.Number ASC)
FROM master.dbo.spt_values v1
CROSS JOIN master.dbo.spt_values v2
) d
WHERE DATENAME(WEEKDAY, DATEADD(d, [Row],'2010-01-01')) IN ('Saturday','Sunday')
AND DATEPART(YEAR, DATEADD(DAY, 1, DATEADD(d, [Row],'2010-01-01'))) < 2020 --Until the year 2020

The results:
WeekendDays-from-View-Results