SQL Server分页最佳解决方案

SQL Server分页最佳解决方案:
建立表：

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 

 
-------------------------------------

分页方案一：(利用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 

 
-------------------------------------

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

 
其它的方案：如果没有主键，可以用临时表，也可以用方案三做，但是效率会低。
建议优化的时候，加上主键和索引，查询效率会提高。

通过SQL 查询分析器，显示比较：我的结论是:
分页方案二：(利用ID大于多少和SELECT TOP分页）效率最高，需要拼接SQL语句
分页方案一：(利用Not In和SELECT TOP分页)   效率次之，需要拼接SQL语句

在实际情况中，要具体分析。

再看看这个呢SQL Server

从数据库表中的第M条记录开始取N条记录，利用Top关键字：注意如果Select语句中既有top，又有order by，则是从排序好的结果集中选择：

SELECT * 

　　FROM ( SELECT Top N * 

　　FROM (SELECT Top (M + N - 1) * FROM 表名称 Order by 主键 desc) t1 ) t2 

　　Order by 主键 asc 

 
例如从表Sys_option(主键为sys_id)中从10条记录还是检索20条记录，语句如下：
　　

SELECT * 

　　FROM ( SELECT TOP 20 * 

　　FROM (SELECT TOP 29 * FROM Sys_option order by sys_id desc) t1) t2 

　　Order by sys_id asc