SQL Server 2012 - Server side paging demo using OFFSET/FETCH NEXT

SQL Server 2005 introduced ROW_NUMBER() which provided an easy option to implement server side paging. SQL Server 2012 added another enhancement which made paging much simpler and efficient.

All the scripts in this demo assume that a database with name **NorthPole** exists in the current SQL Server instance. In case you do not have it, just create an empty database with the same name or edit the script below to point to a database where you would like to run the scripts.

USE NorthPole 
GO
 
IF OBJECT_ID('Customers','U') IS NOT NULL
DROP TABLE Customers
 
CREATE TABLE Customers (
CustomerID INT,
CustomerNumber CHAR(4),
CustomerName VARCHAR(50),
CustomerCity VARCHAR(20) )
GO
 
INSERT INTO Customers (
CustomerID, CustomerNumber, CustomerName, CustomerCity
)
SELECT
Number, 
REPLACE(STR(Number, 4), ' ', '0'),
'Customer ' + STR(number,6),
CHAR(65 + (number % 26)) + '-City'
FROM master..spt_values WHERE type = 'p'
AND number BETWEEN 0 AND 999

The above script will insert 1000 rows to the **Customers** table. We will use this data to run the demo scripts given below.

SQL Server 2005 introduced `ROW_NUMBER()` which provided an easy way to generate a sequence number in a result set, which was quite helpful to write queries that perform server-side-paging. In SQL Server 2005 and 2008, we can write a query such as the one given below.

USE NorthPole  
GO
 
DECLARE @page INT, @size INT
SELECT @page = 3, @size = 10
 
;WITH cte AS (
SELECT  TOP (@page * @size)
CustomerID, 
CustomerName, 
CustomerCity,
ROW_NUMBER() OVER(ORDER BY CustomerName ) AS Seq,
COUNT(*) OVER(PARTITION BY '') AS Total
FROM Customers
WHERE CustomerCity IN ('A-City','B-City')
ORDER BY CustomerName ASC
)
SELECT
*
FROM cte
WHERE seq BETWEEN (@page - 1 ) * @size + 1 AND @page * @size
ORDER BY seq
 
/*
CustomerID  CustomerName    
----------- ----------------
260         Customer 260 
261         Customer 261 
286         Customer 286 
287         Customer 287 
312         Customer 312 
313         Customer 313 
338         Customer 338 
339         Customer 339 
364         Customer 364 
365         Customer 365 
*/

The above query returns 10 rows from the **customers** table. The query filters customers by the city specified and sorts the results by Customer name. Finally, rows from position 21 to 30 is returned.

SQL Server 2012 provides an easier syntax to achieve the above. We can use `OFFSET` to specify the starting position of the rows and `FETCH NEXT N` to specify the number of rows to retrieve from the `OFFSET`. Here is the new version of the above query.

USE NorthPole  
GO
 
DECLARE @page INT, @size INT
SELECT @page = 3, @size = 10
 
SELECT
*,
COUNT(*) OVER(PARTITION BY '') AS Total
FROM Customers
WHERE CustomerCity IN ('A-City','B-City')
ORDER BY CustomerID 
OFFSET (@page -1) * @size ROWS
FETCH NEXT @size ROWS ONLY;

You may notice a performance difference between both versions of the query. When running both queries on the given sample data on my laptop, I am seeing that the approach using `ROW_NUMBER()` takes 60% of execution time and the new approach (using `OFFSET FETCH NEXT` takes only 40%).

Keep in mind that the test data used in this demo is very simple and will be quite different from production scenarios. So it is important to run tests on your own test data with specific work load and server configuration before accurately assuming the performance statistics of each approach.

posted @ 2014-06-26 18:20 princessd8251 阅读(225) 评论(0) 收藏举报

刷新页面返回顶部