Listing 1: IDENTITY

DECLARE @OrderedRows TABLE
    (
        Product    VARCHAR(50)
        ,Category  VARCHAR(50)
        ,Price     MONEY
        ,RowNum    INT IDENTITY(1,1)
    )


Listing 2: Ranking Function Syntax

RANK()
OVER (
        [PARTITION BY expr, ... [n]]
        ORDER BY {
                    order_by_expression
                    [COLLATE collation_name]
                    [ASC | DESC]
                 } [,...n]]
     )

DENSE_RANK()
OVER (
        [PARTITION BY expr, ... [n]]
        ORDER BY {
                    order_by_expression
                    [COLLATE collation_name]
                    [ASC | DESC]
                 } [,...n]]
     )

ROW_NUMBER()
OVER (
        [PARTITION BY expr, ... [n]]
        ORDER BY {
                    order_by_expression
                    [COLLATE collation_name]
                    [ASC | DESC]
                 } [,...n]]
     )

NTILE(integer expression)
OVER (
        [PARTITION BY expr, ... [n]]
        ORDER BY {
                    order_by_expression
                    [COLLATE collation_name]
                    [ASC | DESC]
                 } [,...n]]
     )


Listing 3: RANK

SELECT
    p.Name       AS 'Product'
    ,sc.Name     AS 'Category'
    ,p.ListPrice AS 'Price'
    ,RANK()
        OVER(
            PARTITION BY sc.Name
            ORDER BY p.ListPrice
        )        AS 'Rank'
FROM Production.ProductSubCategory sc
    INNER JOIN Production.Product p
        ON sc.ProductSubCategoryID =
           p.ProductSubCategoryID
WHERE sc.ProductSubCategoryID IN (4,20)


Listing 4: DENSE_RANK

SELECT
    p.Name       AS 'Product'
    ,sc.Name     AS 'Category'
    ,p.ListPrice AS 'Price'
    ,DENSE_RANK()
        OVER(
            PARTITION BY sc.Name
            ORDER BY p.ListPrice
    )            AS 'Rank'
FROM Production.ProductSubCategory sc
    INNER JOIN Production.Product p
        ON sc.ProductSubCategoryID =
           p.ProductSubCategoryID
WHERE sc.ProductSubCategoryID IN (4,20)


Listing 5: ROW_NUMBER

SELECT
    p.Name       AS 'Product'
    ,sc.Name     AS 'Category'
    ,p.ListPrice AS 'Price'
    ,ROW_NUMBER()
        OVER(
            PARTITION BY sc.Name
            ORDER BY p.ListPrice
    )            AS 'Row'
FROM Production.ProductSubCategory sc
    INNER JOIN Production.Product p
        ON sc.ProductSubCategoryID =
           p.ProductSubCategoryID
WHERE sc.ProductSubCategoryID IN (4,20)


Listing 6: NTILE

SELECT
    p.Name       AS 'Product'
    ,sc.Name     AS 'Category'
    ,p.ListPrice AS 'Price'
    ,NTILE(3)
        OVER(
            PARTITION BY sc.Name
            ORDER BY p.ListPrice
    )            AS 'Group'
FROM Production.ProductSubCategory sc
    INNER JOIN Production.Product p
        ON sc.ProductSubCategoryID =
           p.ProductSubCategoryID
WHERE sc.ProductSubCategoryID IN (4,20)


Listing 7: Paging with ROW_NUMBER

CREATE PROCEDURE uspProductList
(
    @Page         INT = 1
    ,@PageSize    INT = 10
)
AS
    SET NOCOUNT ON

    -- return paging info
    SELECT
        (@Page - 1) *
            @PageSize + 1     AS 'Start'
        ,@Page * @PageSize    AS 'End'
        ,(SELECT COUNT(*)
          FROM Production.Product
        )                     AS 'Total';

    -- select only those rows
    -- belonging to the proper page
    WITH OrderedRows AS
    (
        SELECT
            ROW_NUMBER()
                OVER(
                    ORDER BY ProductID
            )                 AS 'RowNum'
            ,ProductNumber
            ,[Name]           AS 'Product'
            ,ListPrice
        FROM Production.Product
	 )
    SELECT
        ProductNumber
        ,Product
        ,ListPrice
    FROM OrderedRows
    WHERE RowNum BETWEEN
          ((@Page - 1) * @PageSize + 1)
          AND
          (@Page * @PageSize)



Listing 8: Page 1, 10 Rows

EXEC [dbo].[uspProductList]
		@Page = 1,
		@PageSize = 10



Listing 9: Page 2, 10 Rows

EXEC [dbo].[uspProductList]
		@Page = 2,
		@PageSize = 10



Listing 10: Page 1, 20 Rows

EXEC [dbo].[uspProductList]
		@Page = 1,
		@PageSize = 20