Recursivity in SQL Server with Common Table Expressions

Probably you never thought that there is a nice way to make recursivity in a SQL Server Query but… yes… since CTE’s were launched in version 2005 is there.


So, I was thinking to offer a good and easy example where we can take power of this capability and found the Adventure Works DB (tested in both AdventureWorks2016 & AdventureWorks2016_EXT) is a very good example as already includes the required structures because it includes a field called OrganisationLevel that links the employee with the BusinessEntityID for the corresponding line manager.

In the sample below, we are searching for all employees with the list of managers up to the CEO. I have calculated both in text (Job Title) and Business Entity ID (Manager ID sequence), both separated by ” | “.

This functionallity opens several doors for a lot of good practises. You can nest CTE’s, use the same CTE several times within a query or even use them inside Views, SP’s or Functions and give functional names to very complex subqueries to make the code much easier to understand.

In the recent past I have used several times when need to mix complex queries with tricky table & field names. Within the CTE can extract the information required and then link more than one CTE with friendly names. The final product is very readable and much easier to maintain.