A common problem is in some cases we have the initial date and end date stored in the table, but we need all range the dates in the period.

Considering the products tables below I will show four techniques to get the result above

DECLARE @Products TABLE
(
id INT
,product VARCHAR(10)
,startdate DATE
,enddate DATE
);

INSERT @Products (id, product ,startdate ,enddate) VALUES
(1, 'ProductA' ,'2016-03-29' ,'2016-04-02' )
,(2, 'ProductB' ,'2016-05-10' ,'2016-05-12' )
,(3, 'ProductC' ,'2016-05-01' ,'2016-05-7' ) ;

First using recursive call

WITH cte
AS (SELECT id
,product
,startdate
,enddate
FROM @Products t
UNION ALL
SELECT id
,product
,Dateadd(day, 1, startdate) startdate
,enddate
FROM cte
WHERE startdate <= enddate)
SELECT id
,product
,startdate
FROM cte
ORDER BY product
,startdate
OPTION (maxrecursion 0);

The problem of this approuch is if you have more that 100 recursive interactios you need to add

OPTION (maxrecursion 0)

at the end of the query.

Using the Cross Apply

SELECT A.id
,A.product
,B.date
FROM @Products A
CROSS apply (SELECT TOP (Datediff(day, A.[startdate], A.[enddate])+1)
Date= Dateadd(day, -1 + Row_number() OVER (ORDER BY (SELECT NULL)), A.[startdate])
FROM master..spt_values n1
,master..spt_values n2) B
ORDER BY A.product
,B.date;

in this example we use a workaround to quickly create a tally table with dates. using the table master..spt_values we create a tally table with more than 6 million of registers.

Using a traditional tally table with date delimitation

DECLARE @fromdate DATE = '20160101'DECLARE @thrudate DATE = '20171231' ;WITH n AS
(
SELECT n
FROM (VALUES
(
0
)
,(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n)) ,
dates
AS (SELECT TOP (Datediff (day, @fromdate, @thrudate)+1) [Date]=
CONVERT(DATE,
Dateadd(day, Row_number()
OVER(
ORDER BY ( SELECT 1)) - 1, @fromdate))
FROM n AS deka
CROSS JOIN n AS hecto
CROSS JOIN n AS kilo
CROSS JOIN n AS tenK
CROSS JOIN n AS hundredK
ORDER BY [date])
SELECT t.product
,d.date
,t.id
FROM @Products t
INNER JOIN dates d
ON d.date >= t.startdate
AND d.date <= t.enddate
ORDER BY product
,date 

In this tally table was used dates to limit the processing

Using a physical tally table

Code to create the physical table

DECLARE @fromdate DATE = '20000101';
DECLARE @years INT = 300;

WITH n
AS (SELECT n
FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n))
SELECT TOP (Datediff(day, @fromdate, Dateadd(year, @years, @fromdate))) [Date]=
CONVERT(DATE, Dateadd(day, Row_number()
OVER(
ORDER BY (SELECT 1)) - 1, @fromdate))
INTO dbo.dates
FROM n AS deka
CROSS JOIN n AS hecto
CROSS JOIN n AS kilo
CROSS JOIN n AS tenK
CROSS JOIN n AS hundredK
ORDER BY [date];

CREATE UNIQUE CLUSTERED INDEX ix_dbo_dates_date
ON dbo.dates([date]);

Query to retrieve the values

SELECT t.product
,d.date
,t.id
FROM @Products t
INNER JOIN dates d
ON d.date >= t.startdate
AND d.date <= t.enddate [/code

In this technique, we get the best performance but is necessary create a table with the max and min dates before the use and depending of the ranges the dates tables can be bigger than that and compromise the performance.