CODEWITHSUNDEEP
sql-servert-sqlrecursioncommon-table-expression
Shawn Li
Shawn Li

Reputation: 417

Why does a recursive cte not return duplicates?

I happened to encounter the following sql statement

DECLARE @SDate DATETIME
DECLARE @TDate DATETIME
SET @SDate = '2011-9-01 00:00:00.000'
SET @TDate = '2011-10-01 00:00:00.000'
;WITH CTE AS
(
    SELECT @SDate Date
    UNION ALL
    SELECT Date + 1 FROM CTE WHERE Date + 1 <= @TDate
)
SELECT Date FROM CTE

To my knowledge, this statement should return quite a lot of duplicates as each iteration in the recursion increments the entire column by 1 and the result should be a UNION ALL of all the intermediate tables.

However to my surprise the resulting table contains no duplicates, just all the distinct dates from 2011-9-01 to 2011-10-01. Why is this the case?

Upvotes: 1

Views: 300

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

Don't think of recursive CTEs as actually being "recursive". They are inductive.

The anchor portion of the CTE runs. Then the "recursive" part keeps adding rows -- based on the most recently added rows. So, the first iteration adds the first date. The second iteration takes the just-added date and adds one to it.

The third iteration only considers the second (last) date, not both.

The terminology is not great, but they do not generate duplicates by reprocessing a given row more than once.

Upvotes: 4

paparazzo
paparazzo

Reputation: 45096

Did you test this? 

DECLARE @SDate DATETIME
SET @SDate = '2011-9-01 00:00:00.000'
select @SDate; 
select @SDate + 1;

Upvotes: -1

Related Questions