sql code
功能：生成测试数据.
create table Test_paging(
id int identity(1,1) not null primary key,
TestNumber int not null,
TestName varchar(20) not null,
TestDept varchar(10) not null,
TestDate datetime not null
with tep(Number,Name,Dept,Date) as
select 1,cast('0_testname' as varchar(20)),cast('0_DBA' as varchar(10)),getdate()
union all
select Number+1,cast(cast(Number as varchar(20))+'_testname' as varchar(20)),cast(cast(Number/500 as varchar(10))+'_DBA' as varchar(10)) ,getdate()
from tep
where Number<=20000000
insert into Test_paging(TestNumber,TestName,TestDept,TestDate)
select Number,Name,Dept,Date from tep option(maxrecursion 0)
--添加索引（我有测试没有索引的情况，2012的优势更加明显，但是我们的数据库不可能不建索引的，故可忽略没有索引的情况）
create nonclustered index IX_TestDept on Test_paging(
TestDept
) include
TestName,TestDate
@total int --总行数
select @page=20,@size=10,@total=count(1) from Test_paging where TestDept = '1000_DBA'
select
TestName,TestDept,TestDate,@total
Test_paging
where
TestDept = '1000_DBA'
order by id offset (@page-1)*@size rows fetch next @size rows only
set statistics io off
set statistics time off
set statistics profile off
@total int
select @page=20,@size=10,@total=count(1) from Test_paging where TestDept = '1000_DBA'
select TestName,TestDept,TestDate,@total from
select
TestName,TestDept,TestDate,row_number() over(order by ID) as num
Test_paging
where
TestDept = '1000_DBA'
) test where num between (@page-1)*@size+1 and @page*@size order by num
set statistics io off
set statistics time off
set statistics profile off
SQL code
功能：测试2000版本中top分页.
dbcc dropcleanbuffers
dbcc freeproccache
set statistics io on
set statistics time on
set statistics profile on
declare
@page int, --第@page页
@size int, --每页@size行
@total int --总行数
select @page=20,@size=10,@total=count(1) from Test_paging where TestDept = '1000_DBA'
select TestName,TestDept,TestDate,@total from
select top(@size) id,TestName,TestDept,TestDate from
select top(@page*@size) id,TestName,TestDept,TestDate
from Test_paging
where TestDept = '1000_DBA'
order by id
)temp1 order by id desc
)temp2 order by id
set statistics io off
set statistics time off
set statistics profile off
原文：http://bbs.csdn.net/topics/390941777
