Paging SQL Server 2005 Results *

Question

How do I page results in SQL Server 2005?

I tried it in SQL Server 2000, but there was no reliable way to do this. I'm now wondering if SQL Server 2005 has any built in method?

What I mean by paging is, for example, if I list users by their username, I want to be able to only return the first 10 records, then the next 10 records and so on.

Any help would be much appreciated.

Answer

You can use the Row_Number() function. Its used as follows:

SELECT Row_Number() OVER(ORDER BY UserName) As RowID, UserFirstName, UserLastName
FROM Users

From which it will yield a result set with a RowID field which you can use to page between.

SELECT * 
FROM 
    ( SELECT Row_Number() OVER(ORDER BY UserName) As RowID, UserFirstName, UserLastName
      FROM Users 
    ) As RowResults
WHERE RowID Between 5 AND 10

etc

< br > via < a class="StackLink" href=" http://stackoverflow.com/questions/2840/" >Paging SQL Server 2005 Results< /a>

Share on Google Plus

About Cinema Guy

This is a short description in the author block about the author. You edit it by entering text in the "Biographical Info" field in the user admin panel.