That's actually going to be a bit slow and you do stand the chance of running out of rows because sys.all_objects isn't that big.

The reason why it will be slow is because you're calculating dates for every row there is in sys.all_objects and you probably won't usually need all those rows.

Here's a comparison against the code above and the code that limits the number of rows being generated by the CTE. Look at the actual execution plan and play with SET STATISTICS to see what I mean in the comments.

DECLARE @StartDate DATE

DECLARE @EndDate DATE

DECLARE @RotationDays INT

SET @StartDate = '01/01/2015'

SET @EndDate = '12/31/2015'

SET @RotationDays = 4

--===== Original method generates more than 2000 dates (and 33 reads) rather than just the 91 that are needed.

-- Also has an extra ROW_NUMBER() calculation that's just not needed.

-- In theory, could also run out of "rows" from sys.all_objects.

;WITH result AS

(

SELECT DATEADD(DAY, (ROW_NUMBER() OVER (ORDER BY object_id) - 1) * @RotationDays, @StartDate) StartDate,

DATEADD(DAY, ((ROW_NUMBER() OVER (ORDER BY object_id) - 1) * @RotationDays) + @RotationDays - 1, @StartDate) EndDate

FROM sys.all_objects

)

--INSERT INTO TABLE here, if you need to

SELECT StartDate, EndDate

FROM result

--LEFT OUTER JOIN table, if you need to join people here

WHERE EndDate <= @EndDate

;

------------------------------------------------------------------------------------------------------

--===== Faster becausse this only generates the 91 rows (and only 5 reads) that are needed

-- and only does one ROW_NUMBER() calcualation.

-- Since the cross join is guaranteed to gen over 16 MILLION rows when needed,

-- there's virtually no chance of ever hitting the proverbial wall.

-- Using a properly formed Tally cte would take the reads down to almost nothing.

WITH

cteStartDates AS

(

SELECT TOP ((DATEDIFF(dd,@StartDate,@EndDate)+1)/@RotationDays)

StartDate = DATEADD(dd,(ROW_NUMBER()OVER(ORDER BY (SELECT NULL))-1)*@RotationDays,@StartDate)

FROM sys.all_columns ac1

CROSS JOIN sys.all_columns ac2

)

SELECT StartDate

,EndDate = DATEADD(dd,@RotationDays-1,StartDate)

FROM cteStartDates

WHERE DATEADD(dd,@RotationDays-1,StartDate) <= @EndDate

;

Does such a "small" increase in performance and decrease in resource usage actually make a difference? If you only run it once or twice a day, then not so as anyone would notice. If you run it 40,000 times a day, then the cumulative resource usage starts to be noticed, a lot. I always plan on the larger scale. Like Granny used to say, "Mind the pennies and the dollars will take care of themselves".