数据分页SQL语句的比较

建立表

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

插入数据:(2万条,用更多的数据测试会明显一些)

SET IDENTITY_INSERT TestTable ON 

declare @i int
set @i=1
while @i<=20000
begin
insert into TestTable([id], FirstName, LastName, Country,Note) values(@i, ''FirstName_XXX'',''LastName_XXX'',''Country_XXX'',''Note_XXX'')
set @i=@i+1
end SET IDENTITY_INSERT TestTable OFF

分页方案一:(利用SqlServer2005中的新特性ROW_NUMBER进行分页)

Select * FROM (
select ROW_NUMBER()Over(order by id desc) as rowId,* from TestTable
) as mytable
where rowId between 21 and 40

说明:效率高,但存在版本兼容性问题,只兼容SQL SERVER2005及以上版本

分页方案二:(利用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

说明:效率次之,根据主键ID分页,也可根据其他排序字段分页。与方案三相比较,在处理1000条以下数据时,效率优于方案三。但处理大数据时次于方案三。  

分页方案三:(利用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

说明:效率次之,自能根据主键ID分页,也可根据其他排序字段分页(将id替换为排序字段名),在1000条以上数据检索时,效率高于方案二

分页方案四:无主键表,使用临时表分页

备注:SQL Server 查看查询时间,依次点击查询->查询选项->高级->选中“SET STATISTICS TIME”->确定。在“消息”中查看查询时间

上一篇:javascript 学习总结(五)Function对象


下一篇:Failed to decode downloaded font