Best paging solution using SQL Server 2005?
For a table that size, use a Common-Table Expression (CTE) and ROW_NUMBER; use a small function to calculate the records to bring back based on @PageNumber
and @PageSize
variables (or whatever you want to call them). Simple example from one of our stored procedures:
-- calculate the record numbers that we need
DECLARE @FirstRow INT, @LastRow INT
SELECT @FirstRow = ((@PageNumber - 1) * @PageSize) + 1,
@LastRow = ((@PageNumber - 1) * @PageSize) + @PageSize
;
WITH CTE AS
(
SELECT [Fields]
, ROW_NUMBER() OVER (ORDER BY [Field] [ASC|DESC]) as RowNumber
FROM [Tables]
WHERE [Conditions, etc]
)
SELECT *
-- get the total records so the web layer can work out
-- how many pages there are
, (SELECT COUNT(*) FROM CTE) AS TotalRecords
FROM CTE
WHERE RowNumber BETWEEN @FirstRow AND @LastRow
ORDER BY RowNumber ASC
Paging SQL Server 2005 Results
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
SQL Server 2005 filtering and paging with ROW_NUMBER()
Move the where clause inside and leave the row number check outside
SELECT *
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY DateCreated DESC) AS RowId, irweb_Posts.*
FROM irweb_Posts
WHERE ( (IsDeleted = @IsDeleted)
AND (CategoryId = @CategoryId)
AND (DateCreated >= @StartDate)
AND (DateCreated <= @EndDate))
) as p
WHERE ((RowId > @Offset) AND (RowId <= (@Offset + @PageSize)))
What is the best way to paginate results in SQL Server
Getting the total number of results and paginating are two different operations. For the sake of this example, let's assume that the query you're dealing with is
SELECT * FROM Orders WHERE OrderDate >= '1980-01-01' ORDER BY OrderDate
In this case, you would determine the total number of results using:
SELECT COUNT(*) FROM Orders WHERE OrderDate >= '1980-01-01'
...which may seem inefficient, but is actually pretty performant, assuming all indexes etc. are properly set up.
Next, to get actual results back in a paged fashion, the following query would be most efficient:
SELECT *
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, *
FROM Orders
WHERE OrderDate >= '1980-01-01'
) AS RowConstrainedResult
WHERE RowNum >= 1
AND RowNum < 20
ORDER BY RowNum
This will return rows 1-19 of the original query. The cool thing here, especially for web apps, is that you don't have to keep any state, except the row numbers to be returned.
Data Paging in SQL Server 2005 Only Returns 1 (One) Row
If it's only returning one row, chances are there is only one row to return. Add a SELECT * FROM @Products
and look to see what rows were there. In particular, look at the row numbers.
Data Pagination: SQL Server 2005 + SL3
There are usually at least 2 separate lookups involved here, the first to get the pager information and a second to retrieve the data appropriate to the current page.
The pager (slider) needs to know the total number results that would be returned if you weren't doing paging. This is needed to show the pretty "Total # of Results" indicator for your use and to calculate how many total pages you've got. In your case you're going to want to format a nice visual display of the pages for selection (determined by your desired records/page value) in your slider. That's your first query; it should return just a single scalar int or long and you can calculate what you need for your slider display.
The second results include the actual records to be displayed for a given page.
Step 1 of 2 for a page of data is to run a query to filter and order your results by a primary key present within the data you're returning, throwing the key values in a temp table with an AUTO_INCREMENT/IDENTITY field or getting the row number over for the results of a derived table. (Summary: 2 fields, 1 for sequence/1 for primary key to join in step 2).
Step 2 of 2 is to join the key values with your tables which contain data, ordering by the sequence determined in Step 1, selecting only rows numbered (desired start row) to (desired end row) as determined by the page number and page size you've selected.
A stored procedure is strongly advised to keep the data on the server during this operation while you're having to examine more data than you really care to bring back to optimize bandwidth.
What is an efficient method of paging through very large result sets in SQL Server 2005?
Well, for your sample query ROW_COUNT should be pretty fast with thousands of rows, provided you have an index on your PostDate field. If you don't, the server needs to perform a complete clustered index scan on your PK, practically load every page, fetch your PostDate field, sort by it, determine the rows to extract for the result set and again fetch those rows. It's kind of creating a temp index over and over again (you might see an table/index spool in the plain).
No wonder you get timeouts.
My suggestion: set an index on PostDate DESC, this is what ROW_NUMBER will go over - (ORDER BY PostDate DESC, ...)
As for the article you are referring to - I've done pretty much paging and stuff with SQL Server 2000 in the past without ROW_COUNT and the approach used in the article is the most efficient one. It does not work in all circumstances (you need unique or almost unique values). An overview of some other methods is here.
.
Paging results from a complex SQL Server query
A STORED PROCEDURE body could be written as:
;WITH rownums AS (
SELECT tempTable.[link],
ROW_NUMBER() OVER (ORDER BY tempTable.[link]) AS rownum
FROM <temptable here> AS tempTable
)
SELECT tempTable.link
FROM <temptable here> AS tempTable
INNER JOIN rownums AS rn
ON rn.[link] = drn.[link]
WHERE rn.[rownum] BETWEEN @low AND @high
Then you would need to provide the STORED PROCEDURE with the range (@low and @high) and you would need the temp table of course.
P.S. Also, I think your variable declaration will only work on 2008, if I remember correctly 2005 does not support single line instantiation and initialization.
Related Topics
How to Combine Multiple Rows into a Comma-Delimited List in SQL Server 2005
How to Count Instances of Character in SQL Column
Generate a Range of Dates Using SQL
Postgresql: Encoding Problems on Windows When Using Psql Command Line Utility
Postgres Column "X" Does Not Exist
Execution Sequence of Group By, Having and Where Clause in SQL Server
Find the Smallest Unused Number in SQL Server
SQL Query - Concatenating Results into One String
How to Calculate a Running Total in SQL Without Using a Cursor
How to Prevent a Database Trigger from Recursing
Oracle -- Split Multiple Comma Separated Values in Oracle Table to Multiple Rows
How to Delete Duplicate Rows with SQL
How to Convert Comma Separated Nvarchar to Table Records in SQL Server 2005
What Are the Use Cases for Selecting Char Over Varchar in SQL
SQL Server Replace, Remove All After Certain Character
Return Multiple Fields as a Record in Postgresql with Pl/Pgsql
Select Top X (Or Bottom) Percent for Numeric Values in MySQL