>Since the query is about ranges, groups of records, a recursive query is not the first solution of choice to me. Recursive queries are best applied when you've got to weed through recursive data structures.

One thing, recursion with CTEs has been in SQL Server since 2005. Yes, recursive queries help in querying through recursive/parent-child relationships. (The website www.babybrowns.com has some SQL code running behind the scenes that I wrote for them years ago, to get parent/child relationships on products/brands/etc).

But they also have the benefit of serving as the equivalent of a "perform varying". When I saw Don's example, the notion each subsequent row representing a new set of 4-15 minute segments, I think of the capability in recursive queries to implement that pattern. (Though I had to derive a dummy grouping column to get it to work, so it did wind up a bit complicated).

At a very basic level, if you wanted to create a set of numbers from 1-N, and all you can write is a SQL SELECT statement, you can do something like this (and I'm pretty sure you know this, just more for benefit of anyone who doesn't but would like to know)

;WITH NumbersCTE as  
    (select 1 as Counter
	    UNION ALL
		   SELECT Counter + 1 AS Counter from NumbersCTE 
		            WHERE Counter < 25)

select * from NumbersCTE

This can come in handy if someone wants to create a list of Dates. For instance, if I wanted to create a list of Saturdays from today until the end of 2018...

;WITH DateListCTE AS
   (SELECT  cast('7/19/2014' as Date)  AS SaturdayDate
      union all
	        SELECT DATEADD(D,7,SaturdayDate) FROM DateListCTE 
			        WHERE YEAR(DATEADD(D,7,SaturdayDate))  < 2019 )
SELECT * from DateListCTE  option (maxrecursion 300)   -- since the default # of "loops" is 100

I don't think these are better (or worse) than things like correlated subqueries, just different. If a person is going from shop to shop as a contractor, they might very well see code like this, so at the very least understanding these approaches can help.

And yes, correlated subqueries are a great way to solve so many problems of querying for an offset. Many queries that calculate moving averages use them, and they're great.


MS has certainly released a good # of new language features. Some are great enhancements over what was done previously (like MERGE, or being able to pass a read-only table as a parameter)....some are certainly nice but not what I would call earth shattering (like GROUPING SETS). And some are certainly big (like the new analytic functions in 2012) that offer great power, but definitely require a learning curve.

I mentioned to Craig B a few weeks ago that there's a pattern with MS development tools. People will come up with an approach to solve a problem...and then years later MS will implement a feature to make solving that problem a bit easier (less code through a new function, etc.). Some will say, "wow, that's great"....others might sometimes say, "OK, nice, but I solved this years ago with older features, and the new function is just different, but not necessarily any better". And both might be right.


Oh, and I've been writing SQL since 1992 when Fox 2.0 came out. :)