CTE Data Paging

Day 14 of Common Table Expression Month (June) at SteveStedman.com, today we will be taking a look at data paging with CTE’s and how it compares to the offset and fetch in T-SQL 2012. Now on to CTE Data Paging.

These queries will be using that database that was set up in a previous posting on the CTE_DEMO Sample Database, if you haven’t set up the sample database, download it and set it up now.

The concept of data paging is taking a large set of results and breaking it up into smaller pages of result sets. For instance if you search your favorite internet search engine for a term, you are usually shown results 1 to 20 of X where X is a very large number.  For instance page 1 is results 1 to 20 of 652,443 results.

CTE Data Paging – Before SQL 2012

First lets take a look at a query with no data paging Shows all tables on this database, and all columns, and returns many rows.

SELECT OBJECT_NAME(sc.object_id) as TableName,
       name as ColumnName
  FROM sys.columns sc
ORDER BY OBJECT_NAME(sc.object_id);

When this is run we get 702 rows in the result set:

Now to introduce data paging with a CTE,  Here we have a CTE example to get data paging. This example assumes that the page size is 10 the first page would display the first 10 rows, the the second page would display rows 11 to 20, and the third page would display rows 21 to 30. Here we are using the ROW_NUMBER() function with the OVER clause for the windowing functionality to get the row number from the output. In a non-CTE query you can’t use ROW_NUMBER() in the where statement, but if it is wrapped in a CTE, and given a column alias we can then use it in the WHERE clause selecting from the CTE.

declare @pageNum as int;
declare @pageSize as int;
set @pageNum = 2;
set @pageSize = 10;

;WITH TablesAndColumns AS
(
SELECT OBJECT_NAME(sc.object_id) AS TableName,
 name AS ColumnName,
 ROW_NUMBER() OVER (ORDER BY OBJECT_NAME(sc.object_id)) AS RowNum
 FROM sys.columns sc
)
SELECT *
 FROM TablesAndColumns
 WHERE RowNum BETWEEN (@pageNum - 1) * @pageSize + 1
 AND @pageNum * @pageSize ;

When we run this we get the following results for page 2:

Now if we change the @pageNum variable to be 3 we get the following:

SQL 2012 Data Paging

If you are using SQL Server 2012, there is a new option added to the SELECT statement, this is called OFFSET and FETCH. You have an option to do data paging without the CTE, the OFFSET and FETCH option allows you to do the following:

</pre>
declare @pageNum as int;
declare @pageSize as int;
set @pageNum = 2;
set @pageSize = 10;

SELECT OBJECT_NAME(sc.object_id) AS TableName,
name AS ColumnName
 FROM sys.columns sc
 ORDER BY TableName
OFFSET (@pageNum - 1) * @pageSize ROWS FETCH NEXT @pageSize ROWS ONLY;
<pre>

Which produces similar results for page 2 as the CTE version as shown here:

Which is Better?

The only correct answer is “It Depends”.  It depends on a number of things. First if you are writing code that needs to run on an older version of SQL Server than 2012, then you can’t use the  OFFSET and FETCH way 0f doing it, your best option is the CTE data paging. If you are running on only SQL Server 2012 or newer, then you have a choice. The size of your result set, the design of your tables, the number of JOINs in the query will all impact the overall performance. I would suggest if you are using SQL 2012 and you have a choice, code it both ways, and performance test it. See which runs best base on your specific query.

Related Links:

• My Book on Common Table Expressions
• cte_demo sample database
• TSQL 2012 – OFFSET and FETCH

Common Table Expressions Book

If you enjoyed this posting, and want to learn more about common table expressions, please take a look at my book on CTE’s at Amazon.com. The book is titled Common Table Expressions – Joes 2 Pros® – A CTE Tutorial on Performance, Stored Procedures, Recursion, Nesting and the use of Multiple CTEs.

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!