Introducing Common Table Expressions in SQL Server

Common Table Expressions, or CTE for short, is simply a technique to create a temporary set of records that can be referenced within an INSERT, SELECT, UPDATE or DELETE statement.

Common table expressions were introduced by Microsoft in SQL Server 2005. They are not stored as objects in the database memory as their lifespan is equal to the execution time of the query. As soon as a query completes they are removed from the database memory. CTE can be referenced in a query as many times as you want and they can also be self-referencing.

Let’s create a database with a student table and insert some dummy student records in it. We will use this database to write CTE queries. As always be sure you are well backed up before experimenting with a new code. See this article on SQL backup if you’re not sure.

Execute the following queries on your server.

CREATE DATABASE schooldb

CREATE TABLE student
(
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    gender VARCHAR(50) NOT NULL,
    DOB datetime NOT NULL,
    total_score INT NOT NULL,
    
 )

INSERT INTO student

VALUES (1, 'Jolly', 'Female', '12-JUN-1989', 500), 
(2, 'Jon', 'Male', '02-FEB-1974', 545), 
(3, 'Sara', 'Female', '07-MAR-1988', 600), 
(4, 'Laura', 'Female', '22-DEC-1981', 400), 
(5, 'Alan', 'Male', '29-JUL-1993', 500), 
(6, 'Kate', 'Female', '03-JAN-1985', 500), 
(7, 'Joseph', 'Male', '09-APR-1982', 643), 
(8, 'Mice', 'Male', '16-AUG-1974', 543), 
(9, 'Wise', 'Male', '11-NOV-1987', 499), 
(10, 'Elis', 'Female', '28-OCT-1990', 400);

Now, let’s create a very simple common table expression. This CTE will contain records of all students who were born before 1^st January 1985. Take a look at the following script.

USE schooldb;

WITH OldStudents AS
(
SELECT * FROM student
WHERE DOB < '1985-01-01'
)

To create a CTE, you have to start with ‘WITH’ keyword followed by the name of the CTE and ‘AS’ keyword.

Next, within the parenthesis, you have to write the query which returns the records that the CTE will temporarily store. In the above script, we created a CTE named “OldStudents”.

However, note that if you try to execute the above query, you will get an error. This is because once you create a CTE, you have to immediately use it.

Let’s select all the records from our newly created “OldStudents” CTE. Try the following script on your server.

USE schooldb;

WITH OldStudents AS
(
	SELECT * FROM student
	WHERE DOB < '1985-01-01'

)

SELECT * FROM OldStudents

The above script will retrieve the following set of records:

Calculating Aggregate via CTE

Like tables, you can perform aggregate functions on CTE. Let’s take a look at another example of CTE.

USE schooldb;

WITH SumofScores AS
(
	SELECT 
		gender, SUM(total_score) as SumScore
	FROM student
	GROUP BY gender
)

SELECT AVG (SumScore)
FROM SumofScores

In the above example, we created a CTE named SumofScores. This CTE contains the sum of the values stored in the total_score column of the student table. The result is grouped by the gender column. The data stored by the CTE looks like this in the memory:

Next, we performed AVG function on the “SumScore” column of the CTE. The final result of the script will be the average of 2400 and 2730 i.e. 2565.

This is a bit more complicated than the previous example but demonstrates the concept of CTE more clearly.

Labeling Columns in CTE

In the previous example, we added an alias to the second column of the CTE. We renamed it as “SumScore”. This is one way of labeling columns in CTE and is similar to table column aliases.

However, there is another way of defining column names in CTE. Take a look at the following query.

USE schooldb;

WITH SumofScores(Gender, SumScore) AS
(
	SELECT 
		gender, SUM(total_score)
	FROM student
	GROUP BY gender
)

SELECT AVG (SumScore)
From SumofScores

In this script, we added the column names of the “SumofScores” CTE in the parenthesis after the CTE name. Each column name is separated by a comma.

If you look at the SELECT statement after the CTE, you can see that we are then referencing “SumScore” column that we created in the parenthesis after the CTE name.

Creating Multiple CTEs

All the examples so far have only used a single common table expression for clarity. You can create a list of CTEs at the same time and then use all of them in combination in the final result set.

This is best explained with the help of an example. Take a look at the following script below.

Here we will create two CTEs. The first CTE will store all the records of the students born before 1^st January 1985. The second CTE will contain all the records of the students born on or after 1^st January 1985.

After that, we will use select statements to retrieve all the records from both CTEs. The retrieved records will be merged together using UNION statement. Finally, the merged record will be sorted in the ascending order of date of birth.

USE schooldb;

WITH OldStudents AS
(
	SELECT * FROM student
	WHERE DOB < '1985-01-01'

),

YoungStudents AS
(
	SELECT * FROM student
	WHERE DOB >= '1985-01-01'

)

(SELECT * FROM OldStudents
UNION
SELECT * FROM YoungStudents)

ORDER BY DOB

In the above SQL query, we created two CTEs: “OldStudents” and “YoungStudents”. It is worth mentioning that you do not need to use the “WITH” keyword with every CTE. You are only required to use it before the first CTE in the script, after that you can create any number of CTEs by separating them with a comma.

The above script retrieves following results: