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 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 ] ) ]

( 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-Recursive CTE:

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 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.

2. 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.

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

6. When a CTE is used in a statement that is part of a batch, the statement before it must be followed by a semicolon.

7. A query referencing a CTE can be used to define a cursor.

8. Tables on remote servers can be referenced in the CTE.

9. 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:

About Recursive CTE:

Recursive CTEs are use repeated procedural loops aka recursion. The recursive query call themselves until the query satisfied the condition. In a recursive CTE we should provide a where condition to terminate the recursion.

Guidelines for Creating and Using Recursive Common Table Expressions:

1. The recursive CTE definition must contain at least two CTE query definitions, an anchor member and a recursive member. Multiple anchor members and recursive members can be defined; however, all anchor member query definitions must be put before the first recursive member definition. All CTE query definitions are anchor members unless they reference the CTE itself.

2. Anchor members must be combined by one of these set operators: UNION ALL, UNION, INTERSECT, or EXCEPT. UNION ALL is the only set operator allowed between the last anchor member and first recursive member, and when combining multiple recursive members.

3. The number of columns in the anchor and recursive members must be the same.

4. The data type of a column in the recursive member must be the same as the data type of the corresponding column in the anchor member.

5. The FROM clause of a recursive member must refer only one time to the CTE expression_name.

6. The following items are not allowed in the CTE_query_definition of a recursive member:

SELECT DISTINCT
GROUP BY
PIVOT (When the database compatibility level is 110 or higher. See Breaking Changes to Database Engine Features in SQL Server 2016.)
HAVING
Scalar aggregation
TOP
LEFT, RIGHT, OUTER JOIN (INNER JOIN is allowed)
Subqueries
A hint applied to a recursive reference to a CTE inside a CTE_query_definition.

Example:

Firstly we declare the Integer variable as “RowNo” and set the default value as 1 and we have created our first CTE query as an expression name, “ROWCTE”. In our CTE we’ll first display the default row number and next we’ll use a Union ALL to increment and display the row number 1 by one until the Row No reaches the incremented value to 10. To view the result, we will use a select query to display our CTE result.

Declare @RowNo int =1;

;with ROWCTE as

(SELECT @RowNo as ROWNO

UNION ALL

SELECT ROWNO+1

FROM ROWCTE WHERE RowNo < 10)

SELECT * FROM ROWCTE

Output:

CTE Query to display Date Range:

Let’s consider as there is a scenario to display the date from start date to end date all one by one as each row with details. In order to display the recursive data, we will be using the CTE Query.

Here we will write a CTE query to display the dates range with week number and day. For this we set the start and end date in parameter. Here in this example we have used the getdate() to set the start date as Todays date, and for end date we add 16 days from today.

CTE without Union All

Here we can see we have create a simple CTE query to display the RowNo, start date and week number. When we run this we will get only one result with RowNo as “1” ,StartDate as current date and week number along with week day.

Example:

declare @startDate datetime,

@endDate datetime;

select @startDate = getdate(),

@endDate = getdate()+16;

-- select @sDate StartDate,@eDate EndDate

;with myCTE as

(select 1 as ROWNO,@startDate StartDate,'W - '+convert(varchar(2), DATEPART( wk, @startDate))+' / D ('+convert(varchar(2),@startDate,106)+')' as 'WeekNumber')

select ROWNO,Convert(varchar(10),StartDate,105) as StartDate ,WeekNumber from myCTE;

Output:

CTE with Union All

In order to display the result from start date to end date one by one as recursive, we use a Union All to increment RowNo, to add the day one by one till the condition satisfied the date range, in order to stop the recursion we need set some condition. In this example, we repeat the recursion to display our records until the date is less than or equal to the end date.

Example:

declare @startDate datetime,

@endDate datetime;

select @startDate = getdate(),

@endDate = getdate()+16;

-- select @sDate StartDate,@eDate EndDate

;with myCTE as

(select 1 as ROWNO,@startDate StartDate,'W - '+convert(varchar(2), DATEPART( wk, @startDate))+' / D ('+convert(varchar(2),@startDate,106)+')' as 'WeekNumber'

union all

select ROWNO+1 ,dateadd(DAY, 1, StartDate),'W - '+convert(varchar(2),DATEPART( wk, StartDate))+' / D ('+convert(varchar(2),

dateadd(DAY, 1, StartDate),106)+')' as 'WeekNumber'

FROM myCTE WHERE dateadd(DAY, 1, StartDate)<= @endDate)

select ROWNO,Convert(varchar(10),StartDate,105) as StartDate ,WeekNumber from myCTE;

Output:

Multiple CTE

In some scenarios, we need to create more than one CTE query and join them to display our result. In this case, we can use the Multiple CTEs. We can create a multiple CTE query and combine them into one single query by using the comma. Multiple CTE need to be separate by “,” comma fallowed by CTE name.

Example:

We will be using above same date range example to use more than one CTE query, here we can see as we have created two CTE query as CTE1 and CTE 2 to display date range result for both CTE1 and for CTE2.

Declare @startDate datetime,@endDate datetime;

Declare @startDate1 datetime,@endDate1 datetime;

Set @startDate = '2017-02-10'; Set @endDate = '2017-02-15';

Set @startDate1 = '2017-02-16'; Set @endDate1 = '2017-02-28';

WITH CTE1

AS (SELECT 'CTE1' CTEType ,@startDate StartDate,'W'+convert(varchar(2),DATEPART( wk, @startDate))+'('+convert(varchar(2),@startDate,106)+')' as 'WeekNumber'

UNION ALL

SELECT CTEType, dateadd(DAY, 1, StartDate) ,'W'+convert(varchar(2),DATEPART( wk, StartDate))+'('+convert(varchar(2),dateadd(DAY, 1, StartDate),106)+')' as 'WeekNumber' FROM CTE1 WHERE dateadd(DAY, 1, StartDate)<= @endDate),

CTE2

AS (SELECT 'CTE2' CTEType, @startDate StartDate,'W'+convert(varchar(2),DATEPART( wk, @startDate1))+'('+convert(varchar(2),@startDate1,106)+')' as 'WeekNumber'

UNION ALL

SELECT 'CTE2' valuetype, dateadd(DAY, 1, StartDate) ,'W'+convert(varchar(2),DATEPART( wk, StartDate))+'('+convert(varchar(2),dateadd(DAY, 1, StartDate),106)+')' as 'WeekNumber' FROM CTE2 WHERE dateadd(DAY, 1, StartDate)<= @endDate1)

SELECT CTEType, Convert(varchar(10),StartDate,105) as StartDate ,WeekNumber FROM CTE1

UNION ALL

SELECT CTEType, Convert(varchar(10),StartDate,105) as StartDate ,WeekNumber FROM CTE2

Output:

Using CTE query for SQL Table

Now let’s see on, how to use CTE query for our SQL server table data.

Create Database: First, we create a database for creating our table

USE MASTER

-- 1) Check for the Database Exists .If the database is exist then drop and create new DB

IF EXISTS (SELECT [name] FROM sys.databases WHERE [name] = 'CTEDB' )

DROP DATABASE CTEDB

CREATE DATABASE CTEDB

USE CTEDB

Create Table: Now we create a sample Item Table on the created Database.

IF EXISTS ( SELECT [name] FROM sys.tables WHERE [name] = 'ItemDetails' )

DROP TABLE ItemDetails

CREATE TABLE ItemDetails

(

Item_ID int identity(1,1),

Item_Name VARCHAR(100) NOT NULL,

Item_Price int NOT NULL,

Date VARCHAR(100) NOT NULL ,

CONSTRAINT [PK_ItemDetails] PRIMARY KEY CLUSTERED

(

[Item_ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

Insert Sample Data: We will insert few sample records for using in our CTE Query.

Insert into ItemDetails(Item_Name,Item_Price,Date) values('Access Point',950,'2017-02-10')

Insert into ItemDetails(Item_Name,Item_Price,Date) values('CD',350,'2017-02-13')

Insert into ItemDetails(Item_Name,Item_Price,Date) values('Desktop Computer',1400,'2017-02-16')

Insert into ItemDetails(Item_Name,Item_Price,Date) values('DVD',1390,'2017-03-05')

Insert into ItemDetails(Item_Name,Item_Price,Date) values('DVD Player',450,'2017-05-07')

Insert into ItemDetails(Item_Name,Item_Price,Date) values('Floppy',1250,'2017-05-07')

Insert into ItemDetails(Item_Name,Item_Price,Date) values('HDD',950,'2017-07-10')

Insert into ItemDetails(Item_Name,Item_Price,Date) values('MobilePhone',1150,'2017-07-10')

Insert into ItemDetails(Item_Name,Item_Price,Date) values('Mouse',399,'2017-08-12')

Insert into ItemDetails(Item_Name,Item_Price,Date) values('MP3 Player ',897,'2017-08-14')

Insert into ItemDetails(Item_Name,Item_Price,Date) values('Notebook',750,'2017-08-16')

Insert into ItemDetails(Item_Name,Item_Price, Date) values('Printer',675,'2017-07-18')

Insert into ItemDetails(Item_Name,Item_Price,Date) values('RAM',1950,'2017-09-23')

Insert into ItemDetails(Item_Name,Item_Price,Date) values('Smart Phone',679,'2017-09-10')

Insert into ItemDetails(Item_Name,Item_Price,Date) values('USB',950,'2017-02-26')

select * from ItemDetails

CTE Example:

Now we will create a simple temporary result using CTE Query. Here in this CTE Query we have given the expression name as “itemCTE” and we have added the list of Columns which we use in the CTE query. In the CTE query we display all item details with the year.

;WITH itemCTE (Item_ID, Item_Name, Item_Price,SalesYear)

(

SELECT Item_ID, Item_Name, Item_Price ,YEAR(Date) SalesYear FROM ItemDetails

)

Select * from itemCTE

Output: