Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

Paging with SQL Server 2012

  • 8 November 2012
  • Author: Kathi Kellenberger
  • Number of views: 8151
  • 0 Comments

One of the new T-SQL features is a new way to page results.  This allows you to skip a number of rows and view a given number of rows. For example, when you do a search on Amazon for SQL Server books, you will view the results a page at a time.

The new paging functionality is an enhancement of the OVER clause using the new keywords OFFSET  ROWS and FETCH NEXT or FETCH FIRST ROWS ONLY. Here is an example using the AdventureWorks2012 database:

SELECT ProductID, Name
FROM Production.Product
ORDER BY Name
OFFSET 90 ROWS FETCH NEXT 10 ROWS ONLY;

This example skips past the first 90 rows and then returns the next 10. It doesn’t matter if we use FETCH NEXT or FETCH FIRST, we will see the same results. 

Back in 2005, we were given a way to do paging with the ROW_NUMBER() function.


WITH Products AS (
     SELECT ProductID, Name,
            ROW_NUMBER() OVER(ORDER BY Name) AS RowNum
      FROM Production.Product
 )
SELECT ProductID, Name
FROM Products
WHERE RowNum BETWEEN 91 AND 100;

I wondered how the performance of these two compare. In this case, they both do a non-clustered index scan. 

 
 
If I turn on SET STATISTICS, they both show one scan with two logical reads.


 
For this particular example, the two queries performed the same.  The important thing to keep in mind is that using either of these methods will produce a scan each time. Both methods will perform poorly on a large dataset if they have to do a clustered index or table scan.

Now we have a very easy way to return data a page at a time, but make sure that you spend the time to determine if the performance will be acceptable with your data.

 

 

Print
Categories: SQL Server
Tags:
Rate this article:
No rating

Please login or register to post comments.