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.
