使用SQL Server 2005的最佳分页解决方案? [英] Best paging solution using SQL Server 2005?
本文介绍了使用SQL Server 2005的最佳分页解决方案?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
使用SQL Server 2005针对具有约5,000-10,000行的表的最有效的分页解决方案是什么?我已经看到了几个,但是没有比较它们的地方.
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 comparing them.
推荐答案
对于大小合适的表,请使用通用表表达式(CTE)和ROW_NUMBER;使用一个小函数根据@PageNumber
和@PageSize
变量(或您要调用的任何变量)计算要取回的记录.来自我们的存储过程之一的简单示例:
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
这篇关于使用SQL Server 2005的最佳分页解决方案?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文