SQL2012 page offset fetch compare sql2005/2008 row_number

Sql2005/2008 's Row_number

Http://www.cnblogs.com/Snowfun/archive/2011/10/10/2205772.html

1.OFFSET and Fetch:
These two keywords are used in MSDN prototypes as shown in code 1

Offset is very simple to use, first specifying which record to start with after offset. Where the number of values can be a constant or it can be a variable or an expression. You then specify how many records to fetch by using the FETCH keyword. Where first and next are synonyms, like the previous row and rows, they can be replaced with each other. Similarly, the number of record bars taken here can also be constants or variable expressions.

ORDER byOrder_by_expression
[COLLATE collation_name]
[ASC | DESC]
[,... N]
[<offset_fetch>]


<Offset_fetch>::=
{
OFFSET {integer_constant|Offset_row_count_expression} {Row|ROWS}
[
FETCH {First | NEXT} {integer_constant | fetch_row_count_expression} {ROW | ROWS} only
]
}

Simple usage of 2.OFFSET and fetch

--Create a table
CREATETABLE[dbo].[Testcolumnstore_tcs](
[tcs_id] [int]IDENTITY(1,1) notNULL,
[Tcs_data][int]NULL
) on[PRIMARY]
--Insert 1 million test data,

--SELECT * from Testcolumnstore_tcs
--Select Floor (RAND (ABS (CHECKSUM (NEWID))) *100)--Get random values

Declare@indexint
Set@index=0
while(@index<1000000)
begin
Insert intoTestcolumnstore_tcs (Tcs_data)Values( Floor(RAND(ABS(CHECKSUM (NEWID())))* -))
Set@index=@index+1
End

Using the offset and FETCH keywords makes paging easier.

--take data from 500,000 to 500020
Select* fromTestcolumnstore_tcsOrder bytcs_id Offset500000RowFetchNext 20Rows only

3..OFFSET ... Improved performance with fetch paging
The offset and FETCH statements are not just syntactic sugars, but can also lead to improved paging efficiency. Let's compare the paging efficiency of SQL Server 2012 and SQL Server 2005/2008 with different paging methods using an example. We also take data between 500,000 and 500100, as shown in the performance comparison.

--SQL2012 Paging Method
Select* fromTestcolumnstore_tcsOrder bytcs_id Offset500000RowFetchNext -Rows only;
--SQL2008, 2005 pagination mode
withCte as(
SelectRow_number () Over(Order byTCS_ID) asCan* fromTESTCOLUMNSTORE_TCS)
Select* fromCtewhereAa>500000 andAa<=500020

: Comparison of paging efficiency between SQL Server 2012 paging and SQL Server 05/08

: In the query plan I see a fetch in SQL Server2012. Next is a very lossy performance.

The paging effect that SQL Server 2012 brings is very powerful, making it much easier to page out the pages under SQL Server. The impact on performance, due to the deviation of the above-mentioned execution plan, will not be concluded

SQL2012 page offset fetch compare sql2005/2008 row_number