T-sql中的三种分页查询

USE [APS_Future_FT]
GO
/****** Object:  StoredProcedure [dbo].[A_PagingAndSorting]    Script Date: 2013/11/7 21:42:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,数据库分页查询,>
-- =============================================
ALTER PROCEDURE [dbo].[A_PagingAndSorting]
    (
      @PageSize INT ,
      @PageIndex INT   --从1开始
    )
AS 
    BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
        SET NOCOUNT ON;

----------------------------------------------第一种方法：借助row_number()函数-------------------------------
        SELECT  A.ID ,
                A.NAME ,
                A.Age
        FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY t.ID ASC ) AS RowNumber ,
                            t.ID ,
                            t.NAME ,
                            t.Age
                  FROM      dbo.Test t
                ) AS A
        WHERE   A.RowNumber BETWEEN ( @PageIndex - 1 ) * @PageSize + 1
                            AND     @PageIndex * @PageSize

--------------------------------------------第二中方法：借助表变量-------------------------------------
        DECLARE @T TABLE
            (
              ID INT IDENTITY(1, 1) ,
              nid INT
            )   

        INSERT  @T
                ( nid )
                SELECT  Te.ID
                FROM    dbo.Test Te


        SELECT  Te.ID ,
                Te.NAME ,
                Te.Age
        FROM    @T T
                INNER JOIN dbo.Test Te ON Te.ID = T.nid
        WHERE   T.ID BETWEEN ( @PageIndex - 1 ) * @PageSize + 1
                     AND     @PageIndex * @PageSize

--------------------------------------------第三种方法：top---------------------------------------
        SELECT  *
        FROM    ( SELECT TOP ( @PageSize )
                            *
                  FROM      ( SELECT TOP ( @PageIndex * @PageSize )
                                        *
                              FROM      dbo.Test A
                              ORDER BY  A.ID ASC
                            ) B
                  ORDER BY  B.ID DESC
                ) C
        ORDER BY c.ID 
    END