Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

My technology blog on SQL Server, TSQL, XML, FILESTREAM and other areas of SQL Server.
Browse by Tags · View All
XML 112
TSQL 69
XQuery 69
XQuery Functions 67
XQuery Training 65
XQuery in TSQL 64
XQuery Tutorial 63
SQL Server XQuery 63
XQuery-Labs 57
BRH 38

Archive · View All
September 2008 32
August 2008 30
July 2008 21
August 2009 19
June 2009 19
May 2010 18
January 2009 15
January 2010 14
October 2008 14
June 2008 13

SQL Server - Server-side paging with Row_number() function

Aug 6 2010 6:38AM by Jacob Sebastian   

If you are a programmer working with SQL Server, you must have found it little embarrassing to display information which spans across multiple pages (web pages). SQL Server 2000 did not allow you to retrieve a specific range of records, say, records 51 to 100 ordered by a certain column.

For example, assume that you are working on a web page which lists the names of all the cities in different countries. Assume that you need to display 25 records in a page. The database has 50,000 records consisting all the cities/towns across the globe. In the above scenario, it really makes sense to retrieve only the required records. for example, in the first page, retrieve 1 to 25 records. When the user clicks on "next" button, retrieve records 26 to 50 and so on. at this stage the user might click on another column to change the sort order. Earlier it was ordered by city name but now the display is based on Zip code.

With SQL Server 2000, it was not very easy to achieve this. Some times people used temp tables achieve this. Others put the paging responsibility to the application which retrieved all the records and then displayed the information needed for the current page. (this approach will not only overload server resources, but also degrades performance of the application as well as the database server.)

SQL Server 2005 introduces a helpful function ROW_NUMBER() which helps in this scenario. Using ROW_NUMBER()  you can generate a sequence number based on a given sort order and then select specific records from the results. Here is an example:

ROW_NUMBER() OVER (ORDER BY City) as Seq

The syntax ideally says the following. "Order the records by City, and then assign a serial number to each record". You can use it in a query as follows.

SELECT 
     ROW_NUMBER() OVER (ORDER BY City) AS row, 
     CityName, 
     Zip, 
     Country 
FROM Cities

However, filtering the records is a little tricky. A TSQL statement like the following will not work.

SELECT 
     ROW_NUMBER() OVER (ORDER BY City) AS row, 
     CityName, 
     Zip, 
     Country 
FROM Cities 
WHERE row BETWEEN 25 AND 50

Again, using the ROW_NUMBER directly inside the WHERE clause does not work (as shown below)

SELECT 
     ROW_NUMBER() OVER (ORDER BY City) AS row, 
     CityName, 
     Zip, 
     Country 
FROM Cities 
WHERE ( ROW_NUMBER() OVER (ORDER BY City) AS row ) BETWEEN 50 BETWEEN 75 

One option is to use an inner query:

SELECT * FROM ( 
     SELECT 
          ROW_NUMBER() OVER (ORDER BY City) AS row, * 
     FROM Cities 
) AS a WHERE row BETWEEN 101 AND 125

The inner query creates a sub-table and then the outer query filters the records from the inner result set. ROW_NUMBER() is a function that I had been waiting for so long and am glad to find with the version 2005 of SQL Server. As the SQL Server Team celebrates the 17th Anniversary this year, I would really like to congratulate them.

Edit on 16 June 2008

It is almost 2 years since I wrote the above post. I had been learning SQL Server 2005 for the last few years (Still learning uh!) and wrote a few articles and blog posts on the new features introduced by SQL Server 2005. One of the articles that is close to the subject discussed here is "Server side paging with SQL Server 2005". This article closely examines the problems related to server-side-paging and then demonstrates a few examples that implements this.

Edit on 31 July 2010

SQL Server CE 4.0 introduced a new TSQL extension that makes paging queries much easier. For example, to fetch rows 21 to 30, a query can be written like this.

SELECT 
   * 
FROM Orders 
ORDER BY OrderID 
OFFSET 20 ROWS 
FETCH NEXT 10 ROWS ONLY;

See Support for Paging Queries in SQL Server CE 4.0 for more details of this feature. I hope this feature will be part of the next SQL Server release.

First published on May 29, 2006

Tags: TSQL, BRH, DBA, #TSQL, performance, #DBA,


Jacob Sebastian
1 · 100% · 32235
2
 
0
Lifesaver
 
 
0
Learned
 
0
Incorrect



Submit

3  Comments  

  • Hi Sebastin, One question, this means everytime the entire query need to be performed?. i mean it fetches some 50000 records everytime out of which some rows will be filtered out by the outer query?..

    Thanks, Nivas

    commented on Sep 4 2012 11:15AM
    Nivas
    2829 · 0% · 3
  • Is your question specific to the new feature I demonstrated with SQL Server 2012? If so, I assume they may be using an efficient mechanism to ensure that only the minimum number of rows are processed to get you the required results.

    If you are referring to my examples using ROWNUMBER, you can make some enhancements to make it faster/better. For example, when requesting data for the first page (which will be used in most of the cases), we may not need ROWNUMBER at all. You may be able to a simple TOP N to get the desired rows.

    The CTE showed in the example I presented in this post processes all the rows, regardless of the current page being processes. This can be further improved by specifying the maximum number of rows needed to get the result for the current page.

    Look at the following query that I presented earlier in my post

    SELECT * FROM ( 
         SELECT 
              ROW_NUMBER() OVER (ORDER BY City) AS row, * 
         FROM Cities 
    ) AS a WHERE row BETWEEN 101 AND 125
    

    Changing this to

    SELECT * FROM ( 
         SELECT TOP  125
              ROW_NUMBER() OVER (ORDER BY City) AS row, * 
         FROM Cities 
         ORDER BY City
    ) AS a WHERE row BETWEEN 101 AND 125
    

    may end up with a more efficient query execution under some circumstances. See that we used a TOP to specify the size of the data we need to process to get the final results.

    No single solution/method may fit in all cases, therefore a clear examination of the query plan for the different approaches may be needed to find the right solution for a given problem.

    commented on Sep 4 2012 12:44PM
    Jacob Sebastian
    1 · 100% · 32235
  • Thanks Sebastian. yes I got the needed answer.

    One more question, what happens if there is a new insert between two successive paging request. There is a possibility that it could be missed right (if the record fall on previous page as per filter )? . I hope you understand what i am trying to say.

    Thanks in advance.

    commented on Sep 4 2012 3:56PM
    Nivas
    2829 · 0% · 3

Your Comment


Sign Up or Login to post a comment.

"SQL Server - Server-side paging with Row_number() function" rated 5 out of 5 by 2 readers
SQL Server - Server-side paging with Row_number() function , 5.0 out of 5 based on 2 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]