Mastering Common Table Expression or CTE in SQL Server

WITH common_table_expression (Transact-SQL)

Summary

From this post you will learn about Common Table Expression or  CTE in SQL Server using the with Clause. 

About CTE Statement: Specifies a temporary named result set, known as a common table expression (CTE). This is derived from a simple query and defined within the execution scope of a single SELECTINSERTUPDATEDELETE or MERGE statement. A common table expression can include references to itself. This is referred to as a recursive common table expression.

Syntax:
[ WITH <common_table_expression> [ ,...n ] ]  
<common_table_expression>::=  
    expression_name [ ( column_name [ ,...n ] ) ]  
    AS  
    ( CTE_query_definition )  

Arguments

expression_name Is a valid identifier for the common table expression. expression_name must be different from the name of any other common table expression defined in the same WITH <common_table_expression> clause, but expression_name can be the same as the name of a base table or view. Any reference to expression_name in the query uses the common table expression and not the base object.

column_name Specifies a column name in the common table expression. Duplicate names within a single CTE definition are not allowed. The number of column names specified must match the number of columns in the result set of the CTE_query_definition. The list of column names is optional only if distinct names for all resulting columns are supplied in the query definition.

CTE_query_definition Specifies a SELECT statement whose result set populates the common table expression. The SELECT statement for CTE_query_definition must meet the same requirements as for creating a view, except a CTE cannot define another CTE.

There are 2 types of creating and using the Common Tables Expressions.

  1. Using Non-Recursive Common Table Expressions.
  2. Using Recursive Common Table Expressions.

About Non-RecursiveCTE:

Non-Recursive CTEs are simple where the CTE doesn’t use any recursion, or repeated processing in of a sub-routine. We will create a simple Non-Recursive CTE to display the row number from 1 to 10.

Guidelines for Creating and Using Non-Recursive Common Table Expressions:

  1. A CTE must be followed by a single SELECTINSERTUPDATE, or DELETE statement that references some or all the CTE columns. A CTE can also be specified in a CREATE VIEW statement as part of the defining SELECT statement of the view.
  2. Multiple CTE query definitions can be defined in a nonrecursive CTE. The definitions must be combined by one of these set operators: UNION ALLUNIONINTERSECT, or EXCEPT.
  3. A CTE can reference itself and previously defined CTEs in the same WITH clause. Forward referencing is not allowed.
  4. Specifying more than one WITH clause in a CTE is not allowed. For example, if a CTE_query_definition contains a subquery, that subquery cannot contain a nested WITH clause that defines another CTE.
  5. The following clauses cannot be used in the CTE_query_definition:
  • ORDER BY (except when a TOP clause is specified)
  • INTO
  • OPTION clause with query hints
  • FOR BROWSE
  1. When a CTE is used in a statement that is part of a batch, the statement before it must be followed by a semicolon.
  2. A query referencing a CTE can be used to define a cursor.
  3. Tables on remote servers can be referenced in the CTE.
  4. When executing a CTE, any hints that reference a CTE may conflict with other hints that are discovered when the CTE accesses its underlying tables, in the same manner as hints that reference views in queries. When this occurs, the query returns an error.

Example: Here we have been using only one column as ROWNO. Next is the Query part, here we write our select query to be execute for our CTE. After creating our CTE query to run the CTE use the select statement with CTE Expression name.

;with ROWCTE(ROWNO) as  
   (SELECT ROW_NUMBER() OVER(ORDER BY name ASC) AS ROWNO
FROM sys.databases WHERE database_id <= 10)  
SELECT * FROM ROWCTE

Output:

CTE using Union ALL

Let’s consider there is a below two scenarios to display the result.

  1. The first scenario is to display each Item Price of current Year.
  2. The second scenario is to increment 10% to each Item Price for next year.

For this we use the above CTE Query. In this query, we add the UNION ALL and in UNION ALL Query we do calculation to add 10% to each item Price and show in next row with adding one year.

;WITH itemCTE (Item_ID, Item_Name, Item_Price,MarketRate,SalesYear)
AS
(
SELECT Item_ID, Item_Name, Item_Price ,'Present Price' as MarketRate, YEAR(Date) as SalesYear FROM ItemDetails  
	UNION ALL
SELECT Item_ID as Item_ID, Item_Name, (Item_Price + (Item_Price *10 )/100) as Item_Price, 'Future Price' as MarketRate,  YEAR(dateadd(YEAR, 1, Date))  as SalesYear FROM ItemDetails
    
) 
SELECT * from itemCTE Order by Item_Name,SalesYear

Output:

Common Table Expressions (CTE) for Insert

Now we will see how to insert the CTE result to another table. For this let’s consider our above Item Table. We insert the Item details result of above CTE query to Item History table. For this first we create an Item History table.

Create Item History Table: In this history table, we add the same columns as item table along with MarketRate column as present or future Item price. Here is the query to create an ItemHistory table.

CREATE TABLE ItemHistory    
( ID int identity(1,1),
oldITEMID    int, 
Item_Name VARCHAR(100) NOT NULL,    
Item_Price int NOT NULL,   
MarketRate  VARCHAR(100) NOT NULL,    
Date VARCHAR(100) NOT NULL )

CTE Insert Example:

Here we use above same CTE query Insert the result in to the Item History table. From this query we insert both item details of present year Item price along with the next year Item prices added as 10% more.

;WITH itemCTE (Item_ID, Item_Name, Item_Price,MarketRate,Date)
AS
(SELECT Item_ID, Item_Name, Item_Price ,'Present Price' as MarketRate,Date  
    FROM ItemDetails  
	UNION ALL
SELECT Item_ID as Item_ID, Item_Name,(Item_Price + (Item_Price *10 )/100) as Item_Price,
	 'Future Price' as MarketRate,  dateadd(YEAR, 1, Date) as Date
    FROM ItemDetails)
-- Define the outer query referencing the CTE name.
Insert into ItemHistory(oldITEMID ,Item_Name,Item_Price,MarketRate,Date)  
SELECT Item_ID, Item_Name, Item_Price,MarketRate,year(Date) from itemCTE Order by Item_Name,Date

Output:

Select Query: To view the item history result we select and display all the details.

Example: select * from ItemHistory

Output:

Create View with CTE Example:

Now we see how to use the above CTE query can be used in a view. Here we create a view and we add the CTE result inside the view. When we select the view as a result, we can see the CTE output will be displayed.

Example:

CREATE VIEW CTEVIEW
AS
WITH    itemCTE1 AS
(SELECT Item_ID, Item_Name, Item_Price ,'Present Price' as MarketRate,Date as IDate  FROM ItemDetails  
	UNION ALL
SELECT Item_ID as Item_ID, Item_Name,(Item_Price + (Item_Price *10 )/100) as Item_Price,	 'Future Price' as MarketRate,  dateadd(YEAR, 1, Date) as IDate  FROM ItemDetails)
SELECT Item_ID, Item_Name, Item_Price,MarketRate,year(IDate) as IDate from itemCTE1 
GO
 -- T-SQL test view
SELECT * FROM CTEVIEW Order by Item_Name,IDate
GO

Output:

Tips write a clean CTE Query:

Here are some basic guidelines that need to be followed to write a good CTE Query.

  • A CTE must be followed by a single SELECTINSERTUPDATE, or DELETE statement that references some or all the CTE columns.
  • Multiple CTE query definitions can be defined in a non recursive CTE.
  • A CTE can reference itself and previously defined CTEs in the same WITH clause
  • We can use only one With Clause in a CTE
  • ORDER BYINTOCOMPUTE or COMPUTE BYOPTIONFOR XML cannot be used in non-recursive CTE query definition
  • SELECT DISTINCTGROUP BYHAVINGScalar aggregationTOPLEFTRIGHTOUTER JOIN (INNER JOIN is allowed) subqueries cannot be used in a recursive CTE query definition.

 

Published by Abdul Samad

Having 17+ years of extensive experience in IT industry, enabled to enhance the team performance and maximize customer satisfaction by strategically managing calls and implementing process improvements. Demonstrated ability to solve problems, meets challenging goals, and expedites delivery. Skilled MSSQL administrator guide team during the crisis situation. Apply Creative thoughts process in re-designing the workflow system to eliminate duplication of effort and increase productivity.

Leave a Reply