A great big thanks to all who contributed posts to T-SQL Tuesday #18! There were 23 posts in all and they covered a wide variety of tips and tricks that can be done with CTEs! If you’re looking to learn a ton, read through all the awesome content below! Without any further ado, here’s a quick look at what everyone wrote:
Rob Farley (blog) – Rob has some great thoughts on how CTEs fit in with other types of table expressions, and laments the non-existence of temporary views.
Naomi Nosonovsky (blog) – Naomi submitted a previous post that gives several examples of simplifying queries using CTEs and also has links to some great resources.
Bradley Ball (blog | @SQLBalls) – Bradley talks about how CTEs came in handy during a job interview for solving the infamous “Fizz Buzz” problem, and also shows us how they can be used to simplify a DMF query to gauge the scan & update patterns of indexes.
Thomas Rushton (blog | @ThomasRushton) – Thomas brings up an interesting point about how CTEs are executed each time they are referenced, which can lead to absurd execution plans and slow run times. He shows how CTEs can be used to populate temporary tables which can solve many of these problems.
Noel McKinney (blog | @NoelMcKinney) – Noel has an interesting example of how to use OPENROWSET within a CTE to keep your syntax legible, and I’m all for clean code!
Jen McCown (blog | @JenniferMcCown) – Jen has offered up a previous post with a good list of N things to know about CTEs. She even gives an example of what happens when you don’t use a semicolon on the preceding statement.
Jes Borland (blog | @grrl_geek) – Jes challenged herself to learn recursive CTEs, find a use for one, and write a blog post. She did an excellent job and came up with this nifty post on how to build a dates table.
Simon (blog) – Simon came up with an example that demonstrates how a CTE can be used to return a dataset categorized by GROUP BY that also shows the result of TOP in the same query.
Nic Cain (blog | @anonythemouse) – Nic tells the tale of a job interview where he was asked to provide a solution to a problem using recursion, and he did so using CTEs. He was then told that wasn’t correct, so he provided two other much more verbose methods. I believe the first method should have been completely acceptable.
David Howard (blog | @daveh0ward) – Dave shows us how recursive CTEs can be used to perform an “ungroup” operation.
Robert Cook (blog | @sqlmashup) – Robert wrote up a script to generate a numbers table using CTEs.
Thanks again to everyone, and a special thank you to Adam Machanic (blog | @AdamMachanic) for starting the T-SQL Tuesday movement and allowing me to host this month. Stay tuned until next month when Allen Kinsel (blog | @sqlinsaneo) is hosting!