SQL Server Paging Summary-excerpt

sqlserver2008 does not support the keyword limit, so its paged SQL query statements will not be in the way of MySQL, fortunately sqlserver2008 provides the top,rownumber and other keywords, so that can be achieved through the several key words pagination. Here are some of the types of query scripts I have read online: Several sqlserver2008 efficient paging SQL query top scenarios: SQL code:SelectTopTen* fromtable1whereID notinch(SelectPosition ID of top start fromtable1) max:sql code:SelectTopTen* fromtable1whereId> (Selectmax (ID) from(SelectTop Start Position ID fromtable1 ORDER by ID) TT) ROW:SQL code:Select* from (SelectRow_number () over (order by Tempcolumn) temprownumber,* from(SelectTop start Position +Tentempcolumn=0,* fromtable1) t) TTwhereTemprownumber>starting position 3 pagination, respectively, Max scheme, top scheme, row scheme efficiency: section 1:row 2:max 3:top disadvantage: Max: You must write complex SQL, do not support non-unique column sort top: User must write Complex SQL, Composite primary key not supported row: sqlserver2000 test Data: A total of 3.2 million data, 10 data per page, 20,000 pages, 150,000 pages and 320,000 pages were tested. Page number, top scheme, max scheme, row scheme 20,000, 60MS,46MS,33MS15, 453ms,343ms,310ms32, 953ms,720ms,686ms is a paging scheme for splicing SQL statements through programs, User-mentioned SQL statements do not need to write complex SQL logic user provides SQL code as followsSelect* fromtable1 starting with 5th, Query 5, post-processing SQL into SQL codeSelect* from (SelectRow_number () over (order by Tempcolumn) temprownumber,* from(SelectTopTentempcolumn=0,* fromtable1) t) TTwhereTemprownumber>5What does that mean? Break it up. First change the user input SQL statement to a slightly modified after select Add top start position+the number turns into a column of tempcolum. SQL codeSelectTop -tempcolumn=0,* fromclazz nested layer, so you can query the travel number that column was used here for order by (and I don't know why SQL Server's Row_number function must order by).SelectRow_number () over (order by Tempcolumn) temprownumber,* from(modified query) t set another layer, filter out line numbers less than the beginning of the row SQL codeSelect* from(second layer) TTwhereTemprownumber>Ten

SQL Server Paging Summary-excerpt