Paginating data is one of those commonly required tasks. In previous version of MS SQL Server it's been tricky to handle, but made easy via the use of stored procedures that handle the pagination logic for you. However, if you're using SQL Server 2005 there's a much easier way to handle pagination and that's to use a Common Expression Table (CTE.)
Microsoft created the CTE syntax to make solving complicated tasks easier (such as returning a query of hierarchical tree data.) When you create a CTE, you're essentially creating a virtual table you can query against. CTEs generally perform very well and can often replace the need for creating temp tables.
Let's look at an example of query out records 11-20 from a Employee database:
-- create the Common Table Expression, which is a table called "pagination" with pagination as ( -- your normal query goes here, but you put your ORDER BY clause in the rowNo declaration select row_number() over (order by department, employee) as rowNo, -- a list of the column you want to retrieve employeeId, employee, department from Employee where disabled = 0 ) -- we now query the CTE table select -- add an additional column which contains the total number of records in the query *, (select count(*) from pagination) as totalResults from pagination where RowNo between 11 and 20 order by rowNo
The first part of the query creates the table expression we'll actually query against when we grab the actual results from the database. In the CTE you're going to write the SQL you'd normally write to grab all the records with one exception—you use the row_number() function to generate a virtual column that orders your result rows for you. The actual "order by" clause you want to use will actually go in the row_number() declaration—this makes SQL Server assign the correct row number for each record.
It's important to remember that a CTE can be as complex as you need it to be. You can do joins, pivots, etc—whatever you need.
The next step is to actually query against the CTE you created. This query will virtually be the same for all pagination queries you write. You are simply grabbing all of the columns and then limiting the returned data to just the rows you want to display (in this case rows 11 - 20.)
In this example, I added an additional column that contains the total results that were returned from the CTE. This is not needed, but it's handy if you need to do something like "Results 11 - 20 of 4,567 records." There are many ways you could generate the total records, this is just one method.
If you've upgraded to SQL Server 2005 and haven't started using CTEs yet, you really need to start getting familiar with them as they really simplify many tasks in SQL Server that previously were very difficult to solve.