WITH common_table_expression (Transact-SQL)
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 SELECT, INSERT, UPDATE, DELETE 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 )
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.
- Using Non-Recursive Common Table Expressions.
- Using Recursive Common Table Expressions.
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:
- A CTE must be followed by a single SELECT, INSERT, UPDATE, 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.
- Multiple CTE query definitions can be defined in a nonrecursive CTE. The definitions must be combined by one of these set operators: UNION ALL, UNION, INTERSECT, or EXCEPT.
- A CTE can reference itself and previously defined CTEs in the same WITH clause. Forward referencing is not allowed.
- 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.
- The following clauses cannot be used in the CTE_query_definition:
- ORDER BY (except when a TOP clause is specified)
- OPTION clause with query hints
- FOR BROWSE
- When a CTE is used in a statement that is part of a batch, the statement before it must be followed by a semicolon.
- A query referencing a CTE can be used to define a cursor.
- Tables on remote servers can be referenced in the CTE.
- 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
CTE using Union ALL
Let’s consider there is a below two scenarios to display the result.
- The first scenario is to display each Item Price of current Year.
- 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
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
Select Query: To view the item history result we select and display all the details.
Example: select * from ItemHistory
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.
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
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 SELECT, INSERT, UPDATE, 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 BY, INTO, COMPUTE or COMPUTE BY, OPTION, FOR XML cannot be used in non-recursive CTE query definition
- SELECT DISTINCT, GROUP BY, HAVING, Scalar aggregation, TOP, LEFT, RIGHT, OUTER JOIN (INNER JOIN is allowed) subqueries cannot be used in a recursive CTE query definition.