本文著重介紹SQL Server 2005新增加的ROW-NUMBER排序函數，它會根據你指定的分類標准將結果數據集進行分類，同時給數據集分配連續的頁面，供大家參考！
分頁，就是按照某種規則顯示分組數據集，但是在SQL Server 中，分頁並不是十分容易就能夠實現。在過去，開發人員通常需要自己編寫程序，使用臨時表格來實現分頁功能，或者將所有的數據結果集返回到客戶端，在客戶端進行分頁操作。從開發人員或者DBA的角度來看，兩種方法都不能令人滿意。
隨著SQL Server的發布，其中的一些排序函數使得開發人員編寫數據分頁程序變得更加簡單和高效。這些新的排序函數提供了統計數據集的數目，對數據集歸類，按照某種標准對數據集排序等功能。在這篇文章中，我將著重介紹新增加的ROW-NUMBER排序函數，它會根據你指定的分類標准將結果數據集進行分類，同時給數據集分配連續的頁面。
一個分頁的實例
我總是喜歡通過例子來介紹如何使用新技術，所以讓我們來看看如何設計一個存儲程序，使用ROW_NUMBER這一新函數來實現數據的自動分頁。
首先，需要定義一些數據結構。我們定義一個SalesHistory表格，它包含的數據是我們在網上售出產品的銷售記錄。包括一些常見的銷售信息，例如，所售產品、售出日期、產品售出價格等。下面的腳本就是創建這樣的一個表格：
CODE：
IF OBJECT_ID('SalesHistory','U') > 0
DROP TABLE SalesHistory
CREATE TABLE SalesHistory
(
SaleID INT IDENTITY(1,1),
Product VARCHAR(30),
SaleDate SMALLDATETIME,
SalePrice MONEY
)
運行列表A中的腳本則在上面創建的SalesHistory表中添加一些例子數據。
CODE：
列表A
DECLARE @i SMALLINT
SET @i = 1
WHILE (@i <=100)
BEGIN
INSERT INTO SalesHistory
(Product, SaleDate, SalePrice)
VALUES
('Computer', DATEADD(mm, @i, '3/11/1919'), DATEPART(ms, GETDATE()) + (@i + 57) )
INSERT INTO SalesHistory
(Product, SaleDate, SalePrice)
VALUES
('BigScreen', DATEADD(mm, @i, '3/11/1927'), DATEPART(ms, GETDATE()) + (@i + 13) )
INSERT INTO SalesHistory
(Product, SaleDate, SalePrice)
VALUES
('PoolTable', DATEADD(mm, @i, '3/11/1908'), DATEPART(ms, GETDATE()) + (@i + 29) )
SET @i = @i + 1
END
CODE：
列表B
CREATE PROCEDURE usp_SalesRecords
(
@PageSize FLOAT,
@TargetPage SMALLINT
)
AS
BEGIN
WITH Sales_CTE(PageNumber, SaleID, Product, SaleDate, SalePrice)
AS
(
SELECT
CEILING((ROW_NUMBER() OVER (ORDER BY SaleDate ASC))/@PageSize) AS PageNumber,
SaleID, Product, SaleDate, SalePrice
FROM SalesHistory FROM SalesHistory
)
SELECT
PageNumber, SaleID, Product, SaleDate, SalePrice
FROM
Sales_CTE
WHERE
PageNumber = @Targetpage
ENDCREATE PROCEDURE usp_SalesRecords
(
@PageSize FLOAT,
@TargetPage SMALLINT
)
AS
BEGIN
WITH Sales_CTE(PageNumber, SaleID, Product, SaleDate, SalePrice)
AS
(
SELECT
CEILING((ROW_NUMBER() OVER (ORDER BY SaleDate ASC))/@PageSize) AS PageNumber,
SaleID, Product, SaleDate, SalePrice
FROM SalesHistory FROM SalesHistory
)
SELECT
PageNumber, SaleID, Product, SaleDate, SalePrice
FROM
Sales_CTE
WHERE
PageNumber = @Targetpage
END
如果你剛剛開始使用SQL Server，可能會不熟悉以“WITH”開頭的聲明語句。這條語句會調用SQL Server中的一個新屬性，我們稱之為common table expression（CTE），從本質上來說，我們可以將CTE看作是高版本的臨時表。
分頁的實質就是CTE中的TSQL語句。在下面的選擇語句中，我使用了一個新的排序函數——ROW_NUMBER（這一函數很容易使用，你只需要給ROW_NUMBER函數提供一個域名作為參數，ROW_NUMBER會用它來進行分頁）。隨後，我使用@PageSize參數來劃分每頁的行數以及每頁的最大行數值。
例如，假設現在有一個包含三條記錄的數據集，並設計每頁顯示兩條記錄，那麼頭兩條記錄將會在第一頁顯示，因為每頁的行數必須小於或者等於第一個變量值。第三條記錄將會在第二頁顯示，因為每頁的可顯示最大行數值應該小於2但是又大於1。
可以使用下面的腳本調用存儲程序：
CODE：
EXECUTE usp_SalesRecords
@PageSize = 3,
@TargetPage = 2
執行程序後的返回結果如下：
圖1
就如你所看到的，程序執行後將會返回一頁的數據，包含三條記錄，而且返回的是第二頁的數據集。
需要注意的一點
一般來說，有兩種方法完成數據結果的分頁：在數據庫層實現和不在數據庫層實現。可以在客戶端實現分頁，但是這樣做的時候，所有的數據都會返回到客戶端，而且在進行數據分析的時候就決定了頁面數目。在早期版本的SQL Server中，可以在數據庫層實現分頁，但是需要臨時表和表變量。如果上面的例子沒有使用CTE來進行分頁的話，分頁程序就不會那麼簡單。之所以這麼簡單就是因為使用了ROW_NUMBER函數的強大功能。在以後的文章中，我將會大概介紹一下其它三種排列函數，並展示它們提供的一些靈活易用的函數功能。
