SQL server 2005 I Ranking Functions and TOP operator

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
 

Before SQL server 2005 we can use Top to separate pages too, but it is not easy as the Top in 05. Here we have a problem. We should check if there are 5 columns left for selecting, so we should use if to check it befor selecting. In SQL server 2005  we also could use operator TOP to separate pages. The code is below