What Is a CTE in SQL Server?

What is a CTE, and how do you write a CTE in SQL Server? Join us on a journey where we’ll see all the typical usage of a CTE in SQL Server.

CTEs (or Common Table Expressions) are an SQL feature used for defining a temporary named result. You can think of it as a temporary table whose output is available only when the main query is run. This is practical because the CTEs result isn’t stored anywhere but can always be referenced inside the query like any other table. CTEs are most commonly used in the SELECT statement, but they can also be used in the INSERT, UPDATE, and DELETE statements.

CTEs are a relatively new SQL feature. They were introduced in the SQL: 1999 standard (SQL 3). In 2005, they were made available in SQL Server 2005.

You can get hands-on experience with CTEs in SQL Server in our interactive Recursive Queries in MS SQL Server course. You’ll learn CTE in SQL Server syntax, how to use more than one CTE, how to nest them, and how to make them work in SELECT, INSERT, UPDATE, and DELETE. There’s also a section that explains recursion and how to write a recursive CTE.

We’ll cover all these topics in this article. However, we can’t compete here with the 112 interactive exercises the course offers. Apart from the course and this article, there are also some other ways to learn CTEs.

SQL Server CTE Syntax

The basic SQL Server CTE syntax is:

WITH cte AS (  
  SELECT
    ...
)

SELECT
  ...
FROM cte;

A CTE in SQL Server is initiated using the keyword WITH. Then we follow it with the CTE name (here, cte), the AS keyword, and the parentheses. The parentheses hold the CTE definition. In simple terms, it’s a regular SELECT statement, only in the form of a CTE.

The next part comes after the parentheses and is called the main query. The CTE doesn’t work without it. This main query is, in the generic example, a SELECT statement that references the CTE in the FROM clause. As we already mentioned, the main query could be an INSERT, UPDATE, or DELETE statement instead of SELECT.

Another way of writing a CTE in SQL Server is by explicitly specifying the columns, which looks like this:

WITH cte (cte_columns) AS (
  SELECT
    ...	
)

SELECT
  ...
FROM cte;

The only difference is that you explicitly define the CTE columns before the AS keyword. This is useful when the CTE columns require aliases (e.g. when they contain functions); the query is more readable with the aliases assigned in the above way.

As you’ll see with the examples in this article, the main argument for using CTE in SQL Server is improved code readability. You can also take a look at some of its other advantages.

Now that you know the basic CTE syntax, let’s use each approach in an example. As we go further, we’ll show slight changes in the syntax depending on query usage.

Examples of CTE in SQL Server

Before writing any code, let’s familiarize ourselves with the dataset. The table is flight_database, which contains historical flight data. It has the following columns:

• id – The record’s ID and the table’s primary key (PK).
• flight_id – The flight number according to IATA standards.
• airline – The airline name.
• flight_date – The date of the flight.
• departure_airport – The airport from which the flight took off.
• arrival_airport – The airport where the flight landed.
• planned_departure – The time when the flight was due to depart.
• actual_departure – The time of the flight’s actual departure.
• planned_arrival – The time when the flight was due to arrive.
• actual_arrival – The time of the flight’s actual arrival.
• airport_distance – The distance between the departure and arrival airports, in kilometers.

This is fictional data for Amsterdam Schiphol Airport. All the times are GMT+1, which makes it easier for us to compare the departure and arrival times.

Here are a few rows of this table:

The id column is unique, as it’s the table primary key. The column flight_id is not unique, as there is data for the same flights on different dates.

You can create this dataset by using the code in the link. If you need to install SQL Server, here are the instructions on how to do it. 

Example 1: Standard CTE in SQL Server

The task here is to write a CTE and find the longest departure and arrival delays by IATA flight number.

Here’s the query:

WITH delay_times AS (
  SELECT 
    flight_id,
    flight_date,
    DATEDIFF(minute, planned_departure, actual_departure) AS departure_delay,
    DATEDIFF(minute, planned_arrival, actual_arrival) AS arrival_delay
  FROM flight_database
)

SELECT 
  flight_id,
  MAX(departure_delay) AS max_departure_delay,
  MAX(arrival_delay) AS max_arrival_delay
FROM delay_times
GROUP BY flight_id;

The CTE starts by writing the keyword WITH. The CTE name is delay_times. After the AS keyword and the opening parenthesis, there is a CTE definition in the form of a SELECT statement. It calculates the difference between the planned and actual departure using the DATEDIFF() function. The same approach is applied when calculating the difference between the planned and actual arrival. Both results are in minutes. Since these columns use functions, they each have an alias.

After closing the parenthesis, it’s time to write the main query. It’s a SELECT statement that references the delay_times CTE and uses SQL Server’s MAX() aggregate functions twice to calculate the longest departure and arrival delay by flight.

The output reads the following way. The DL 47 flight’s maximum delay on departure was 5 minutes. Its maximum arrival delay was 0; it always arrived on time.

Example 2: CTE with Explicitly Defined Columns

The following example is very similar. The only difference is that we want to find the smallest departure and arrival delays by flight. Also, we’ll use explicitly defined columns. Do it like this:

WITH delay_times (flight_id, flight_date, departure_delay, arrival_delay) AS (
  SELECT 
    flight_id,
    flight_date,
    DATEDIFF(minute, planned_departure, actual_departure),
    DATEDIFF(minute, planned_arrival, actual_arrival)
  FROM flight_database
)

SELECT 
  flight_id,
  MIN(departure_delay) AS min_departure_delay,
  MIN(arrival_delay) AS min_arrival_delay
FROM delay_times
GROUP BY flight_id;

This CTE is again named delay_times. To define the CTE columns explicitly, write them in the parentheses before the AS keyword.

The CTE itself is not much different from the previous one: it again uses the DATEDIFF() function to calculate the delay differences. The only change is that the aliases for these two (and the other two) columns are defined earlier, with the name of the CTE.

The main query is almost the same as before. The difference is it now uses the MIN() function since the goal is to calculate the smallest delays.

The result shows the DL 47’s flight shortest delay was zero. In other words, it was on time at least once. The LH 2301 flight was never on time. It was delayed at least 20 minutes both on departure and arrival.

Example 3: A Nested CTE in SQL Server

In SQL Server, a nested CTE occurs when there are at least two CTEs and the second CTE references the first. We’ll need this in the following example. The task is to calculate the average flight duration in minutes and the average flight speed in km/h.

Here’s the code:

WITH flight_duration AS (
  SELECT 
    flight_id,
    DATEDIFF(MINUTE, actual_departure, actual_arrival) AS minutes_of_flight,
    airport_distance
  FROM flight_database
),

average_flight_duration AS (
  SELECT 
    flight_id,
    AVG(minutes_of_flight) AS average_flight_duration,
    airport_distance
  FROM flight_duration
  GROUP BY flight_id, airport_distance
)

SELECT 
  flight_id,
  average_flight_duration,
  airport_distance/(CAST(average_flight_duration AS DECIMAL(10,2))/60) AS average_flight_speed
FROM average_flight_duration;

The first CTE is written as usual in SQL Server: WITH, the CTE name, AS, and the SELECT statement. This query calculates the flight duration in minutes. After closing the parentheses, you write the second CTE. But be careful; there has to be a comma separating the two CTEs. Also, when you start writing the second CTE, there’s no WITH keyword: you directly start with the CTE name. Everything else is as usual. This second CTE references the first CTE in the FROM clause to calculate the average flight duration by flight for all dates.

There are no differences in the main query’s syntax. This query references the second CTE. It calculates the average flight speed by dividing the distance between the airports by the average flight duration. The result is converted into a decimal number. It’s also divided by 60, so the average speed will be shown in kilometers per hour.

The output shows that, for example, the flight LH 987’s average time to reach the destination is 54 minutes, with the average speed of 407.14 km/h.

If you want more practice, here’s another example of a nested CTE.

Example 4: A CTE in an UPDATE Statement

In this example, we’ll show you how CTEs work in the UPDATE statement. The way it's shown below, you could also use the INSERT statement.

The task is to update the flight_database. To be more precise, we’ll update its column airport_distance. It currently contains data in kilometers, but it should be changed to miles.

Here’s how to do it:

WITH distance_in_miles AS (
  SELECT 
    flight_id,
    airport_distance * 0.621371 AS airport_distance_miles
  FROM flight_database
)

UPDATE flight_database
SET airport_distance = airport_distance_miles
FROM distance_in_miles dim 
JOIN flight_database fd ON dim.flight_id = fd.flight_id;

As always, start with the keyword WITH. The CTE distance_in_miles is used to convert kilometers into miles. It’s simple; multiply the values by 0.621371.

The main query is now UPDATE instead of SELECT. Nothing difficult; just follow the statement syntax. Update the column airport_distance with the values from the column airport_distance_miles that appears in the CTE. Join the table and the CTE, and that’s it; the table is updated.

Here are several values before the update:

And here are the same rows with the updated distance values:

Example 5: A Recursive CTE in SQL Server

Our final example is writing a recursive CTE in SQL Server. This is a CTE that references itself. It’s most often used when querying hierarchical data (such as company organization) or graphs where some or all parts are related (think of a road map with the distances between the cities). The example we’ll show you is a little easier than that. The main point is that you understand recursion and how to translate it into a CTE.

Say the airport has a certain number of flight slots. There’s also a price per slot. Each year, the airport will increase the number of slots by 150; the slot price will stay the same. We want to show the number of slots, the price per slot, and the total slot revenue for the current and next four years.

Here’s the recursive CTE to do that:

WITH airport_slots AS (
  SELECT 
    1 AS id,
    400000 AS number_of_slots,
    20574421.00 AS price_per_slot,
    CAST(20574421.00 * 400000 AS DECIMAL) AS slot_revenue
		   
  UNION ALL

  SELECT 
    id + 1,
    number_of_slots + 150,
    price_per_slot,
    CAST(price_per_slot * (number_of_slots + 150) AS DECIMAL)
  FROM airport_slots
  WHERE id <= 4
)

SELECT *
FROM airport_slots;

Once again, the recursive query starts with the keyword WITH. The first SELECT in the CTE is called the anchor member. We set the starting values of the ID, slots number, and price per slot. Also, we multiply these two values to get the slot revenue.

Then comes UNION ALL, which connects the anchor member with the second SELECT (which is called the recursive member). UNION ALL dictates that the number of columns and their data types have to be the same in both SELECT statements in the CTE.

The recursive member references the CTE itself. With every recursion, the ID will be incremented by one and the number of slots by 150. The price per slot stays the same. The slot revenue is the increased number of slots multiplied by the price per slot.

We also used the WHERE clause to stop the recursion once the ID equals four. The recursion will turn it into five, and a five year projection is what we want (current + next four years).

After that, it’s the same as with any CTE. There’s SELECT as the main query.

And this is the output:

We have more recursive CTE examples on our blog. We stuck to the SQL Server syntax in this article, but you can also learn how to do recursion in PostgreSQL and Oracle.

SQL Server CTEs Make You Better!

Literally, they do. Most of the things CTEs do in SQL Server can be done with a subquery. But imagine what the above codes would look like – not pretty! One of the typical usages of CTEs in SQL Server is to help you organize long queries. CTEs make queries more readable by naming parts of the query. That way, you can easily break down each part of a complex calculation and make the calculation logical.

Knowing this will make you and your T-SQL code better. There’s also one SQL feature that’s not possible without CTEs: recursive queries. They are indispensable when querying hierarchical and graph data. You can learn all the details in our Recursive Queries in MS SQL Server course.

And if you’re interviewing for an SQL position, make sure that you go through our five CTE examples before the interview!