The Database Engine in SQL server 2005 introduces a new category of functions called ranking functions. The functions that make up this category are:
- RANK:
Returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question.
- DENSE_RANK:
Returns the rank of rows within the partition of a result set, without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question.
- NTILE:
Distributes the rows in an ordered partition into a specified number of groups. The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs.
- ROW_NUMBER:
Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.
These functions return a ranking value for each row in a partition. Here is a example. It select PostalCode from Suppliers form Northwind database.
SELECT PostalCode
,ROW_NUMBER() OVER (ORDER BY PostalCode) AS 'Row Number'
,RANK() OVER (ORDER BY PostalCode) AS 'Rank'
,DENSE_RANK() OVER (ORDER BY PostalCode) AS 'Dense Rank'
,NTILE(4) OVER (ORDER BY PostalCode) AS 'Quartile'
FROM Suppliers
We also can use the function ROW_NUMBER to get a part of a table. This is very useful for page separation in ASP.NET. The efficiency is good.
CREATE PROCEDURE GetNextPage
@PageIndex INT,
@PageSize INT,
@RecordCount INT OUT,
@PageCount INT OUT
AS
SELECT @RecordCount = COUNT(*) FROM Packages
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)
/* SQL SERVER 2005 */
SELECT SerialNumber,PackageID,PackageName,NumberOfSeats,Cost FROM
(SELECT PackageID,PackageName,NumberOfSeats,Cost,ROW_NUMBER() OVER (ORDER BY PackageID DESC) AS SerialNumber FROM Packages ) AS T
WHERE T.SerialNumber > (@PageIndex * @PageSize) and T.SerialNumber <= ((@PageIndex+1) * @PageSize)
SELECT TOP 5 * FROM Packages WHERE PackageID > @PackageID ORDER BY PackageID
CREATE PROCEDURE GetNextPage00
@PageIndex INT,
@PageSize INT,
@RecordCount INT OUT,
@PageCount INT OUT
AS
SELECT @RecordCount = COUNT(*) FROM Packages
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)
DECLARE @TOPCOUNT INT
SET @TOPCOUNT = @RecordCount - @PageSize * @PageIndex
/*基于SQL SERVER 2005 */
IF @PageIndex = 0 OR @PageCount <= 1
BEGIN
SELECT TOP(@PageSize) PackageID,PackageName,NumberOfSeats,Cost FROM Packages ORDER BY PackageID ASC
END
ELSE
BEGIN
IF @PageIndex = @PageCount - 1
BEGIN
SELECT * FROM (SELECT TOP(@TOPCOUNT) PackageID,PackageName,NumberOfSeats,Cost FROM Packages ORDER BY PackageID DESC) T
ORDER BY PackageID ASC
END
ELSE
BEGIN
SELECT TOP(@PageSize) * FROM (SELECT TOP(@TOPCOUNT) PackageID,PackageName,NumberOfSeats,Cost FROM Packages ORDER BY PackageID DESC) T
ORDER BY PackageID ASC
END
END
IF @PageIndex = 0 OR @PageCount <= 1
BEGIN
SET @SQLSTR =N'SELECT TOP '+STR(@PageSize)+
'ProductID,Name FROM Production.Product ORDER BY ProductID DESC'
END
ELSE
BEGIN
IF @PageIndex = @PageCount - 1
BEGIN
SET @SQLSTR =N'SELECT * FROM ( SELECT TOP ' + STR(@TOPCOUNT) +
'ProductID,Name FROM Production.Product ORDER BY ProductID ASC) T ORDER BY ProductID DESC'
END
ELSE
BEGIN
SET @SQLSTR =N' SELECT TOP '+STR(@PageSize)+'* FROM (SELECT TOP ' + STR(@TOPCOUNT) +
'ProductID,Name FROM Production.Product ORDER BY ProductID ASC) T ORDER BY ProductID DESC'
END
END