Guide for CTE in SQL Server

The Common Table Expression aka CTE in SQL Server provides a temporary result set in T-SQL. You can refer to it within a SQL Select, SQL Insert, SQL Delete, or SQL Update statement.

The option is available from SQL Server 2005 onwards, helping the developers write complex and long queries involving many JOINs, aggregation, and data filtering. Usually, developers use subqueries for writing T-SQL codes, and SQL Server stores these CTE in memory temporally until the query execution finishes. Once the query is finished, it is removed from memory.

CTE in SQL Server: Syntax

WITH <common_table_expression> ([column names])
AS
(
   <query_definition>
)
<operation>

• It uses a CTE name to refer to it for performing the Select, Insert, Update, Delete or Merge statements.
• Column names are comma-separated. They should match the columns defined in the query definition.
• The query definition involves the select statements from a single table or joins between multiple tables.
• You can refer to the CTE expression name for retrieving the results.

For example, the following basic CTE query uses the following parts:

• Common Table Expression name – SalesCustomerData
• Columns list – [CustomerID],[FirstName],[LastName],[CompanyName],[EmailAddress],[Phone]
• The query definition includes a select statement that gets data from the [SalesLT].[Customer] table
• The last part uses the select statement on the CTE expression and filters records using the where clause.

WITH SalesCustomerdata ([CustomerID],[FirstName],[LastName],[CompanyName],[EmailAddress],[Phone])
AS(
SELECT [CustomerID]
      ,[FirstName]
      ,[LastName]
      ,[CompanyName]
      ,[EmailAddress]
      ,[Phone]
   FROM [SalesLT].[Customer] 
)
SELECT * FROM SalesCustomerdata where Firstname like 'Raj%' 
ORDER BY CustomerID desc

In another example, we calculate the average total sales from the CTE. The query definition includes the GROUP BY clause. Later, we use the AVG() function for calculating the average value.

WITH Salesdata ([SalesOrderID],[Total])
AS(
SELECT [SalesOrderID]
         ,count(*) AS total
          FROM [SalesLT].[SalesOrderHeader]
        GROUP BY [SalesOrderID]
)
SELECT avg(total) FROM salesdata

You can also use CTE to insert data into the SQL table. The CTE query definition includes the required data that you can fetch from existing tables using joins. Later, query CTE for inserting data into the target table.

Here we use the SELECT INTO statement to create a new table named [CTETest] from the output of the CTE select statement.

WITH CTEDataInsert
AS 
(
SELECT
    p.[ProductID]
    ,p.[Name]
    ,pm.[Name] AS [ProductModel]
    ,pmx.[Culture]
    ,pd.[Description]
FROM [SalesLT].[Product] p
    INNER JOIN [SalesLT].[ProductModel] pm
    ON p.[ProductModelID] = pm.[ProductModelID]
    INNER JOIN [SalesLT].[ProductModelProductDescription] pmx
    ON pm.[ProductModelID] = pmx.[ProductModelID]
    INNER JOIN [SalesLT].[ProductDescription] pd
    ON pmx.[ProductDescriptionID] = pd.[ProductDescriptionID]
)
SELECT * INTO CTETest FROM CTEDataInsert
GO

You can also specify an existing table that matches the columns with the inserted data.

WITH CTEDataInsert
AS 
(
SELECT
    p.[ProductID]
    ,p.[Name]
    ,pm.[Name] AS [ProductModel]
    ,pmx.[Culture]
    ,pd.[Description]
FROM [SalesLT].[Product] p
    INNER JOIN [SalesLT].[ProductModel] pm
    ON p.[ProductModelID] = pm.[ProductModelID]
    INNER JOIN [SalesLT].[ProductModelProductDescription] pmx
    ON pm.[ProductModelID] = pmx.[ProductModelID]
    INNER JOIN [SalesLT].[ProductDescription] pd
    ON pmx.[ProductDescriptionID] = pd.[ProductDescriptionID]
)
INSERT into CTETest select * FROM CTEDataInsert
GO

You can update or delete records in the SQL table using the common table expression as well. The following queries use DELETE and UPDATE statements with CTE.

Update Statement in CTE

WITH Salesdata ([SalesOrderID],[Freight])
AS(
SELECT [SalesOrderID]
         ,[Freight]
          FROM [SalesLT].[SalesOrderHeader]
        )
UPDATE SalesData SET [Freight]=100.00 WHERE [SalesOrderID]=71774
Go

Delete Statement in CTE

WITH Salesdata ([SalesOrderID],[Freight])
AS(
SELECT [SalesOrderID]
         ,[Freight]
          FROM [SalesLT].[SalesOrderHeader]
        )
delete SalesData  WHERE [SalesOrderID]=71774
GO
SELECT * FROM [SalesLT].[SalesOrderHeader] WHERE SalesOrderID=71774

Multiple CTEs

You can declare multiple CTEs in the T-SQL script and use the join operations on them. To the multiple CTE, T-SQL uses a comma as a separator.

In the following query, we have two CTEs:

1. CTESales
2. CTESalesDescription

Later, in the select statement, we retrieve results using INNER JOIN on both CTEs.

WITH CTESales
AS 
(
SELECT
     p.[ProductID]
    ,p.[Name]
    ,pm.[Name] AS [ProductModel]
    ,pmx.[Culture]
    ,pmx.[ProductDescriptionID]
   FROM [SalesLT].[Product] p
    INNER JOIN [SalesLT].[ProductModel] pm
    ON p.[ProductModelID] = pm.[ProductModelID]
    INNER JOIN [SalesLT].[ProductModelProductDescription] pmx
    ON pm.[ProductModelID] = pmx.[ProductModelID]
    INNER JOIN [SalesLT].[ProductDescription] pd
    ON pmx.[ProductDescriptionID] = pd.[ProductDescriptionID]
),CTESalesDescription

AS (

SELECT  description AS describe,[ProductDescriptionID]
from [SalesLT].[ProductDescription]  
)

SELECT  productid, [Name],[ProductModel],describe
FROM CTESales 
INNER JOIN CTESalesDescription 
    ON 
CTESales.[ProductDescriptionID] = CTESalesDescription.[ProductDescriptionID]

Recursive Common Table Expressions

The Recursive CTE runs in a repeated procedural loop until the condition satisfies. The following T-SQL example uses an ID counter and selects records until the WHERE condition is satisfied.

Declare @ID int =1;
;with RecursiveCTE as  
   (  
      SELECT @ID as ID
        UNION ALL  
      SELECT  ID+ 1
  FROM  RecursiveCTE  
  WHERE ID <5
    )  
 
SELECT * FROM RecursiveCTE

Another use of recursive CTE in SQL Server is to display hierarchical data. Assume that we have an employee table, and it has records for all employees, their departments, and their managers’ IDs.

--Script Reference: Microsoft Docs

CREATE TABLE dbo.MyEmployees  
(  
EmployeeID SMALLINT NOT NULL,  
FirstName NVARCHAR(30)  NOT NULL,  
LastName  NVARCHAR(40) NOT NULL,  
Title NVARCHAR(50) NOT NULL,  
DeptID SMALLINT NOT NULL,  
ManagerID INT NULL,  
 CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC)   
);  
INSERT INTO dbo.MyEmployees VALUES   
 (1, N'Ken', N'Sánchez', N'Chief Executive Officer',16,NULL)  
,(273, N'Brian', N'Welcker', N'Vice President of Sales',3,1)  
,(274, N'Stephen', N'Jiang', N'North American Sales Manager',3,273)  
,(275, N'Michael', N'Blythe', N'Sales Representative',3,274)  
,(276, N'Linda', N'Mitchell', N'Sales Representative',3,274)  
,(285, N'Syed', N'Abbas', N'Pacific Sales Manager',3,273)  
,(286, N'Lynn', N'Tsoflias', N'Sales Representative',3,285)  
,(16,  N'David',N'Bradley', N'Marketing Manager', 4, 273)  
,(23,  N'Mary', N'Gibson', N'Marketing Specialist', 4, 16);

Now, we need to generate the employee hierarchy data. We can use recursive CTE with UNION ALL in the select statement.

WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort)  
AS (SELECT CONVERT(VARCHAR(255), e.FirstName + ' ' + e.LastName),  
        e.Title,  
        e.EmployeeID,  
        1,  
        CONVERT(VARCHAR(255), e.FirstName + ' ' + e.LastName)  
    FROM dbo.MyEmployees AS e  
    WHERE e.ManagerID IS NULL  
    UNION ALL  
    SELECT CONVERT(VARCHAR(255), REPLICATE ('|    ' , EmployeeLevel) +  
        e.FirstName + ' ' + e.LastName),  
        e.Title,  
        e.EmployeeID,  
        EmployeeLevel + 1,  
        CONVERT (VARCHAR(255), RTRIM(Sort) + '|    ' + FirstName + ' ' +   
                 LastName)  
    FROM dbo.MyEmployees AS e  
    JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID  
    )  
SELECT EmployeeID, Name, Title, EmployeeLevel  
FROM DirectReports   
ORDER BY Sort;

The CTE returns the employee level details as shown below.

Important Points Regarding Common Table Expressions

• We cannot reuse the CTE. Its scope is limited to the outer SELECT, INSERT, UPDATE, or MERGE statements.
• You can use multiple CTES; however, they should use UNION ALL, UNION, INTERSECT, or EXCERPT operators.
• We can define multiple CTE query definitions in the non-recursive CTE.
• We cannot use ORDER BY (without TOP), INTO, OPTIONS clause with query hints, and FOR BROWSE in the CTE query definition.

For example, the below script uses the ORDER BY clause without a TOP clause.

WITH CTEDataInsert
AS 
(
SELECT
    p.[ProductID]
    ,p.[Name]
    ,pm.[Name] AS [ProductModel]
    ,pmx.[Culture]
    ,pd.[Description]
FROM [SalesLT].[Product] p
    INNER JOIN [SalesLT].[ProductModel] pm
    ON p.[ProductModelID] = pm.[ProductModelID]
    INNER JOIN [SalesLT].[ProductModelProductDescription] pmx
    ON pm.[ProductModelID] = pmx.[ProductModelID]
    INNER JOIN [SalesLT].[ProductDescription] pd
    ON pmx.[ProductDescriptionID] = pd.[ProductDescriptionID]
    ORDER BY productid
)
select * FROM CTEDataInsert 
GO

It gives the following error:

• We cannot create an index on the CTE.
• The defined column names in CTE should match the columns returned in the select statement.

The CTE does not have the [Phone] column in the below code while the select statement returns its value. Therefore, you get the highlighted error message.

• If you have multiple statements in the T-SQL script, the previous statement before CTE should terminate using a semicolon.

For example, the first select statement does not include a semicolon. Therefore, you get an incorrect syntax error in the CTE script.

The script works fine if we terminate the first select statement using the semicolon operator.

• We cannot use a duplicate column in the select statement if we do not declare the column name externally.

For example, the following CTE definition specifies the duplicate column [Phone]. It returns an error.

However, if you define external columns, it won’t cause errors. It is required when you need a single column multiple times in the output for different calculations.

Important: Common Table Expressions (CTE) are not a replacement for the temp tables or table variables.

• Temp tables get created in the TempDB, and we can define index constraints similar to a regular table. We cannot reference the temp table multiple times in a session
• Table variables also exist in the TempDB and act like variables that exist during the batch execution. We cannot define an index on the table variables.
• CTE is for a single reference purpose, and we cannot define the index on it. It exists in memory and is dropped upon after reference is made.

Conclusion

The Common Table Expressions (CTE) allow the developers to write clean and effective code. In general, you can use CTE where you do not require multiple references like a temporary table, and we explored various scenarios for SELECT, INSERT, UPDATE, DETELTE statement, and recursive CTEs.

With the help of modern tools, such as SQL Complete SSMS Add-in, handling CTEs becomes even easier. The add-in can suggest CTEs on the fly, thus making the tasks involving CTE much more straightforward. Also, refer to Microsoft documentation for more details about the CTE.

Tags: sql cte, t-sql statements Last modified: September 16, 2021