Recursive CTE in SQL Server

CTE was one of the most standout debut in SQL Server 2005 version. I personally as a developer give a million salute to those people @ Microsoft, who invented and implemented such a great functionality in SQL Server for us. Now coming to the topic CTE, it behaves like a table variable and/or temp table, just to give a little clarity, I have differentiated CTE with table variable and temp table in brief.

  • CTE is a temporary result set and most likely a derived table from a set of query.
  • Unlike the # table or table variable, CTE’s are not written to the disk and does not need deallocation after use.

Difference between CTE, Table Variable and Temp Table

CTE Table Variable Temp table
Limited to the scope Limited to the scope and batch Limited to a session
CTE does not need any special permission and can be executed having just read only permission on database. Does not need any special permission. This needs special permission since it creates physical table on tempdb.
Does not use tempdb It uses tempdb and physical disk It uses tempdb and physical disk
Identity can’t be created Identity can be created Identity can be created
Constraints can’t  be created Constraints can be created Constraints can be created
Index can’t  be created Index can be created and applied Index can be created and applied
Primary key can’t  be created Primary key can be created Primary key can be created
Pre-compiled Pre-compiled Not pre-compiled
Can be used in a view Can’t use in a view Can’t use in a view
Can’t  be called within an inner procedure in the same session Can’t  be called within an inner procedure in the same session Can be called within an inner procedure in the same session
Does not need deallocation Does not need deallocation Deallocation needed or it automatically deallocates once the session is closed
CTE has to be the first statement in the scope or batch  so ; is mandatory before WITH clause Can be called anywhere in the batch Can be called anywhere in the batch
Can be nested Can’t  be nested Can’t  be nested

Demo query:

CTE-Sample
Example 01: A list of all managers from employee table

CTE-Example-01
CTE-Example-01

Example 02:

  • Two CTEs in a same statement and reusable of one CTE in another.
  • cteUS gets a list of employees, who are from United States. and cteOther gets a list of employees, who are not in cteUS.
CTE-Example-02
CTE-Example-02

Example 03: Hierarchical structure of all employees (RECURSION)

CTE-Example-03
CTE-Example-03
  • [Level] column makes the hierarchy from top to bottom.

Example 04: Hierarchical structure of an employee from bottom to top most (RECURSION)

CTE-Example-04
CTE-Example-04
  • EmpID: 8 = Nirav Bhavsar
  • Nirav Bhavsar reports to Maulik Patel
  • Maulik Patel reports to Avani Panchal
  • Avani Panchal reports to Himanshu Patel

Example 05: Find all Sundays between two dates (RECURSION)

CTE-Example-05
CTE-Example-05

Example 06: Split comma separated string into columns (RECURSION)

CTE-Example-06
CTE-Example-06

Example 07: Display a string in increasing order (RECURSION)

CTE-Example-07
CTE-Example-07

Prasad Sahoo

Prasad Sahoo, is an enthusiast of Microsoft technologies in general and a passionate database professional, mainly focusing on SQL Server performance tuning and business intelligence. He has contributed a long time of his career working on SQL Server and other RDBMS. If he is not busy with SQL stuff then he must be watching some Hollywood movies.

5 thoughts on “Recursive CTE in SQL Server

Leave a Reply

%d bloggers like this: