SQLServer2000 to SQLServer2012 three ways to compare pages

SQLSERVER2012 out new paging Function!!!
In the last two days I did the paging performance comparison in SQL Server 2000/(2005/2008)/20,123 versions on my own machine PC (no concurrency, single thread).

The following results can be broadly obtained:
1, table data volume within 200W: SQLServer2012 Offset/fetch Paging performance and SQLServer2005 Row_number paging performance (only considering the result speed) basically no difference (difficult to compete), slightly above (about 10% ) SQL2000 top paging performance.
2, table data volume of about 2000W: SQLServer2012 Offset/fetch Paging performance is slightly higher than SQLServer2005 row_number paging performance, mainly reflected in the IO, but the performance of the two is far more than (about 25% ) SQL2000 top paging performance.
3. Implementation plan 2012:2005 simple, 2005:2000 simple, easy to learn, 2012 the most easy to achieve.
Here is my test script, and I'm interested to try it myself.
Test environment:
Microsoft SQL Server 2014-12.0.2000.8 (X64)
Feb 20 2014 20:04:26
Copyright (c) Microsoft Corporation

Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601:service Pack 1)

/*
Function: Generate test data.
*/
CREATE TABLE Test_paging (
ID int Identity (+) is not null primary key,
Testnumber int NOT NULL,
TestName varchar () NOT NULL,
Testdept varchar (TEN) is not NULL,
TestDate datetime NOT NULL
)
Go

With Tep (number,name,dept,date) as
(
Select 1,cast (' 0_testname ' as varchar), cast (' 0_dba ' as varchar), GETDATE ()
UNION ALL
Select Number+1,cast (CAST (number as varchar) + ' _testname ' as varchar), cast (CAST (number/500 as varchar (10)) + ' _ DBA ' as varchar ', GETDATE ()
From Tep
where number<=20000000
)
Insert into test_paging (testnumber,testname,testdept,testdate)
Select Number,name,dept,date from tep option (maxrecursion 0)

--Add index (I have test without index, the advantage of 2012 is more obvious, but our database cannot be indexed, so we can ignore the case without index)

Create nonclustered index ix_testdept on test_paging (
Testdept
) include
(
Testname,testdate
)
Go

/*
Feature: Test offset/fetch pagination in version 2012.
*/
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

SET STATISTICS IO on
SET STATISTICS TIME on
Set Statistics PROFILE on

DECLARE
@page int,  --@page page
@size int,  --@size per page
@total int   --Total number of rows

Select @page =20, @size =10, @total =count (1) from test_paging where testdept = ' 1000_dba '  

select 
Testname,testdept,testdate, @total
from 
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

/*
Function: Test row_number pagination in version 2005/2008.
*/

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

SET STATISTICS IO on
SET STATISTICS TIME on
Set Statistics PROFILE on

DECLARE
@page int,  --@page page
@size int,  --per page
@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 
from 
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 ProFi Le off

/*
Features: Test top paging in version 2000.
*/
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

SET STATISTICS IO on
Set STATISTICS TIME on
Set STATISTICS PROFILE on

Declare
@page int,--page @page
@size int,--@size rows per page
@total INT--Total row count

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

SQLServer2000 to SQLServer2012 three ways to compare pages