2 ms·
The key thing that is important is that a recursive CTE allows for the retrieval of data from graph-like table schemas. I'd sort of forgotten about this featur
by chris_wot 11y ago
The key thing that is important is that a recursive CTE allows for the retrieval of data from graph-like table schemas.
I'd sort of forgotten about this feature of SQL! But common table expressions and window functions actually keep the expressiveness of SQL and fill in gaps that just straight subselects, derived tables and joins alone can't always achieve.
Take for instance the rolling update he highlights. Before SQL Server got windowing functions (and boy, were Microsoft ever slow in implementing those in SQL Server!) the way to do a reasonable fast query (i.e. Not use cursors) was one a guy called Jeff Moden came up with in 2012:
http://www.sqlservercentral.com/articles/T-SQL/68467/ http://www.sqlservercentral.com/articles/T-SQL/68467/
It relied on undocumented behaviour he observed in cluster indexes - so long as he could keep the order of rows returned by the database, he figured out a very fast method of doing a running total.
Obviously it was a hack and couldn't really be used, but it shows what folks can do when their favourite vendor doesn't supply critical features needed to process data in their favourite tool quickly :-)