>Haven't done one of these in a while.
>
>Recently someone asked me about exploding out a date range by user.
>
>For instance, suppose you had the following table definition and two sample rows:
>
>

>create table dbo.SourceData (Name varchar(100), StartDate date, EndDate date)	
>
>insert into dbo.SourceData values
>    ('Kevin Goff', '9-12-2016', '9-12-2016'), 
>    ('Katy Goff','9-12-2016', '9-15-2016') 
>

>
>So I have a date range of 9-12 to 9-12 (one day), and Katy has a date range of 9-12 to 9-15 (4 days).
>
>I want to generate a result set of 5 rows...one for me and four for Katy, with the corresponding dates. In reality, the sourceData table could have thousands of rows.
>
>Absolute best performance isn't paramount, since this routine would only run once a day, but obviously it could run more frequently down the road.
>
>Here was my solution...I've had to do this before to populate data marts. I know that recursion allows me to generate a range, but in order to use a CROSS APPLY to apply the routine across many rows, I've only ever been able to do it with a table function.
>
>So here was my solution:
>
>

>CREATE FUNCTION dbo.ExplodeDates
>( @StartDate date, @EndDate date)
>returns table
>as 
>return (
>    with tempcte as
>                (select @StartDate as ActualDate
>                      union all             -- CTE recursive loop
>                   select dateadd(d,1,ActualDate) from tempcte
>                           where dateadd(d,1,ActualDate) <= @EndDate )
>        select * from tempcte )
>
>go
>
>
>select Name, ActualDate from SourceData
>   CROSS APPLY dbo.ExplodeDates ( SourceData.StartDate, SourceData.EndDate)
>        OPTION (MAXRECURSION 32767)
>

>
>This works, and generates the following results:
>
>

>Name	     ActualDate
>Kevin Goff       2016-09-12
>Katy Goff       2016-09-12
>Katy Goff       2016-09-13
>Katy Goff       2016-09-14
>Katy Goff       2016-09-15
>

>
>Again, this works - though it doesn't seem 100% optimal. Admittedly, I've never spent any serious time thinking about ways to optimize it. But just curious if anyone had any thoughts.
>
>Thanks

The juice is to generate an arbitrary number
of lines, in the fastest possible way.
I always prefer to use generic functions, they do just the juice.
Use the recursion is always the last choice,
because it is always the slower route, in any language.

CREATE FUNCTION [dbo].ft_Rows
(	@max	INT
)
RETURNS TABLE
	RETURN SELECT TOP (@max)
			Nulls = NULL FROM	master.sys.all_columns T1	-- ~ 7000        -- put here the long master system table
						,	master.sys.all_columns T2	-- ~ 7000²	more  55 millions
					--	,	master.sys.all_columns T3	-- ~ 7000³	more 400 billions	-- add this get 2 x slower

GO

SELECT * 
	,	ActualDate = DATEADD(D,P.N-1,S.StartDate)
	FROM dbo.SourceData S
	CROSS APPLY ( SELECT N=ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM dbo.ft_Rows(1+DATEDIFF(D,S.StartDate,S.EndDate))) P