Share via

T-SQL: Common Table Expression(CTE)

  • Article

Introduction

Common Table Expression (CTE) is one of the interesting features in SQL server. Common Table Expression or CTE is also known as WITH query because it is defined using WITH Statement.

CTE was added as part of SQL:1999 standard (SQL 3).

A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. A CTE can be thought of like a temporary result set. Also, unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

Let’s go through different types of CTE.

Types of CTE:

1. Non-Recursive CTE:

To demonstrate how Non-Recursive CTE works, we will use Information_Schema table (which is system table) and a custom table called “Employee”. Following script will create Employee table.

CREATE TABLE [dbo].[Employee] ( [FirstName] varchar(50) NULL, [LastName] varchar(50) NULL, [EmployeeID] varchar(50) NULL, ) GO

  • Single Use of CTE:

This is the simplest form of CTE and very similar to derived table concept. Here, we are just going to get table information for “Employee” table.

--Creating CTE WITH MyTable as ( SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Employee' ) --Using CTE Select * from MyTable

Steps:

1. Create CTE called MyTable.
2. Select from CTE.

Results:
**

**

  • Multiple Use of CTE

Following example shows how to use multiple CTE. One of the advantages of using CTE is it makes complex code more readable so it can be used to implement complex scenarios.

WITH MyTable as ( SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TreeNode' ), MyColumn as ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME IN (SELECT TABLE_NAME FROM MyTable) ) Select * from MyColumn

Steps:

  1. Create CTE called MyTable.
  2. Create CTE called MyColumn which is referencing MyTable.
  3. Select from CTE – MyColumn.

Result:

2. Recursive CTE:

Recursive CTE uses recursive logic to implement a hierarchical query. Let's explain recursive query with an example. In our example, we will write a query which will work through the hierarchy of the TreeNode.

Let’s look at the following TreeNode:

For example, 1 is the Root Node. 7 is child node for 1 and 3 is child node for 7.


Following SQL script will convert above tree node into SQL data.

-----Create Table TreeNode---- CREATE TABLE [dbo].[TreeNode] ( [ChildNode] INT NOT NULL PRIMARY KEY CLUSTERED, [ParentNode] INT NULL ) GO ------------Insert Tree Node data in to Table-------------- INSERT INTO TreeNode VALUES('10','8') INSERT INTO TreeNode VALUES('11','8') INSERT INTO TreeNode VALUES('8','1') INSERT INTO TreeNode VALUES('3','7') INSERT INTO TreeNode VALUES('7','1') INSERT INTO TreeNode VALUES('4','5') INSERT INTO TreeNode VALUES('9','5') INSERT INTO TreeNode VALUES('5','1') INSERT INTO TreeNode VALUES('1',null)

Purpose of the following recursive CTE query is to show the result in the hierarchical format starting from the root node and showing each levels of the tree structure.

WITH RecursiveCTE as ( --Anchor Member definition. SELECT ChildNode,ParentNode,Level = 0 FROM TreeNode WHERE ParentNode IS NULL UNION ALL --Recursive Member definition. SELECT TR.ChildNode,TR.ParentNode,Level + 1 AS LEVEL FROM RecursiveCTE R INNER JOIN TREENODE TR ON R. ChildNode = TR.ParentNode ) SELECT * FROM RecursiveCTE

Steps:

  1. Create an Anchor member. Anchor member is the member which returns a result set. In this case, it returns a base result set – root node.
  2. The recursive member returns the direct subordinate(s) of the node (i.e root node) in the anchor member result set. This is achieved by a join operation between TreeNode and Recursive CTE. Recursion operation continues till there are no result sets in the recursive member definition. For this reason, it is important to verify logic of Recursive operation else logic can cause query to go into infinite loop.
    3.Select from the CTE.

Result for the CTE Query: As you can see, it presents the whole hierarchy of the treenode row by row. The first row is the root node and has level 0 and all the child nodes to the root node has level 1 and so on….

As we can see recursive CTE is helpful in presenting hierarchical data in flat format. For example, we can use this same approach to present employees in the organization or for that matter any hierarchical data.

Please note, incorrect logic in recursive CTE query can cause infinite iteration but the good thing is CTE has built-in mechanism to stop infinite loop. By default, the number of recursion levels is set to 100.

You will see the following error in any case CTE query goes above 100 recursions:

“ Msg 530, Level 16, State 1, Line 1 The statement terminated. The maximum recursion 100 has been exhausted before statement completion.”

You can increase Max recursion value by using MAXRECURSION property of the query. MA Recursion can be set as part of Select query using CTE. In below example, Maxrecursion is set to 1000.

--select from CTE SELECT * FROM RecursiveCTE OPTION (MAXRECURSION 1000)

Reference: