This blog post is written in response to the T-SQL Tuesday hosted by Bob Pusateri. He has picked very interesting topic which is related to APPLY clause of the T-SQL. When I read the subject, I really liked the subject. This is very new subject and it is quite a interesting choice by Bob.
Common Table Expression (CTE) are introduced in SQL Server 2005 so it is available with us from last 6 years. Over the years I have seen lots of implementation of the same as well lots of misconceptions. Earlier I had presented on this subject many places. Here is the quick note on the subject, which I used to keep with me.
Please note this note is in no particular order:
- CTE are not replacement of the Temp Table or Temp Variable Table
- Always begin CTE with semi-comma
- The scope of the CTE is limited to very first SELECT statement
- Usually the execution plan of the CTE like sub-query but there are cases when it can be different as well
- Use MAXRECURSION to prevent infinite loop in recursive CTE
Here are few blog posts where I used to demonstrate regarding how to begin with CTE.
Beginning CTE:
- SQL SERVER – Simple Example of Recursive CTE
- SQL Server – Multiple CTE in One SELECT Statement Query
- SQL SERVER – SQL SERVER – Simple Example of Recursive CTE – Part 2 – MAXRECURSION – Prevent CTE Infinite Loop
Read world use case:
- SQL SERVER – 2005 T-SQL Paging Query Technique Comparison (OVER and ROW_NUMBER()) – CTE vs. Derived Table
- SQL SERVER – 2005 – 2008 – Delete Duplicate Rows
I really had great time writing this blog post as all the memories of working with CTE is brought back when it was newly introduced.
Reference : Pinal Dave (https://blog.sqlauthority.com)
