浅谈SQL Server2005的几种分页方法
来源:互联网 发布:win10如何卸载软件 编辑:程序博客网 时间:2024/05/05 16:35
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分页查询方面能有所帮助。
- 浅谈SQL Server2005的几种分页方法
- 浅谈js分页的几种方法
- sql 几种分页方法
- 一个sql server2005分页的存储过程
- linq to sql 分页 的几种方法
- sql server2005 数据分页
- sql server2005分页rownumber()
- SQL Server2005杂谈(4):在SQL Server2005中按列连接字符串的三种方法
- SQL Server2005杂谈(4):在SQL Server2005中按列连接字符串的三种方法
- 分页的几种方法
- SQL Server2005及以上 存储过程分页方法分享
- 在SQL Server2005中按列连接字符串的三种方法
- sql server2005+ 分页查询汇总
- 几种流行的数据库SQL分页
- 几种流行的数据库SQL分页
- 几种流行的数据库SQL分页
- 几种流行的数据库SQL分页
- sql的几种分页查询
- C#常用开源类库
- 浅谈setjmp与longjmp函数
- DropEdit
- java发邮件
- mysql 常用函数
- 浅谈SQL Server2005的几种分页方法
- 新手
- 竟然 能 恢复 过来 了 从 10.10升级到11.4 Ubuntu
- 5月份
- 关于x86、i386、i486、i586和i686等名词的解释
- DeltaOffice word在线编辑控件增加OnIAmReady事件函数
- Gene Shuffle
- 性能测试的步骤流程---DETECT(让性能测试规范化)
- 开始真正进入windows开发之旅,加油~!!!