介紹
有了SQL Server 2005之后，我們使用分頁查詢比老版本容易多了。在本文中，我將主要用到的是NorthWind數據庫，所以你也可以仿照編寫我所列舉的范例。我盡量使例子簡單化;因為任何復雜的東西都會都會造成混淆。我將從“傳統”的方法著手，比如：SELECT, TOP，然后慢慢進入具體的SQL Server 2005 分頁的例子。
背景
我常常會被問到這樣的一個問題或者說一系列這樣的問題，“你如何在SQL中進行分頁？在有很多個記錄的情況下，你又如何做，比如10000個記錄或者更多呢？
我思索著答案。更確切地說，我考慮了更多的問題并且我都認真去思考，“這必定是一個普篇的問題，每一個開發人員必須處理或者說解決的。具有非常大的數據庫集的工作和分頁大小是怎樣的？從多種表所得到的結果集又是如何？”
因此，我決定具體結合SQL Server 2005來研究這些問題。下面的方法是至今為止最容易的，建議采納。但是這是非常罕見的，并非易事。
select * from mytable
Where ID between20and30
SQL Top
SQL Top（從結果集返回記錄）非常善于從結果集的每個尾部返回大量的記錄。
下面的例子通過命令qty獲得了前10名顧客。在論壇上這是一個非常普騙的問題。盡管TOP也可以拉動一定百分比的記錄，但是我們這里不討論。
selecttop10 * from customers -- This is a very basic example.
selectTOP10 Customers.CustomerID, Customers.CompanyName, count(*) OrderCount
from Customers innerjoin Orders on Orders.CustomerID = Customers.CustomerID
GROUPBY Customers.CustomerID, Customers.CompanyName
ORDERBY OrderCount DESC
這是很有用的。當你要把記錄從11拉到20時，你就可以使用臨時表。
-- SELECT First 30 records in to Temp table
SELECTTOP30 * INTO
#TEMP
from Customers
ORDERBY CompanyName ASC
--Select Bottom 10 records in another temp table
SELECTTOP10 *
INTO #TEMP2
from #Temp
ORDERBY CompanyName DESC
-- GET THE RECORDS
SELECT * FROM #TEMP2
這對少數前幾個頁面或者前幾個用戶是種懲罰。如果擁有這樣的用戶，他們想從一頁一頁之后返回頁面，你將以使1000條記錄返回10條來結束，這是非常低效的。你可以在第一個臨時表中內置一個身份，然后用一個SELECT聲明來作替代。
TOP的替代
有一個這樣的TOP替代，它使用的是rowcount（行計數）。使用行計數要小心。如果它不關閉的話，將你將陷入各種各樣的困境。
SETrowcount10
SELECT * from Customers
ORDERBY CompanyName
WITH, ROW_NUMBER （行數）and OVER
這對SQL Server 2005來說非常新鮮并且看上去非常有用。下面一個例子顯示從一個結果集得到20至19條記錄。剛開始，我有一點驚奇，但是我瀏覽了查詢器后我發現它是如此簡單。
With Cust AS
( SELECT CustomerID, CompanyName,
ROW_NUMBER() OVER (orderby CompanyName) as RowNumber
FROM Customers )
select *
from Cust
Where RowNumber Between20and30
SQL Server 2005的WITH指定了一個臨時命名的結果，很像SQL Server以前版本中的臨時表。但是，輸入部分是ROW_NUMBER和OVER聲明，它根據公司的名稱在每組中創建行數。這就像通過命令條文向臨時表添加一個身份種子。
我希望你贊成我的看法。如果不，運行代碼并查看結果集。對大表來說速度真的非常快;表的速度超過250，000條記錄，對此我留下了深刻的印象。
一起整合到儲存過程中
現在我們把它一起整合到儲存過程，這個儲存過程我們可以通過應用程序來使用。我不打算展示.NET Datagrid或者相似的控件，因為是本文探討范圍之外。下面看到的儲存過程使用了靈活的頁面大小和頁面數目，所以你可以隨意地選擇任何頁面。這樣，如果你想跳過前十頁去尋找某一條記錄就非常方便了。下面的例子是從第一頁開始分頁的，而不是從第0頁，但也可以隨意更改。
CREATEPROC GetCustomersByPage
@PageSize int, @PageNumber int
AS
Declare @RowStart int
Declare @RowEnd int
if @PageNumber > 0
Begin
SET @PageNumber = @PageNumber -1
SET @RowStart = @PageSize * @PageNumber + 1;
SET @RowEnd = @RowStart + @PageSize - 1 ;
With Cust AS
( SELECT CustomerID, CompanyName,
ROW_NUMBER() OVER (orderby CompanyName) as RowNumber
FROM Customers )
select *
from Cust
Where RowNumber >= @RowStart and RowNumber <= @RowEnd end
END
運行這個程序，僅需指定頁面大小和頁數目(GetCustomersByPage, @PageSize and @PageNumber)，代碼如下：
exec GetCustomersByPage 10, 1
MSN:
posted on 2008-01-20 20:21
kylixlu 閱讀(256)
評論(0) 編輯 收藏 所屬分類:
Database