T-SQL 101: 122 Simplifying Queries with Common Table Expressions (CTEs) in SQL Server T-SQL

One of the challenges with subqueries is they can get very, very messy if you need to use them more than once. So for example, in the first query above, I’ve created a derived table called BulkProducts. But what if I needed to derive that same table again? Then I would end up with yet another copy of all that code. (And obviously it might be far more complex)

A more common problem is that in a SELECT statement, I might be selecting subqueries but every single time I might be needing to calculate a sub-query to get the values.

Common Table Expressions (CTEs)

Now, common table expressions, or CTEs, are an option for tidying up queries. So for example, instead of having bulk products as a derived table in the middle of the query, you can see in the bottom query above, that I’ve moved the code that calculates the list of bulk products, into a separate area at the top.

Note how much easier it is to then focus on the main query, without getting tied down in the details of the subquery. That’s even more important if it’s normally repeated possibly many times.

By defining BulkProducts as a CTE, I can just write the main query, treating BulkProducts as though it is a table that I wish I had.

Scope

One question that often arises, is about whether you can then use the same CTE in a subsequent query. If you need that, you need to define it again, or to use other techniques like temporary tables. CTEs are scoped to the query that they are defined in.

Materialized?

Another question that comes up, is whether SQL Server is materializing the data for the result of the CTE under the covers i.e., does it first calculate all that data and store it temporarily under the covers. It doesn’t do that. In the form shown above, the CTE is what many people would call syntactic sugar. It’s just a different way to write the same query.

More importantly, if I then further filtered the BulkProducts data later in the main query, SQL Server will happily apply the same logic as if you had filtered it in the CTE.

That’s not how some other database engines work. In PostgreSQL, a CTE is used to pre-calculate the data into an internal temporary structure. Many people use that as an optimization technique.

I’ve seen discussions about PostgreSQL adding an option to be able to work the same way that SQL Server does. And it wouldn’t surprise me if the SQL Server team ever provided an option to work the same way that PostgreSQL does by default.

Statement Separator

When you use the WITH clause to define a CTE, you must have used a statement separator (i.e., a semi-colon) between the previous SQL statement and the WITH.

I always use statement separators so this isn’t an issue for me, but it’s important to be aware of. There are a handful of statements in T-SQL that must be separated from previous statements. This is one of them.

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

2025-02-28