What is Common Table Expression in SQL Server?

Imagine you are writing a very complicated query involving a lot of tables and joins. Either you can write the query all at once. But that would make debugging difficult if there is some error in the query. Otherwise, you can break the query into small parts, get the data into temporary tables, and combine these temporary tables in a query to get the final output. There are many ways to do it. One way is to use a Common table expression or CTE in SQL server. Over the next few paragraphs, we will learn more about common table expressions, how to use them, and what are its advantages and disadvantages. You can learn about Common Table Expression by enrolling in an online SQL server training course and shape your ever-growing SQL career. 

What is Common Table Expression in SQL?

Common Table Expressions (CTE) were introduced into standard SQL to simplify various classes of SQL Queries for which a derived table was just unsuitable. CTE was introduced in SQL Server 2005. The common table expression (CTE) is a temporary named result set that we can reference within a SELECT, INSERT, UPDATE, or DELETE statement. We can also use a CTE in a CREATE view as part of the view’s SELECT query. In addition, as of SQL Server 2008, we can add a CTE to the new MERGE statement. 

A CTE (Common Table Expression) is a one-time result set that only exists for the duration of the query. It allows us to refer to data within a single SELECT, INSERT, UPDATE, DELETE, CREATE VIEW, or MERGE statement's execution scope. It is temporary because its result cannot be stored anywhere and will be lost when a query's execution is completed. It first came with SQL Server 2005 version. A DBA always preferred CTE to use as an alternative to a Sub query/View. They follow the ANSI SQL 99 standard and are SQL-compliant.

Data is an integral part of businesses, and there’s great demand for administrators. Therefore go through SQL DBA career path if you too want to set yourself up for this role.

Difference between CTE and Table variable

Difference between CTE and Temp Table

Do you love working with data? Or want to pursue a career in the Microsoft SQL Server Database domain? But feel stuck with doubts? A comprehensive SQL career path will help you explore all the career options.

Different Types of CTE:

CTE SQL Server is divided  into two broad categories:

1. Recursive CTE
2. Non-Recursive CTE

Recursive CTE

Recursive CTEs use repeated procedural loops, aka recursion. The recursive query calls them until the query satisfies the condition. In a recursive CTE, we should provide a where condition to terminate the recursion.

Using a CTE, we will see how to create a simple Recursive query to display the Row Number from 1 to 10.

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 of 10. To view the result, we will use a select query to display our CTE result.

The query is as follows

Declare @RowNo int =1;
;with ROWCTE as  
   (  
      SELECT @RowNo as ROWNO    
UNION ALL  
      SELECT  ROWNO+1  
  FROM  ROWCTE  
  WHERE RowNo < 10
    )  
SELECT * FROM ROWCTE

The output looks like the below

Still, have doubts regarding career benefits, average SQL developer salary, and the top companies hiring SQL developers around the world? Get in touch with our consultant today!

Non-Recursive CTE

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

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

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

The query looks like below:

with ROWCTE(ROWNO) as  
   (  
     SELECT 
  ROW_NUMBER() OVER(ORDER BY name ASC) AS ROWNO
FROM sys.databases 
WHERE database_id <= 10
    )  
SELECT * FROM ROWCTE 

The output is as follows:

Next, we would discuss a special case in CTE i.e. Multiple CTE.

Multiple CTE

Sometimes, we'll need to create multiple CTE queries and join them together to see the results. We may use multiple CTEs concepts in this scenario. We need to use the comma operator to create multiple CTE queries and merge them into a single statement. The CTE name must precede the "," comma operator to distinguish multiple CTE.

Multiple CTEs help us simplify complex queries that are eventually joined together. Each complex piece had its own CTE, which could be referenced and joined outside the WITH clause.

The syntax looks like this below

WITH   
cte_name1 (column_names) AS (query),  
cte_name2 (column_names) AS (query)  
SELECT * FROM cte_name  
UNION ALL  
SELECT * FROM cte_name; 

Let us now check out a real-life example.

In this example, we have defined the two CTE names, customers_in_newyork and customers_in_california. Then the result set of subqueries of these CTEs populates the CTE. Finally, we will use the CTE names in a query that will return all customers in New York and California State.

The query looks like the one below:

WITH   
customers_in_NewYork  
AS (SELECT * FROM Sales.Customer WHERE TerritoryID = 1),  
customers_in_California  
 AS (SELECT * FROM Sales.Customer WHERE TerritoryID = 4)  
SELECT * FROM customers_in_NewYork  
UNION ALL  
SELECT * FROM customers_in_California;

The output of the query is as below.

Why we need Common Table Expression in SQL

• It is useful when defining a derived table multiple times within a query.
• It is useful when we need to create an alternative to a view in the database.
• It is useful when we need to perform the same calculation multiple times on multiple query components simultaneously.
• It is useful when we need to use ranking functions like ROW_NUMBER(), RANK(), and NTILE().

CTE Syntax in SQL Server

The CTE syntax includes a CTE name, an optional column list, and a statement/query that defines the common table expression (CTE). After defining the CTE, we can use it as a view in a SELECT, INSERT, UPDATE, DELETE, and MERGE query.

The following is the basic syntax of CTE in SQL Server:

WITH cte_name (column_names)   
AS (query)     
SELECT * FROM cte_name;  

In this syntax:

• We have first specified the CTE name that will be referred to later in a query.
• The next step is to create a list of comma-separated columns. It ensures that the number of columns in the CTE definition arguments and the number of columns in the query must be the same. If we have not defined the CTE arguments columns, it will use the query columns that define the CTE.
• After that, we'll use the AS keyword after the expression name and then define a SELECT statement whose result set populates the CTE.
• Finally, we will use the CTE name in a query such as SELECT, INSERT, UPDATE, DELETE, and MERGE statement.

It should keep in mind while writing the CTE query definition; we cannot use the following clauses:

1. ORDER BY unless you also use as TOP clause
2. INTO
3. OPTION clause with query hints
4. FOR BROWSE

SQL CTE Examples

The following CTE example prints all the days of the week in a table format.

The query is as follows:

WITH cte_numbers(n, weekday) 
AS (
SELECT 
0, 
DATENAME(DW, 0)
UNION ALL
SELECT    
n + 1, 
DATENAME(DW, n + 1)
FROM  
cte_numbers
WHERE n < 6
)
SELECT 
weekday
FROM 

cte_numbers;

The output is as below:

Advantages of CTE

Some of its advantages are given below:

• SQL CTE facilitates code maintenance easier.
• Sql CTE increases the readability of the code.
• It increases the performance of the query.
• SQL CTE makes it possible to implement recursive queries easily.

Disadvantages of CTE

• Sql CTE members cannot use the keyword clauses like Distinct, Group By, Having, Top, Joins, etc.
• The CTE can only be referenced once by the Recursive member.
• We cannot use the table variables and CTEs as parameters in stored procedures.
• We already know that the CTE could be used in place of a view, but a CTE cannot be nested, while Views can.
• Since it's just a shortcut for a query or subquery, it can't be reused in another query.
• The number of columns in the CTE arguments and the number of columns in the query must be the same.

SQL Testing Training

• Personalized Free Consultation
• Access to Our Learning Management System
• Access to Our Course Curriculum
• Be a Part of Our Free Demo Class

Sign Up Now

Conclusion

Over the last few paragraphs, we have learned about the different aspects of CTE, how it works, when to use it and its advantages and disadvantages, and how to become a SQL database administrator. This document can be a head start on learning more about CTE. We hope this write-up will give the readers enough thought to do a more detailed study about the topic.