Syed Shanu

SQL Server Common Table Expressions (CTE)

February 23, 2017 by

What is a Common Table Expression

A Common Table Expression, also called as CTE in short form, is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. The CTE can also be used in a View.

In this article, we will see in detail about how to create and use CTEs from our SQL Server.

Syntax and Examples for Common Table Expressions

The CTE query starts with a “With” and is followed by the Expression Name. We will be using this expression name in our select query to display the result of our CTE Query and be writing our CTE query definition.

To view the CTE result we use a Select query with the CTE expression name.

Or

Common Table Expression (CTE) Types

There are two types of CTEs: Recursive and Non-Recursive

Non-Recursive CTEs

Non-Recursive CTEs are simple where the CTE doesn’t use any recursion, or repeated processing in of a sub-routine. We will create a simple Non-Recursive CTE to display the row number from 1 to 10.

As per the CTE Syntax each CTE query will start with a “With” followed by the CTE Expression name with column list.

Here we have been using only one column as ROWNO. Next is the Query part, here we write our select query to be execute for our CTE. After creating our CTE query to run the CTE use the select statement with CTE Expression name.

Output: When we run the query, we can see the below output.

Recursive CTE

Recursive CTEs are use repeated procedural loops aka recursion. The recursive query call themselves until the query satisfied the condition. In a recursive CTE we should provide a where condition to terminate the recursion.:

We will see how to create a simple Recursive query to display the Row Number from 1 to 10 using a CTE.

Firstly we declare the Integer variable as “RowNo” and set the default value as 1 and we have created our first CTE query as an expression name, “ROWCTE”. In our CTE we’ll first display the default row number and next we’ll use a Union ALL to increment and display the row number 1 by one until the Row No reaches the incremented value to 10. To view the result, we will use a select query to display our CTE result.

Output: When we run the query, we can see the below output.

CTE Query to display Date Range:

Let’s consider as there is a scenario to display the date from start date to end date all one by one as each row with details. In order to display the recursive data, we will be using the CTE Query.

Here we will write a CTE query to display the dates range with week number and day. For this we set the start and end date in parameter. Here in this example we have used the getdate() to set the start date as Todays date, and for end date we add 16 days from today.

CTE without Union All

Here we can see we have create a simple CTE query to display the RowNo, start date and week number. When we run this we will get only one result with RowNo as “1” ,StartDate as current date and week number along with week day.

Output: When we run the query, we can see the below output.

CTE with Union All

In order to display the result from start date to end date one by one as recursive, we use a Union All to increment RowNo, to add the day one by one till the condition satisfied the date range, in order to stop the recursion we need set some condition. In this example, we repeat the recursion to display our records until the date is less than or equal to the end date.

Output: When we run the query, we can see the below output.

Multiple CTE

In some scenarios, we need to create more than one CTE query and join them to display our result. In this case, we can use the Multiple CTEs. We can create a multiple CTE query and combine them into one single query by using the comma. Multiple CTE need to be separate by “,” comma fallowed by CTE name.

We will be using above same date range example to use more than one CTE query, here we can see as we have created two CTE query as CTE1 and CTE 2 to display date range result for both CTE1 and for CTE2.

Example :

Output: When we run the query, we can see the below output.

Using CTE query for SQL Table

Now let’s see on, how to use CTE query for our SQL server table data.

Create Database: First, we create a database for creating our table

Create Table: Now we create a sample Item Table on the created Database.

Insert Sample Data: We will insert few sample records for using in our CTE Query.

CTE Example:

Now we will create a simple temporary result using CTE Query. Here in this CTE Query we have given the expression name as “itemCTE” and we have added the list of Columns which we use in the CTE query. In the CTE query we display all item details with the year.

Output: When we run the query, we can see the below output.

CTE using Union ALL

Let’s consider there is a below two scenarios to display the result.

  1. The first scenario is to display each Item Price of current Year.
  2. The second scenario is to increment 10% to each Item Price for next year.

For this we use the above CTE Query. In this query, we add the UNION ALL and in UNION ALL Query we do calculation to add 10% to each item Price and show in next row with adding one year.

Output: When we run the query, we can see the below output.

Common Table Expressions (CTE) for Insert

Now we will see how to insert the CTE result to another table. For this let’s consider our above Item Table. We insert the Item details result of above CTE query to Item History table. For this first we create an Item History table.

Create Item History Table: In this history table, we add the same columns as item table along with MarketRate column as present or future Item price. Here is the query to create an ItemHistory table.

CTE Insert Example:

Here we use above same CTE query Insert the result in to the Item History table. From this query we insert both item details of present year Item price along with the next year Item prices added as 10% more.

Output: When we run the query, we can see the below output as 30 records has been inserted into our Item History table.

Select Query:

To view the item history result we select and display all the details.

Output: When we run the query, we can see the below output from item history table.

Create View with CTE Example:

Now we see how to use the above CTE query can be used in a view. Here we create a view and we add the CTE result inside the view. When we select the view as a result, we can see the CTE output will be displayed.

Example Query:

Output: When we run the query, we can see the below output as result from the View.

How to write a clean CTE Query:

Here are some basic guidelines that need to be followed to write a good CTE Query.

  1. A CTE must be followed by a single SELECT, INSERT, UPDATE, or DELETE statement that references some or all the CTE columns.
  2. Multiple CTE query definitions can be defined in a non recursive CTE.
  3. A CTE can reference itself and previously defined CTEs in the same WITH clause
  4. We can use only one With Clause in a CTE
  5. ORDER BY, INTO, COMPUTE or COMPUTE BY, OPTION, FOR XML cannot be used in non-recursive CTE query definition
  6. SELECT DISTINCT, GROUP BY, HAVING, Scalar aggregation, TOP, LEFT, RIGHT, OUTER JOIN (INNER JOIN is allowed) subqueries cannot be used in a recursive CTE query definition.

Conclusion

CTEs can be used to create a recursive query and can be used to reference itself multiple times. CTEs can be used instead of views and finally a CTE is easy and simple for readability and code maintainability.

Syed Shanu
Common Table Expressions (CTE)

About Syed Shanu

Syed Shanu is a Microsoft MVP, two-time CsharpCorner MVP and two-time Code project MVP, Author, Blogger, Speaker and always happy to share what he knows to others. He’s basically from Madurai, Tamil Nadu, India. He’s working as Technical Lead in South Korea with more than 10 years of experience on Microsoft technologies. Shanu is active in the community and always happy to share topics related to ASP.NET, MVC, ASP.NET Core, Web API, SQL Server, UWP, Azure, C#, AngularJs, Angular2. He has written more than 70 articles with various technology’s on ASP.NET, SQL Server, C#, AngularJs, Angular2, ASP.NET Core, Unity 3D, Windows Universal App and Samsung Gear App development. He’s several times TechNet Guru Gold Winner and you can found all his contribution in MSDN and also in TechNet Wiki https://social.technet.microsoft.com/profile/syedshanu/ He is also an Author of the eBook "SQL Queries For Beginners" you can download the eBook from here: http://www.c-sharpcorner.com/ebooks/sql-queries-for-beginners. View all posts by Syed Shanu

168 Views