T-SQL Tuesday #18 Wrapup

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:

Matt Whitfield (blog | @atlantis_uk) – Matt explains recursive CTEs and uses them to query a permission hierarchy.

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.

Pinal Dave (blog | @pinaldave) – Pinal was thinking back to SQL 2005 when CTEs were first introduced and listed some observations and links to other resources about them.

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!

Joe Casella (blog) – Joe went above and beyond and wrote two posts. The first compares CTEs to derived and temporary tables. The second uses a CTE to create a calendar table.

Steve Jones (blog | @way0utwest) – Steve shows us how CTEs can be used to find the TOP n values over a group without the need for any subquery nastiness.

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!

Steven Ormrod (blog | @SQLAvenger) – Steven has some queries that use CTEs to provide administrative data, including info about the buffer pool and plan cache.

Amit Banerjee (blog | @banerjeeamit) – Amit has an in-depth example for using CTEs to parse out XML and even provides load times and hardware info to boot.

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.

Robert L. Davis (blog | @SQLSoldier) – Robert shows us a nifty setup using CTEs to create date ranges in a pivot table and return everything nice and pretty in one fell swoop.

Brad Schulz (blog) – Brad gives several examples of features of CTEs that you may not know about, including a handy prank for unsuspecting co-workers!

Jason Brimhall (blog | @sqlrnnr) – Jason shared some scripts he's posted previously that involve CTEs.

John Morehouse (blog | @sqlrus) – John has an interesting code sample demonstrating how he used a CTE to filter a result set.

Ricardo Leka (blog | @BigLeka) – Ricardo blogged a script using CTEs to aggregate wait stats. I don't speak Portuguese, but I'm glad Google Translate does!

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!