You can use the Itzik style cross join to replace reading sys.all_columns to get the numbers cte I created in my solution and get rid of all reads in my solution. Like this:

SET STATISTICS IO ON;

DECLARE

@StartDate DATE = '2014-08-01',

@EndDate DATE = '2014-08-31',

@DayNo TINYINT = 6;

/* this is a virtual numbers/tally table that is used to get all the days

between the days. If you already have a calendar table or a table that has

the dates you are querying this isn't necessary */

WITH E1(N)

AS (

SELECT

1

UNION ALL

SELECT

1

UNION ALL

SELECT

1

UNION ALL

SELECT

1

UNION ALL

SELECT

1

UNION ALL

SELECT

1

UNION ALL

SELECT

1

UNION ALL

SELECT

1

UNION ALL

SELECT

1

UNION ALL

SELECT

1

), -- 1*10^1 or 10 rows

E2(N)

AS (

SELECT

1

FROM

E1 a,

E1 b

), -- 1*10^2 or 100 rows

E4(N)

AS (

SELECT

1

FROM

E2 a,

E2 b

), -- 1*10^4 or 10,000 rows

E8(N)

AS (

SELECT

1

FROM

E4 a,

E4 b

), -- 1*10^8 or 100,000,000 rows

nums

AS (

SELECT TOP (4000)

ROW_NUMBER() OVER (ORDER BY (

SELECT

NULL

)) - 1 AS N

FROM

E8

),

Calendar

AS (

SELECT

CONVERT(DATE, DATEADD(DAY, n, @StartDate)) AS theDate

FROM

nums

),

WeekDays

AS (

SELECT

*,

/* figure what day is the first day of the week. This setting is controlled by the

language of the SQL Server and you can see the values for DateFirst in sys.syslanguages */

CASE @@DateFirst

/* First day of week is monday (1) and last day of week is Sunday (7)*/

WHEN 1 THEN CASE DATEPART(WEEKDAY, theDate)

WHEN 7 THEN 0

ELSE DATEPART(WEEKDAY, theDate)

END

/* 1 and 7 are the only options for @@DATEFIRST currently so

Sunday is first day of week when @@DATEFIRS isn't 1 */

ELSE DATEPART(WEEKDAY, theDate) - 1

END AS DayNo,

DATENAME(WEEKDAY, theDate) AS DayName

FROM

Calendar

)

SELECT

*

FROM

WeekDays

WHERE

WeekDays.theDate BETWEEN @StartDate AND @EndDate AND

WeekDays.DayNo = @DayNo;

GO

DECLARE

@StartDate DATE = '2014-08-01',

@EndDate DATE = '2014-08-31',

@DayNo TINYINT = 6;

;

WITH cte_Recursion

AS (

SELECT

@startDate AS [Date]

UNION ALL

SELECT

DATEADD(DAY, 1, [Date])

FROM

cte_Recursion

WHERE

[Date] < @endDate

)

SELECT

[Date]

FROM

cte_Recursion

WHERE

DATEPART(WEEKDAY, [Date]) = @DayNo

OPTION

(MAXRECURSION 0)

--

SET STATISTICS IO OFF;

This is an interesting situation where, if you tune based on execution plan, the recursive cte looks better because if you run both in a batch the recursive cte solution says it's cost is 0% of the batch even though the top solution does 0 reads and the recursive cte does 187. The reason is that no matter how many rows you need to return the cost estimate for the recursive CTE remains the same.

For instance if you set the StartDate to 0001-08-01 and leave the EndDate as 2014-08-31 then the recursive CTE (on my laptop) takes ~6500 ms adn the virtual tally table solution takes ~900 ms.