SQL Server, all about the Common Table Expressions (CTEs)

Hi friends!

Today we talk about Common Table Expression which is much more often referred to with the acronym CTE.

What are they?

When someone ask me this question I answer using the simplest way I can define them:

A CTE is a temporary Result set to which we associate a name and to which we can refer in the various statements.

 

About the CTE

 

CTEs were introduced few years ago with the release of the 2005 version of SQL Server.

The goal was to provide an extended syntax that would allow you to write queries more easily.

The basic syntax for using CTEs is really simple: it is sufficient to precede our INSERT, UPDATE or DELETE statement with the keyword WITH followed by the name we want to give to the Query, followed in turn by the keyword AS.

A Simple example

 
WITH MAXCODICE AS
       (SELECT MAX (CODE) AS CODE FROM LIST)
SELECT * FROM MAXCODICE

 

Very Very important: CTEs bring the ability to write recursive queries.

 

We will therefore see the two cases of non-recursive and recursive CTE.

 

An example of a non-recursive CTE could be this below where I want to extract all the codes from my LIST table excluding the code that has maximum value.

 

I could write the Query like this:

 
WITH MAXCODICE AS (SELECT MAX (CODE) AS CODE FROM LIST)
   SELECT * FROM LIST E
WHERE NOT EXISTS (SELECT CODE FROM MAXCODICE T WHERE T.CODICE = E.CODICE)

 

As you can see the CTE is in effect a table for which I can go in JOIN.

 

Now let's see how to use a recursive CTE:

For this example first we create a CTE named RIGHE.

Then we insert two SELECT statements joined by a UNION ALL operator

The first SELECT is called ANCHOR.

The second SELECT triggers the recursion thanks to the fact that it is possible to specify the name of the CTE itself (in this case RIGHE)

WITH RIGHE AS
(
SELECT 1 AS NUMBER
UNION ALL
SELECT NUMBER + 1 FROM RIGHE WHERE NUMBER < 10)   
SELECT NUMBER FROM RIGHE  
       
 

 

Let's run it to see that the numbers between 1 and 10 are returned.

This method is in fact useful for example to generate a table with a predetermined number of rows.

 

Take care that ...

by default an error will be thrown if the number of recursions exceeds the value of 100.

It is possible to increase this value using the OPTION command (MAXRECURSION N)

Where N can be worth a maximum of 32768.

Instead, setting N = 0 we will obtain an infinite recursion.

 

 

That's all for today guys!

Luca Biondi @ SQLServerPerformance blog 2021!

Previus post:SQL Server, use transactions in a smarter way. The save transaction