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:
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.
Rich Brown (blog) – Rich has a great code sample showing how CTEs can be used to remove duplicates from a set. I’ve never tried deleting from a CTE before but apparently it is possible. Neat stuff!
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.
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!