Best paging solution using SQL Server 2005?
What is the most efficient paging solution using SQL Server 2005 against a table with around 5,000-10,000 rows? I've seen several out there but nothing comparin开发者_运维问答g them.
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
One of the best discussions of various paging techniques I've ever read is here: SQL Server 2005 Paging – The Holy Grail. You'll have to complete a free registration on SQLServerCentral.com to view the article, but it's well worth it.
Even this should help..
SELECT * FROM
(
SELECT Row_Number() OVER(order by USER_ID) As RowID,
COUNT (USER_ID) OVER (PARTITION BY null) AS TOTAL_ROWS,
select name from usertbl
)
As RowResults WHERE
RowID Between 0 AND 25
Not sure if its better than @keith version.
精彩评论