With SQL Server 2005, it is now a lot easier to use paged queries
than in previous versions. I will be using the NorthWind database
(mostly), so you can also use the examples I have provided. I will keep
the examples simple; anything complex will only cause confusion. I will
start with "traditional" methods such as
I was asked a question or a series of questions, "How would you do paging in a SQL? How would you do this with a lot of records, say, 10,000 or more?"
I thought about answers. To be more precise, I thought of more questions and this got me thinking, "This must be a common problem, every developer must have done or solved this. What about paging sizes and working with very large data sets? What about getting results from multiple tables?"
So, I decided to look into these questions with specific reference to SQL Server 2005. The following is by far the easiest way and should be used, but it is rarely this easy.
select * from mytable
select top 10 * from customers -- This is a very basic example.
This is really useful. When you want to pull records 11 to 20 you could use temp tables.
-- SELECT First 30 records in to Temp table
This is fine for the first few pages or the first few users. If you
have users that want to return page after page after page, you end up
getting 1000 records to return 10, which is not very efficient. You
could also have placed an identity on the first temp table and used a
There is an alternative to
SET rowcount 10
This is new to SQL Server 2005 and looks really useful. Below is an example to get records 20 to 29 from a results set. It might a bit strange at first, but I will go through the query so you'll see how simple it is.
With Cust AS
I hope you are still with me. If not, run the code and view the results. This is really very quick for large tables; I have been impressed with the speed on tables with over 250,000 records.
Now we will put it all together in a Stored Procedure that can be used by your application. I won't show a .NET datagrid or similar control, as that is outside the scope of this article. The stored procedure below uses flexible page sizes and page numbers, so you can select any page at random. This is quite useful if you wish to jump ahead 10 pages to find a particular record. The paging for this example starts at page 1 rather than at page 0, but this can be easily changed.
CREATE PROC GetCustomersByPage
To run this, simply specify the page size and page number (