浅谈SQL Server2005的几种分页方法

 我来答
一骑当后
2014-10-30 · 知道合伙人数码行家
一骑当后
知道合伙人数码行家
采纳数:40298 获赞数:306440
网络、设备维护、电路、弱电检测。

向TA提问 私信TA
展开全部
SQL Server分页查询是我们经常会用到的功能,其实现方法也有很多,本文的几种分页方法摘自《SQL Server2005性能调优》一书。希望对您学习SQL Server分页查询方面能有所帮助。
用以下脚本生成测试数据:
CREATE TABLE TRANS_TABLE(
MYID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
MYDESC VARCHAR(10),
MYDATE DATETIME,
MYGROUPID INT)
DECLARE @I INT
SET @I = 0WHILE @I < 1000000
BEGIN
INSERT INTO TRANS_TABLE
SELECT CHAR(ASCII('A') - 2 + (2 * (1 + ABS(CHECKSUM(NEWID())) % 26))),
DATEADD(day, ABS(CHECKSUM(NEWID())) % 365, '01/01/2007'),
(ABS(CHECKSUM(NEWID())) % 10)
SET @I = @I + 1
END
CREATE NONCLUSTERED INDEX IX_TRANS_TABLE_MYDATE
ON TRANS_TABLE(MYDATE)
CREATE NONCLUSTERED INDEX IX_TRANS_TABLE_MYGROUPID
ON TRANS_TABLE(MYGROUPID)

1、基于CTE分页
1)用row_number()排名函数,派生表的方式分页
DECLARE @START_ID int, @START_ROW int, @MAX_ROWS int
SELECT @START_ROW = 1, @MAX_ROWS = 25
select *
from ( select p.*, rownum rnum
FROM (
SELECT ROW_NUMBER() OVER(ORDER BY MyDate, MYID) AS rowNum, *
FROM TRANS_TABLE (NOLOCK)
) p
where rownum <= @START_ROW + @MAX_ROWS - 1
)
z where rnum >= @START_ROW

2)用CTE方式取代派生表
DECLARE @START_ROW int, @MAX_ROWS int, @TOT_ROW_CNT int
SELECT @START_ROW = 1, @MAX_ROWS = 25;
WITH PAGED AS (
SELECT ROW_NUMBER() OVER(ORDER BY MyDate, MYID) AS rowNum, *
FROM TRANS_TABLE (NOLOCK)
)
SELECT *
FROM PAGEDWHERE ROWNUM BETWEEN @START_ROW AND @START_ROW + @MAX_ROWS-1
3)也是CTE方法,但是根据测试数据显示这种性能比前两种都好
DECLARE @START_ROW int, @MAX_ROWS int, @TOT_ROW_CNT int
SELECT @START_ROW = 1, @MAX_ROWS = 25;
WITH PAGED AS (
SELECT ROW_NUMBER() OVER(ORDER BY MyDate, MYID) AS rowNum, MYID
FROM TRANS_TABLE (NOLOCK)
)
SELECT TT.*
FROM PAGED PGD
INNER JOIN TRANS_TABLE TT
ON PGD.MYID = TT.MYID
WHERE ROWNUM BETWEEN @START_ROW AND @START_ROW + @MAX_ROWS - 1
ORDER BY MyDate, MYID
2、 基于ROW_COUNT的分页
DECLARE @START_ID int, @START_ROW int, @MAX_ROWS int,
@START_DATETIME DATETIME, @TOT_ROW_CNT INT
SELECT @START_ROW = 1, @MAX_ROWS = 25
-- Get the first row for the page
SET ROWCOUNT @START_ROW
SELECT @START_ID = MYID, @START_DATETIME = MYDATE FROM TRANS_TABLE (NOLOCK)
ORDER BY MYDATE, MYID
-- Now, set the row count to MaximumRows and get
-- all records >= @first_idSET ROWCOUNT @MAX_ROWS
SELECT *
FROM TRANS_TABLE (NOLOCK)
WHERE MYID >= @START_ROW
AND MYDATE >= @START_DATETIME
ORDER BY MYDATE, MYID
SET ROWCOUNT 0
3、 TOP @X分页
SQL Server 2005中可以把返回行数做为参数传给top语句。
DECLARE @START_ID int, @START_ROW int, @MAX_ROWS int, @TOT_ROW_CNT INT, @START_DESC VARCHAR(10)
SELECT @START_ROW = 1, @MAX_ROWS = 25
-- Get the first row for the page
SELECT TOP(@START_ROW) @START_ID = MYID, @START_DESC = MYDESC FROM TRANS_TABLE (NOLOCK)
ORDER BY MYDESC, MYID
SELECT TOP(@MAX_ROWS) *
FROM TRANS_TABLE (NOLOCK)
WHERE MYID >= @START_ROW
AND MYDESC >= @START_DESC
ORDER BY MYDESC, MYID

4、 Temp表分页
DECLARE @START_ROW int, @MAX_ROWS int, @TOT_ROW_CNT int
SELECT @START_ROW = 1, @MAX_ROWS = 25;
SELECT ROW_NUMBER() OVER(ORDER BY MyDate, MYID) AS rowNum,
MYID
into #TEMP
FROM TRANS_TABLE (NOLOCK)
SELECT TT.*
FROM TRANS_TABLE (NOLOCK) TT
INNER JOIN #TEMP TON TT.MYID = T.MYID
WHERE ROWNUM BETWEEN @START_ROW AND @START_ROW + @MAX_ROWS - 1
DROP TABLE #TEMP

以上便是这次为您介绍的 SQL Server 2005中几种分页方法,希望对您学习SQL Server分页查询方面能有所帮助。
推荐律师服务: 若未解决您的问题,请您详细描述您的问题,通过百度律临进行免费专业咨询

为你推荐:

下载百度知道APP,抢鲜体验
使用百度知道APP,立即抢鲜体验。你的手机镜头里或许有别人想知道的答案。
扫描二维码下载
×

类别

我们会通过消息、邮箱等方式尽快将举报结果通知您。

说明

0/200

提交
取消

辅 助

模 式