Paging for SQL 2012

Today I see an article about 2012 of the page, just want to test the new paging method than the original performance of the promotion, the following is my test process (2012 of the paging syntax here is not a long description, MSDN on a search):

Let's start by constructing the test data:

　　

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33

--建表 CREATE TABLE [dbo].[MyCustomer](     [id] [int] PRIMARY KEY,     [CustomerNumber] [varchar](25),     [CustomerName] [varchar](25),     [CustomerCity] [varchar](25) )--生成100W测试数据 DBCC DROPCLEANBUFFERS  DBCC FREEPROCCACHE    SET STATISTICS IO ON;  SET STATISTICS TIME ON;  ;WITH mycte AS (     SELECT id=1,CustomerNumber=CAST(‘0000‘ AS VARCHAR(25)),     CustomerNamer=CAST(‘AAAA‘ AS VARCHAR(25)),     CustomerCity=CAST(‘CCCC‘ AS VARCHAR(25))     UNION ALL     SELECT id=id+1,     CustomerNamer=CAST(CHECKSUM(NEWID()) AS VARCHAR(25)),     CustomerNamer=CAST(‘Name‘+CHAR(65+id%26) AS VARCHAR(25)),     CustomerCity=CAST(CHAR(65+id%26) AS VARCHAR(25)) FROM mycte WHERE id<1000000 ) INSERT INTO MyCustomer SELECT * FROM mycte OPTION(MAXRECURSION 0)SET STATISTICS IO OFF ;  SET STATISTICS TIME OFF;

Here I use the CTE method to recursively construct the data, interested children's shoes can try the traditional while loop to compare the difference between the two. Let's take a look at the common paging statements and their time overhead in the next episode:

1, 2012 of the paging situation

2, 05~08 of the page situation

3, double top n paging case

The above three kinds of paging query scenario is consistent, are query 50W after the 10 records, the total record of 100W, we can see, in this case, 2012 of the paging effect is the best. The other two differences are not very large,

Of course, the more the query on the previous record difference will be smaller, for example, if we reduce the range of data query to 500 after 10, the first 2 of the time consumption has not seen the difference. Interested children's shoes can be compared to the following 3 query statements of the query plan. The query plan selection is proportional to its complexity and the overhead required by the query.