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 »


  • @DesertDBA: Is it possible to rename an existing SQL 2012 Availability Group? #sqlhelp
  • @AirborneGeek: @tjaybelt How well the Gateway will work for you will depend on what your end goal is. #sqlhelp
  • @AirborneGeek: @tjaybelt As Tom said, you can use the Desktop app, or, you can use the Personal Gateway to get to an on-prem DB.#sqlhelp
  • @tjaybelt: #sqlhelp where does my simple SQL data need to reside for #PowerBI to access it? does it need to be in a cloud azure sql db? or local?
  • @AnupWarrier: I understand that SQL2014 SP1 with a CU has a fix, however dont think 2008 R2 has any #sqlhelp 2/2
  • @AnupWarrier: If I disable TLS1.0 on any servers which hosts SQL 2008 R2, then SQL Service wont start...Is this a true statement..#sqlhelp 1/2
  • @flyingcod: 'Cannot execute as the db principal because principal dbo does not exist' All dbs have full perms #servicebroker error. #sqlserver #sqlhelp
  • @Joe_E_O: Can you get the object from the offset for error 1117 - or do you need DBCC output #SQLHELP
  • @retracement: @kevine323 Don't do anything until you have checked the logs! And no, don't go removing WSFC. #sqlhelp.
  • @kevine323: 2/2 but wanting to make sure 0 downtime. should I just restart cluster services? Win 2012R2 w/ SQL 2012. Cluster is multi-subnet. #sqlhelp

Welcome , today is Friday, August 28, 2015