Tag Archives: CTE

Data Paging by OFFSET…FECTCH and CTE – Sql Server

When we have huge result set it doesn’t make sense to return the complete result set to the application and leave it to the datagrid, gridview or any other data control to handle the data paging. Because it wastes lot of network resources, puts pressure on the database server and the application server, so it makes page loading very slow. In such scenario it makes sense to return only the rows which needs to displayed on the current page, when user clicks next page/next result set then bring the next set of rows. In this way it reduces the unnecessary network traffic and load on the data base server, which in-turn helps in faster loading of the page.

With Sql Server 2012 Microsoft has introduced OFFSET and FETCH feature for data paging. Prior to Sql Server 2012 CTE was on of the option for data paging.

To demonstrate data paging let us create a customers table as shown in the below image by the following script:

Data Paging Using CTE
Script:

--Create Demo database
CREATE DATABASE SqlHintsCTEDataPaging
GO
USE SqlHintsCTEDataPaging
GO
-- Create an Customers table.
CREATE TABLE dbo.Customers
( Id INT  PRIMARY KEY, FirstName NVARCHAR(50),
  LastName NVARCHAR(50))
-- Populate Customer table with sample data
INSERT INTO dbo.Customers VALUES
 (1, 'Basavaraj', 'Biradar') ,(2, 'Abhishiek', 'Akkanna')
,(3, 'Santosh', 'Patil'), (4, 'Sachin', 'Tendulkar')
,(5, 'Virendra', 'Shewag'),(6, 'Virat', 'Kohli')
,(7, 'Ajinkya', 'Rahane'), (8,'Cheteshwar', 'Pujara')

Data Paging by OFFSET/FETCH

We can write a stored procedure like below for Data Paging by using the OFFSET..FETCH pagination feature introduced in Sql Server 2012. This stored procedure gives specified number of rows (i.e. it can be specified by the parameter @pageSize) for the passed page number (i.e. parameter @pageNum) sorted by the FirstName column.

CREATE PROCEDURE dbo.GetCustomersPagedDatabyFetch
(
  @pageNum INT,
  @pageSize INT
)
AS
BEGIN
	SELECT Id, FirstName, LastName
	FROM dbo.Customers WITH(NOLOCK)
	ORDER BY FirstName
	OFFSET (@pageNum - 1) * @pageSize ROWS
	FETCH NEXT @pageSize ROWS ONLY  
END

Try to get the first page data sorted by FirstName by executing the above Stored Procedure, assume that the page size as 3 for our example.

EXEC dbo.GetCustomersPagedDatabyFetch 
         @pageNum = 1, @pageSize = 3

RESULT:
Data Paging by OFFSET and FETCH Page 1

Now try to get the second page data sorted by FirstName by executing the below statement.

EXEC dbo.GetCustomersPagedDatabyFetch 
         @pageNum = 2, @pageSize = 3

RESULT:
Data Paging by OFFSET and FETCH Page 2

Now try to get the third page data sorted by FirstName by executing the below statement.

EXEC dbo.GetCustomersPagedDatabyFetch 
         @pageNum = 3, @pageSize = 3

RESULT:
Data Paging by OFFSET and FETCH Page 3

The reason we got only two records in the above result even when the data page size 3 is, the demo Customers table created in this article has only 8 records. So, with page size as 3 the third page will have only two records.

Data paging by CTE

We can write a stored procedure like below for Data Paging by CTE. This stored procedure gives specified number of rows (i.e. it can be specified by the parameter @pageSize) for the passed page number (i.e. parameter @pageNum) sorted by the FirstName column.

CREATE PROCEDURE dbo.GetCustomersPagedData
(
	@pageNum INT,  -- Data page number
	@pageSize INT  -- Number of rows per page 
)
AS
BEGIN
	WITH PagingCTE AS
	(
	 SELECT Id, FirstName, LastName, 
          ROW_NUMBER() OVER (ORDER BY FirstName) AS RowNumber
	 FROM dbo.Customers WITH(NOLOCK)
	)
	SELECT *
	FROM PagingCTE
	WHERE RowNumber BETWEEN (@pageNum - 1) * @pageSize + 1 
              AND @pageNum * @pageSize	
END

Try to get the first page data sorted by FirstName by executing the above Stored Procedure, assume that the page size as 3 for our example.

EXEC dbo.GetCustomersPagedData @pageNum = 1, @pageSize = 3

RESULT:
Data Paging by CTE

Now try to get the second page data sorted by FirstName by executing the below statement.

EXEC dbo.GetCustomersPagedData @pageNum = 2, @pageSize = 3

RESULT:
Data Paging by CTE data page 2

Now try to get the third page data sorted by FirstName by executing the below statement.

EXEC dbo.GetCustomersPagedData @pageNum = 3, @pageSize = 3

RESULT:
Data Paging by CTE data page 3

The reason we got only two records in the above result even when the data page size 3 is, the demo Customers table created in this article has only 8 records. So, with page size as 3 the third page will have only two records.

Data paging by CTE with dynamic Sort column

In the previous example of this article the SP GetCustomersPagedData was supporting the data paging but the sort column here was always the FirstName column. But in real world we will always not have the data paging with sorting by just one column. For example, for the customers table we may need to support data paging with either FirstName or LastName as the sorting column. We can create a SP like below which supports the data paging by the FirstName or LastName as the sort column by using CTE:

CREATE PROCEDURE dbo.GetCustomersDynamicSortColumn
(
	@pageNum INT,
	@pageSize INT,
	@sortColumnName VARCHAR(50)
)
AS
BEGIN
  WITH PagingCTE AS
  (
    SELECT Id, FirstName, LastName, ROW_NUMBER() OVER 
     (ORDER BY CASE 
       WHEN @sortColumnName = 'FirstName' THEN  FirstName 
       WHEN @sortColumnName = 'LastName' THEN  LastName 
						 END) AS RowNumber
    FROM dbo.Customers WITH(NOLOCK)
  )
  SELECT *
  FROM PagingCTE
  WHERE RowNumber BETWEEN (@pageNum - 1) * @pageSize + 1 
   AND @pageNum * @pageSize
END

Now try to execute the above stored procedure to get the first page data with page size as 3 and sort column as FirstName:

EXEC dbo.GetCustomersDynamicSortColumn 
      @pageNum = 1, @pageSize = 3, @sortColumnName = 'FirstName'

RESULT:
Data Paging by CTE dynamic Sort Column

Now execute the following statement to get the first page data with page size as 3 and sort column as LastName:

EXEC dbo.GetCustomersDynamicSortColumn 
      @pageNum = 1, @pageSize = 3, @sortColumnName = 'LastName'

RESULT:
Data Paging by CTE dynamic Sort Column 2

Nested Common Table Expressions (i.e. CTE) – Sql Server

This is the fourth article in the series of articles on Common Table Expression in Sql Server. Below are the other articles in this series:

Introduction to Common Table Expression (a.k.a CTE)
Recursive CTE
Multiple CTEs in a Single Query

Nested Common Table Expressions

Nested CTEs is a scenario where one CTE references another CTE in it.

EXAMPLE 1: Below is a basic example of a Nested CTE:

Nested CTES

WITH FirstCTE 
   AS (SELECT 1 EmployeeId, 'Shreeganesh Biradar' Name)
, SecondCTE 
   AS (SELECT EmployeeId, Name, 'India' Country FROM FirstCTE)
SELECT *   FROM SecondCTE

RESULT:
Nested CTES result ver2

EXAMPLE 2: Below is another example of a Nested CTE:

This example uses the Employees table created in the previous article Introduction to Common Table Expression (a.k.a CTE). You can visit the link to create the table, if you have not created it already.

WITH FirstCTE AS 
   (SELECT Id EmployeeId, Name, ManagerId 
	FROM dbo.Employees WITH(NOLOCK) 
	WHERE ManagerId is NULL)
,  	SecondCTE AS
	(SELECT E.Id EmployeeId, E.Name, E.ManagerId, 
	        FCTE.Name ManagerName
	 FROM dbo.Employees E WITH(NOLOCK)
		INNER JOIN FirstCTE FCTE
		 ON E.ManagerId = FCTE.EmployeeId)
SELECT * FROM SecondCTE

RESULT:
Nested CTE Result 2

Multiple CTEs in a Single Query – Sql Server

This is the third article in the series of articles on Common Table Expression in Sql Server. Below are the other articles in this series:

Introduction to Common Table Expression (a.k.a CTE)
Recursive CTE
Nested Common Table Expressions

Multiple CTEs in a Single Query

Many a times we come across a scenario, where we need to use multiple CTEs in a single query. For instance assume first CTE gives employee details and second CTE gives total salary paid to the employee year to date after performing all the aggregation of the salary paid from the beginning of the year to till date. So, here combining the first CTE with the Second CTE in a single query gives a meaning ful details of employee with his year to date salary.

Below is very basic example of using multiple CTEs in a single query:

Multiple CTES in a Single query Ver2

WITH FirstCTE AS(SELECT 1 EmployeeId,'Shreeganesh Biradar' Name)
	, SecondCTE AS (SELECT 1 EmployeeId, '$ 100000' YTDSalary)
SELECT FC.EmployeeId, FC.Name, SC.YTDSalary
FROM FirstCTE FC
		INNER JOIN SecondCTE SC
		ON FC.EmployeeId = SC.EmployeeId

RESULT:
Multiple CTES in a Single query Result Ver2

Recursive CTE – Sql Server

This is the second article in the series of articles on Common Table Expression in Sql Server. Below are the other articles in this series:

Introduction to Common Table Expression (a.k.a CTE)
Multiple CTEs in a Single Query
Nested Common Table Expressions

Recursive Common Table Expression

CTEs provide a mechanism to write easy to understand, more readable and maintainable recursive queries. Prior to CTEs only mechanism to write recursive query is by means of recursive function or stored procedure. And these recursive functions or stored procedures support only up-to 32 levels of recursion. By default CTEs support a maximum recursion level of 100. CTEs also provide an option to set a MAXRECURSION level value between 0 to 32,767. Specifying it’s value as 0 means no limit to the recursion level, you agreed for a risk in case of a poorly written query resulting in infinite recursion level.

Recursive CTE Example:

Below is a very basic example of a recursive CTE generating numbers between 1 to 10:

RecursiveCTE Basic Example

WITH NumbersCTE AS
(
	SELECT  1 AS Number
	UNION ALL
	SELECT Number + 1 FROM NumbersCTE
	WHERE Number < 10
 )
SELECT * FROM NumbersCTE

RESULT:
Recursive CTE Basic Example result

Example of Recursive CTE to get Managers and employees reporting to them

This example uses the Employees table created in the previous article Introduction to Common Table Expression (a.k.a CTE). You can visit the link to create the table, if you have not created it already.

WITH EmpRecursiveCTE(ManagerId, EmployeeId, Name, Level) AS 
(
    SELECT ManagerId, Id, Name, 0 AS Level
    FROM dbo.Employees 
    WHERE ManagerId IS NULL
		UNION ALL
    SELECT E.ManagerId, E.Id, E.Name, Level + 1
    FROM dbo.Employees E
        INNER JOIN EmpRecursiveCTE EmpCTE
        ON E.ManagerID = EmpCTE.EmployeeId 
)
SELECT EmployeeId, Name, ManagerID, Level 
FROM EmpRecursiveCTE
ORDER BY ManagerID

RESULT:
Recursive CTE Basic Example result Employee Hierarchy

MAXRECURSION hint in Recursive CTE

By default maximum recursion level supported by CTE is 100. But CTE provides an option to change it by means of the MAXRECURSION hint. MAXRECURSION hint value can be between 0 to 32,767. Specifying it’s value as 0 means no limit.

Let us understand MAXRECURSION hint in a Recursive CTE by the below examples:

CTEs default maximum recursion level

Try to generate numbers between 1 to 200 by using a recursive CTE by the following script:

WITH NumbersCTE AS
(
    SELECT  1 AS Number
    UNION ALL
    SELECT Number + 1 FROM NumbersCTE
    WHERE Number < 200
 )
SELECT * FROM NumbersCTE

RESULT:

Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

From the above result it is clear that, the CTEs default maximum recursion level is 100.

How to change CTEs default maximum recursion level?

We can change the CTEs default maximum recursion by specifying the MAXRECURSION query hint. Change the previous recursive CTE to generate numbers between 1 to 200 by specifying the MAXRECURSION hint value as 210 as below and verify the result:

WITH NumbersCTE AS
(
    SELECT  1 AS Number
    UNION ALL
    SELECT Number + 1 FROM NumbersCTE
    WHERE Number < 200
 )
SELECT * FROM NumbersCTE
OPTION (MAXRECURSION 210)

RESULT:
Recursive CTE with MAXRECURSION hint

From the above result it is clear that we can change the CTEs default maximum recursion by means of MAXRECURSION query hint.

MAXRECURSION query hint is helpful to terminate the poorly written recursive CTE

MAXRECURSION hint is very helpful in a scenario where poorly written CTE is resulting in an infinite recursion level, in such cases MAXRECURSION terminates the CTE once the defined recursion crosses. For example below is an infinite recursive CTE, but because of the MAXRECURSION hint value 210 it terminates the CTE execution once it reaches the recursion level of 210.

WITH NumbersCTE AS
(
    SELECT  1 AS Number
    UNION ALL
    SELECT Number + 1 FROM NumbersCTE   
 )
SELECT * FROM NumbersCTE
OPTION (MAXRECURSION 210)

RESULT:
Infinite Recursive CTE with MAXRECURSION hint
Maximum recursion level that we can specify with MAXRECURSION query hint is 32,767

Try to generate the numbers between 1 to 40000 by the below script, by specifying the MAXRECURSION query hint value as 40000.

WITH NumbersCTE AS
(
    SELECT  1 AS Number
    UNION ALL
    SELECT Number + 1 FROM NumbersCTE  
	WHERE  Number < 40000
 )
SELECT * FROM NumbersCTE
OPTION (MAXRECURSION 40000)

RESULT:

Msg 310, Level 15, State 1, Line 9
The value 40000 specified for the MAXRECURSION option exceeds the allowed maximum of 32767.

From the above result it is clear that the maximum recursion level we can specify with the MAXRECURSION query hint is 32,767.

How to support the recursion level greater than the maximum recursion level 32,767?

If we come across a scenario, where we need to have recursion level greater than 32,767, in such scenarios we can achieve this by specifying MAXRECURSION value as 0. MAXRECURSION query hint value 0 means no limit to the recusion level, if we are specifying this we should make sure that our query is not resulting in an infinite recursion level. So, we can re-write the previous recursive CTE as below:

WITH NumbersCTE AS
(
    SELECT  1 AS Number
    UNION ALL
    SELECT Number + 1 FROM NumbersCTE  
	WHERE  Number < 40000
 )
SELECT * FROM NumbersCTE
OPTION (MAXRECURSION 0)

RESULT:
Recursive CTE MAXIMUM Recursion Level 0

Introduction to Common Table Expression (a.k.a CTE) in Sql Server

This is the first article in the series of articles on Common Table Expression in Sql Server. Below are the other articles in this series:

Recursive CTE
Multiple CTEs in a Single Query
Nested Common Table Expressions

Introduction to Common Table Expression

Common Table expression (a.k.a CTE) is one of the new feature introduced in Sql Server 2005. CTE provides the structured way for writing the complex queries as a simple readable and maintainable queries. CTEs provides an alternative way of writing sub queries in a more readable and maintainable way.

CTEs are something like temporary named result sets, but they are not exactly as temporary table or table variable. CTEs doesn’t use the TempDB for maintaining the temporary named result set. And also the scope of the CTEs temporary named result set is to the immediate next SELECT, INSERT, UPDATE or DELETE statement only.

CTEs are like views, for views meta data is stored in the database but not for CTES. From this point of view we can call CTEs as a dynamic view or temporary view.

In most of the cases CTEs performance is on par with the existing sub queries, table variable or temp table approaches. The main thing with CTEs is, it apart from providing a mechanism to write simple readable and maintainable queries. It also provides a mechanism to write recursive queries. And the current recursion level supported by the recursive function or stored procedure is 32 level only, where as by default max recursion level which CTE is 100. But CTE provides an option to set a MAXRECURSION level value between 0 to 32,767 . Specifying it’s value as 0 means no limit, you agreed for a risk and server crash by a bugy query if any 🙂

CTE SYNTAX

[ WITH <common_table_expression> [ ,...n ] ]

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

I think we had enough theory, let us jump on to the examples to understand all the above explained theory.

CTE Syntax explained by a very basic example

CTE in Sql Server

To demonstrate the CTE feature with extensive list of examples, let us create a demo database SqlHintsCTED with Employees table having the sample data as shown in the below image by the following script:

CTE Example Table
Script:

--Create demo database
CREATE DATABASE SqlHintsCTE
GO
USE SqlHintsCTE
GO
-- Create an Employees table.
CREATE TABLE dbo.Employees
( Id INT  PRIMARY KEY, Name NVARCHAR(50), ManagerId INT NULL)
GO
-- Populate Employees table with sample data
INSERT INTO dbo.Employees VALUES
 (1, 'Prabhu Biradar',NULL) ,(2, 'Sharan Biradar', 1)
,(3, 'Monty Biradar', 2), (4, 'Shashank Biradar', 2)
,(5, 'Basavaraj Biradar',1),(6, 'Keerthi Biradar',1)
,(7, 'Shree Biradar',5), (8,'Shree Sharan', 7)

Very basic CTE example, here skipped defining the optional CTE column_names

Below is an example of a very basic CTE, in this CTE example the optional CTE column_names are not specified. So, in this case the the column names specified in the CTE query_definition will be returned.

WITH DemoCTE
AS (SELECT Id, Name FROM dbo.Employees)
SELECT * FROM DemoCTE

RESULT:
Basic CTE Result

If CTE is not the first statement in the batch, then the previous statement to it Should be terminated with a semicolon.

Let us understand this with an example. In the below example we have PRINT statement before the CTE, let us see what is the result of executing the following script:

PRINT 'CTE Demo'
WITH CTESemicolon
AS (SELECT Id, Name FROM dbo.Employees WITH(NOLOCK))
SELECT * FROM CTESemicolon WHERE id = 2
GO

RESULT:

Msg 319, Level 15, State 1, Line 2
Incorrect syntax near the keyword ‘with’. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

To resolve this error let us terminate the PRINT statement with a Semicolon and see what is the result:

PRINT 'CTE Demo';
WITH CTESemicolonPrefix
AS (SELECT Id, Name FROM dbo.Employees WITH(NOLOCK))
SELECT * FROM CTESemicolonPrefix WHERE id = 2
GO

RESULT:
CTE Should be prefixed by Semicolon

From the above result it is clear that, if CTE is not the first statement in the batch of statements, then the previous statement to it must be terminated with a semicolon.