Sql Paging

以NorthWind为例:

(1)两次排序

CREATE PROC dbo.GetCategories
@PageSize int,
@PageNum int
AS
DECLARE @Ignore int
DECLARE @LastID int

IF @PageNum > 1
	BEGIN
		SET @Ignore = @PageSize * ( @PageNum -1 )
		SET ROWCOUNT @Ignore
		SELECT @LastID = CategoryID
		FROM dbo.Categories
		ORDER BY CategoryID DESC
	END
ELSE
	BEGIN
		SET ROWCOUNT @PageSize
	END

SET ROWCOUNT @PageSize
	SELECT * 
	FROM dbo.Categories
	WHERE CategoryID < @LastID
	ORDER BY CategoryID DESC

SET ROWCOUNT 0

(2)临时表或者表变量的自动增长列

ALTER PROCEDURE uspOrderDetailsList
(
     @Page          INT = 1
     ,@PageSize     INT= 10
)
AS
     SET NOCOUNT ON

     -- create a memory-variable table to hold order ids
     DECLARE @TempTable TABLE (OrderID INT, ProductID INT, RowNum INT identity)

     -- insert the table ids and row numbers into the memory table
     INSERT INTO @TempTable
     (
          OrderID
          ,
	ProductID
     )
     SELECT
          OrderID
          ,EmployeeID
     FROM dbo.Orders
     ORDER BY OrderID, EmployeeID

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

     -- select only those rows belonging to the proper page
     SELECT 
          d.OrderID
          ,d.EmployeeID
     FROM dbo.Orders d
          INNER JOIN @TempTable t ON d.OrderID = t.OrderID AND d.EmployeeID = t.ProductID
     WHERE t.RowNum BETWEEN ((@Page - 1) * @PageSize + 1) AND (@Page * @PageSize)

--------------------------------------------------------------------------------------------MemberShip中获取用户分页

CREATE PROCEDURE dbo.aspnet_Membership_GetAllUsers
    @ApplicationName       nvarchar(256),
    @PageIndex             int,
    @PageSize              int
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN 0


    -- Set the page bounds
    DECLARE @PageLowerBound int
    DECLARE @PageUpperBound int
    DECLARE @TotalRecords   int
    SET @PageLowerBound = @PageSize * @PageIndex
    SET @PageUpperBound = @PageSize - 1 + @PageLowerBound

    -- Create a temp table TO store the select results
    CREATE TABLE #PageIndexForUsers
    (
        IndexId int IDENTITY (0, 1) NOT NULL,
        UserId uniqueidentifier
    )

    -- Insert into our temp table
    INSERT INTO #PageIndexForUsers (UserId)
    SELECT u.UserId
    FROM   dbo.aspnet_Membership m, dbo.aspnet_Users u
    WHERE  u.ApplicationId = @ApplicationId AND u.UserId = m.UserId
    ORDER BY u.UserName

    SELECT @TotalRecords = @@ROWCOUNT

    SELECT u.UserName, m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
            m.CreateDate,
            m.LastLoginDate,
            u.LastActivityDate,
            m.LastPasswordChangedDate,
            u.UserId, m.IsLockedOut,
            m.LastLockoutDate
    FROM   dbo.aspnet_Membership m, dbo.aspnet_Users u, #PageIndexForUsers p
    WHERE  u.UserId = p.UserId AND u.UserId = m.UserId AND
           p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
    ORDER BY u.UserName
    RETURN @TotalRecords
END
GO