几种高效mssql server sql分页语句

几种高效mssql server sql分页语句

分页方案三：(利用id大于多少和select top分页）效率最高，需要拼接sql语句
分页方案二：(利用not in和select top分页) 效率次之，需要拼接sql语句www.111cn.net
分页方案一：相对于大数据量会有明显的优势的 

看一个简单存储过程实例的

create procedure pr_getarticles —-这里为存储过程名称 @page int , @pagenum int as declare @tablename nvarchar(20) set @tablename='article' —–表名 declare @idname nvarchar(20) set @idname='article_id' —–表id名 declare @strsql nvarchar(4000) declare @topnum int set @topnum=(@page-1)*@pagenum set @strsql=n'select top'+ str(@pagenum)+' * from '+@tablename+' where '+@idname+'> ( select isnull(max('+@idname+'),0) from ( select top '+str( @topnum)+' '+@idname+' from '+@tablename+' order by '+@idname+' ) a ) order by '+@idname+" print (@strsql) exec(@strsql) go

分页方案二：(利用not in和select top分页)
语句形式：

select top 10 *
from testtable
where (id not in
(select top 20 id
from testtable
order by id))
order by id

select top 页大小 *
from testtable
where (id not in
(select top 页大小*页数 id
from 表
order by id))
order by id

------------------------------------- www.111cn.net

Upscale

AI图片放大工具

85

查看详情

分页方案三：(利用id大于多少和select top分页）
语句形式：
select top 10 *
from testtable
where (id >
(select max(id)
from (select top 20 id
from testtable
order by id) as t))
order by id

select top 页大小 *
from testtable
where (id >
(select max(id)
from (select top 页大小*页数 id
from 表
order by id) as t))
order by id

创建分页数据表，同时保存2万条记录

create table [testtable] (
[id] [int] identity (1, 1) not null ,
[firstname] [nvarchar] (100) collate chinese_prc_ci_as null ,
[lastname] [nvarchar] (100) collate chinese_prc_ci_as null ,
[country] [nvarchar] (50) collate chinese_prc_ci_as null ,
[note] [nvarchar] (2000) collate chinese_prc_ci_as null
) on [primary]
go