Dinesh Asanka
Result set

Common Table Expressions for Database Developers

June 24, 2021 by

Introduction

Though the Common Table Expressions (CTE) were introduced to SQL Server more than a decade ago with the SQL Server 2005 version, still this is not much utilized by database developers due to the unawareness. This article provides what and how you can utilize the CTE effectively and efficiently.

What is a Common Table Expression (CTE)

Common Table Expressions can be explained as a temporary view. However, unlike the view, common table expression is not physical. This means that CTE is valid only to the scope of the query. However, you can write a CTE inside a stored procedure or User Defined Functions (UDFs) or triggers or views. However, you cannot implement CTEs inside indexed views.

First, let us see the syntax of CTE. Let us use the Microsoft Sample database, AdventureWorks for the demonstrations.

In the above query, we have used Order details and the name of the CTE is OrdersIn2011. This is equal to creating a view named Ordersin2011.

Common Table Expressions vs Temp Tables vs Table Variables

There are a few other options to store temporary data in SQL Server. Those options are CTEs, Temp Tables and Table Variables.

  • Temp tables and table variables need explicit inserts to populate those objects while CTE does not need separate insert statements to populate. CTE will be populated with the definition itself
  • Table variable scope is for the batch while CTE’s scope is only for the query. In the case of temp tables, the table is available for the session. There are options in temp tables where you can create ## temporary tables that can be assessable within multiple sessions
  • CTE always uses memory whereas temp tables always use the disk. Table variable uses both. Let us verify this by means of write transactions/sec counter which is shown in the below figure

Comparison of write transactions/sec counter for #table, Table Variable and CTE

As you can see, CTE is completely utilizing the memory while the other two objects are using the disk. This means that we should not use CTE for a large volume of data.

Implementation for CTEs

As a developer, you need to understand where you can implement CTE for different purposes.

Recursion Queries

Recursion is one of the popular implementations for CTEs. Manager – Employee recursive relationship is one of the common examples. Let us create a table and populate some data.

Then use the following query to populate this table.

In the above data, Baker Downs is the boss of the company. Suppose, you want to find out who all employees are reporting to Clio Skinner (EmployeeID =2). This can be achieved by using the Common Table Expression as shown in the below script.

Output for the above script is shown in the following figure that shows the entire list of recursive employees.

Generation of recursive employees by using CTEs

Now let us say, you do not want the entire list, but you want to limit the level to 2. Then you can add the MAXRECURSION option to the query as follows.

Though the above query will generate the correct result, it will raise the following error.

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

Since this query is generating the error, as a developer you will run into the issue of raising an exception. In case this query is in a transaction, the transaction will fail. However, including an additional recursive column called Level and adding a filter to it will solve the issue.

Finding Duplicates

Duplicates are another issue that developers have to fix in their databases. Let us see how we can use CTEs to identify duplicates in a table. Let us duplicate a record in the employee table and run the following common table expression.

This is the output for the above query.

Identification of duplications of employees by using CTEs

As you can see that the duplicates were detected from the above query.

Multiple CTEs

You can use multiple CTEs in a single query as shown below.

You will see that, you join CTEs like you join tables and views. Here are the results for the above query.

Using multiple CTEs in a same query,

Ranking by Aggregates

You may have to rank by the aggregates. This is something that can be achieved by the CTEs as shown below.

The below is the resultset for the above query.

Result set

Conclusion

Common Table Expressions or CTEs are one of the techniques that can be used to store intermediate results in memory. There are a few use cases for CTEs such as recursive queries, identification of duplicates, joining multiple CTEs, ranking by aggregate are the common use cases for the CTEs. It is important to note that, we should not use CTEs when the data volume is high.

Dinesh Asanka
168 Views