SQL Server 常用分頁SQL

今天無聊和朋友討論分頁，發現網上好多都是錯的。網上經常查到的那個Top Not in 或者Max 大部分都不實用，很多都忽略了Order和性能問題。為此上網查了查，順帶把2000和2012版本的也補上了。

先說說網上常見SQL的錯誤或者說局限問題

1 2 3 4 5

select?top?10 * from?table1 where?id?not?in( ????select?top?開始的位置 id ????from?table1)

這樣的確是可以取到分頁數據，但是這是默認排序的，如果要按其中一列排序呢？那order by 加在哪里呢？里外都加，顯然不行，外面的Order不起作用，只能嵌套，Oh my god,編程三個Select了，這效率。

為了好用效率高，總體思路還是老老實實的用RowNumber解決，但是SQL2000沒有RowNumber，其實我們可以通過臨時表自增列搞定，不多說，上例子。

?

SQL 2000 用臨時表解決，通過在臨時表中增加自增列解決RowNumber。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

DECLARE?@Start?INT DECLARE?@End?INT SELECT?@Start = 14000,@End?= 14050 CREATE?TABLE?#employees (RowNumber?INT?IDENTITY(1,1), LastName?VARCHAR(100),FirstName?VARCHAR(100), EmailAddress?VARCHAR(100)) INSERT?INTO?#employees (LastName, FirstName, EmailAddress) SELECT?LastName, FirstName, EmailAddress FROM?Employee ORDER?BY?LastName, FirstName, EmailAddress SELECT?LastName, FirstName, EmailAddress FROM?#employees WHERE?RowNumber > @Start?AND?RowNumber <= @End DROP?TABLE?#employees GO

　　

SQL 2005/2008 由于支持了Row_Number于是通過派生表的方式解決（兩個嵌套）

1 2 3 4 5 6 7 8 9 10 11 12

DECLARE?@Start?INT DECLARE?@End?INT SELECT?@Start = 14000,@End?= 14050 SELECT?LastName, FirstName, EmailAddress FROM?(SELECT?LastName, FirstName, EmailAddress, ROW_NUMBER() OVER (ORDER?BY?LastName, FirstName, EmailAddress)?AS?RowNumber FROM?Employee) EmployeePage WHERE?RowNumber > @Start?AND?RowNumber <= @End ORDER?BY?LastName, FirstName, EmailAddress GO

　　

SQL 2005/2008 或者用CTE的方式實現，和派生表一樣，就是好看點，執行計劃都一樣。

1 2 3 4 5 6 7 8 9 10 11 12 13 14

DECLARE?@Start?INT DECLARE?@End?INT SELECT?@Start = 14000,@End?= 14050; WITH?EmployeePage?AS (SELECT?LastName, FirstName, EmailAddress, ROW_NUMBER() OVER (ORDER?BY?LastName, FirstName, EmailAddress)?AS?RowNumber FROM?Employee) SELECT?LastName, FirstName, EmailAddress FROM?EmployeePage WHERE?RowNumber > @Start?AND?RowNumber <= @End ORDER?BY?LastName, FirstName, EmailAddress GO

　　

SQL SERVER 2012 比較給力支持了OFFSET，于是一個Select結束戰斗

1 2 3 4 5

SELECT?LastName, FirstName, EmailAddress FROM?Employee ORDER?BY?LastName, FirstName, EmailAddress OFFSET 14000?ROWS FETCH?NEXT?50?ROWS?ONLY;

　　

?

最后說下，根據老外的文章，在2012里，如果前面加上TOP（50），那么執行計劃就會少讀很多行數據（讀的精準了），提高性能。但是鑒于本人手頭沒2012也無法測試。至少在2008R2上加不加TOP執行計劃都一樣。