CTEs – Common Table Expressions in SQL Server

CTEs (Common Table Expressions) are very useful in creating queries that need a recursion. CTEs are also very frequently asked interview questions for backend developers.

Lets discuss a scenario and implement it using CTE to understand the same. Consider, we have a Person table which contains the Name, Age, and parent from the same table.

CREATE TABLE [dbo].[Person](
	[Id] [int] NOT NULL,
	[Name] [nvarchar](50) NULL,
	[Age] [int] NULL,
	[parentId] [int] NULL
)

Let us insert the data into this table:

Now we need to write a query which will return the name as well as parent name of every person, except the one whose parent does not exist in the database, obviously. So now we need a recursive query which will keep fetching the record of the parent id, until the parent ID is NULL.

Now, just like in any recursive function/method that we create in any language, lets say, C# or C++ for example, we need 2 conditions for the function to work correctly:

• The end Condition – The condition, which when true, will return from the method so that the recursion does not go on infinitely.
• The recursive condition – The code, which will call the same method until the end condition is occurred.

The CTE has the same requirement. We call it the anchor part and the recursive part. Below will be the CTE for the requirement we discussed above.

;WITH cte_person AS(
	select id,[Name],[age],CAST('' AS NVARCHAR(50)) AS ParentName  
        from dbo.Person   
        where parentId is null -- Anchor Part

	UNION ALL

	Select P.id,P.[name],P.[age],CP.[Name] ParentName from person 
        P inner join cte_person CP ON CP.id = P.parentId  
        -- Recursive Part
)

The Red part above is the anchor part. Which will fetch the data for the all the persons, for whome we do not have a parent in the database.

The Blue part is the recursion part. See that we have joined the person table with the CTE itself, which contains the person table itself. But what will happen is that the first join will be with the records that do not have any parent in database, i.e. the records from the anchor query. Now, the result of the records returned will again be inner joined as per the recursive part until all the records are matched.

Below is the result from the query

The CTE Runs as per the below Flow Chart:

Below are the points to remember while creating CTEs:

• The selected columns must match in the anchor part and the recursive part, otherwise below error may occur –

types don’t match between the anchor and the recursive part in column

• The end condition must occur at least once otherwise the CTE may never end.

Below are the benefits and usages of CTE:

• It makes the query more readable and easy to understand as compared to using any other query, like CURSORs or WHILE loop.
• CTE can be used in creating VIEW, or in update query, etc.
• SQL Server recursion is limited to 32 levels, so CTE is better approach in such a case

Please let us know about your opinion on the comment box.