As you know I have been writing on Columnstore Index for quite a while. Recently my friend Vinod Kumar wrote about SQL Server 2012: ColumnStore Characteristics. A fantastic read on the subject if you have yet not caught up on that subject. After the blog post I called him and asked what should I write next on this subject. He suggested that I should write on DMV script which I have prepared related to Columnstore when I was writing our SQL Server Questions and Answers book. When we were writing this book SQL Server 2012 CTP versions were available. I had written few scripts related to SQL Server columnstore Index. I like Vinod’s idea and I decided to write about DMV, which we did not cover in the book as SQL Server 2012 was not released yet. We did not want to talk about the product which was not yet released.
This is followed up a blog post of my earlier blog post on the same subject – Introduction to Hierarchical Query using a Recursive CTE – A Primer. In the article we discussed various basic terminology of the CTE. The article further covers following important concepts of common table expression. Let us learn in this video how to do Hierarchical Query using a Recursive CTE.
What is a Common Table Expression (CTE)
Building a Recursive CTE
Identify the Anchor and Recursive Query
Add the Anchor and Recursive query to a CTE
Add an expression to track hierarchical level
Add a self-referencing INNER JOIN statement