T-SQL Tuesday #18 – CTEs

Comments: 3 Comments
Published on: May 10, 2011

To CTE or not to CTE, that is the Question

So my post really has nothing to do with the title.  This is not a post to help you determine whether a CTE is appropriate or not.  Or is it?

This month, we have the 18th installment in the TSQLTuesday series.  We are being hosted by Bob Pusateri  (Blog|@SQLBob) this month.  The essence of the topic this month is around CTEs (common table expressions).  There are a great many uses for a CTE in SQL server and this was a nice addition to the product.

One thing I like about CTEs is how much cleaner the code looks to me.  Another benefit for me is the recursion that is available through the use of a CTE.  An observation about CTEs is that a common use would be to use them to replace inline derived tables (which lends to cleaner looking code for some).

Think Think Think

I gave this topic a good long thought.  As I thought about the topic, I came to the conclusion that I had nothing new or unique on the subject.  I did however have some scripts that I posted once upon a time that would work very well for this topic.  Though it is a bit of a cop out, it is an appropriate solution for this month.

In the case of the CTE that I have chosen, there are multiple CTEs being used.  I use the CTE to recurse through data, and then to recurse that same data again – in reverse.  This particular script was created to traverse through system catalogs and create a hierarchy of table relationships.  I use this hierarchy to better understand the structure of the database and the interrelationship of the data between objects.  It is a cheap way of mapping out the objects in an effort to better understand it.

I had thought about using this script once upon a time for a different TSQLTuesday, but thought better of it that time.  Since the original post is more than a year old, it is a good time to bring it up and use it again.  Without further adieu, you can read about that script and CTE here.  I hope you enjoy.

3 Comments - Leave a comment
  1. Bob Pusateri says:

    Hi Jason – thanks so much for contributing to T-SQL Tuesday!

  2. […] Brimhall (blog | @sqlrnnr) – Jason shared some scripts he’s posted previously that involve […]

  3. […] this month we had a TSQL Tuesday on the topic of CTEs.  I bailed on my submission because I already posted some CTE examples and was bone dry on what I could write […]

Leave a comment

Your email address will not be published. Required fields are marked *

May 2011
« Apr   Jun »


  • @tjaybelt: #sqlhelp I need a hadoop hdinsight expert that can help me troubleshoot some immediate issues i am having. We can pay.
  • @SQLKohai: Am I missing a cool SSDT plugin or is it impossible to get an full error except by copying the text and pasting into notepad? #sqlhelp
  • @SQLSoldier: @briancarrig Same as a table. Only if workload/activity warrants it. #sqlhelp
  • @briancarrig: Is a dedicated filegroup for service broker queues a recommended best practice? #sqlhelp
  • @mrdenny: @whoisyarad you mean to remove a column from a table? ALTER TABLE MOCK_DATA DROP COLUMN {ColumnName}; #sqlhelp
  • @whoisyarad: How do i delete column within SQL? SELECT * FROM Practice.dbo.MOCK_DATA Then Replaced the Select with delete #sqlhelp
  • @SQLHA: @kleegeek It's pretty hard to do that. I have yet to find a consistent way. #sqlhelp
  • @kleegeek: Looking for a reliable way to trigger a BSOD on Win2012R2 for some availability testing. Any ideas? #sqlhelp
  • @SqlrUs: @SQLChao Password manager/generator with complex passwords. #sqlhelp
  • @SQLChao: How do you come up with your passwords for service accounts? #sqlhelp

Welcome , today is Monday, November 30, 2015