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

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

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

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

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

DECLARE @Start INTDECLARE @End INTSELECT @Start = 14000,@End = 14050CREATE 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, EmailAddressSELECT LastName, FirstName, EmailAddress FROM #employeesWHERE RowNumber > @Start AND RowNumber <= @EndDROP TABLE #employeesGO

DECLARE @Start INTDECLARE @End INTSELECT @Start = 14000,@End = 14050SELECT LastName, FirstName, EmailAddressFROM (SELECT LastName, FirstName, EmailAddress,ROW_NUMBER() OVER (ORDER BY LastName, FirstName, EmailAddress) AS RowNumberFROM Employee) EmployeePageWHERE RowNumber > @Start AND RowNumber <= @EndORDER BY LastName, FirstName, EmailAddressGO

DECLARE @Start INTDECLARE @End INTSELECT @Start = 14000,@End = 14050;WITH EmployeePage AS(SELECT LastName, FirstName, EmailAddress,ROW_NUMBER() OVER (ORDER BY LastName, FirstName, EmailAddress) AS RowNumberFROM Employee)SELECT LastName, FirstName, EmailAddressFROM EmployeePageWHERE RowNumber > @Start AND RowNumber <= @EndORDER BY LastName, FirstName, EmailAddressGO

SELECT LastName, FirstName, EmailAddressFROM EmployeeORDER BY LastName, FirstName, EmailAddressOFFSET 14000 ROWSFETCH NEXT 50 ROWS ONLY;

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