SQL Server 2000 使用 ROWCOUNT 和 SQL_VARIANT 进行分页和排序

目的

目的是提供一种有效的方法，用于在 Microsoft SQL Server 2000 数据库上对大型数据集进行排序和分页，而无需使用连接 SQL。主要是，这是一个概念验证作品，您需要进行自己的性能测试以查看它是否符合您的目标。

引言

自从我听说过 SQL 注入攻击，并希望通过每次使用相同的查询来提高查询的性能和可维护性以来，我一直在努力避免连接 SQL。至少，静态 SQL 往往更容易理解——这通常会导致更少的错误。但是，当我尝试实现具有动态排序值的分页解决方案时，我总是遇到问题。

然后我遇到了这篇文章，并注意到使用ROWCOUNT的查询往往比其他查询表现更好。但是，在没有连接的情况下使其适用于动态排序值往往涉及一个临时表，并且在您进入前几页之后，它的性能比使用CURSOR更差。所以我决定必须有一种方法可以做到…

SQL

此存储过程构建为在微软的 Adventure Works 示例数据库上运行。我只是选择了一个行数适中的表。废话不多说，这是 SQL。

CREATE PROCEDURE dbo.up_GetSortedSalesOrdersByPageUsingRowset
    @orderedOnStart datetime,
    @orderedOnEnd datetime,
    @pageNumber int,
    @pageSize int,
    @sortExpression varchar(100),
    @sortOrder varchar(4),
    @virtualCount int OUTPUT
AS

    /*
        Make sure that the page number is at least 1
    */
    IF @pageNumber < 1
    BEGIN
        SET @pageNumber = 1
    END

    SELECT
        @virtualCount = COUNT(*)
    FROM
        Sales.SalesOrderHeader Header
    WHERE
        Header.[OrderDate] >= @orderedOnStart
        AND Header.[OrderDate] < @orderedOnEnd

    DECLARE @lastKeyValue numeric(18,0)
    DECLARE @lastAscendingSortValue SQL_Variant
    DECLARE @lastDescendingSortValue SQL_Variant

    DECLARE @numberToIgnore int

    SET @numberToIgnore = (@pageNumber-1) * @pageSize

    IF @numberToIgnore > 0
    BEGIN
        /*
        Get the last available sort data and unique key
        value from the last page.
        */
        SET ROWCOUNT @numberToIgnore

        SELECT
            @lastKeyValue = [UniqueValue],
            @lastAscendingSortValue = [AscendingSort],
            @lastDescendingSortValue = [DescendingSort]
        FROM
        (
            SELECT
                Header.[SalesOrderID] AS [UniqueValue],
                CASE
                    WHEN
                        UPPER(@sortOrder) = 'DESC'
                        AND UPPER(@sortExpression) = 'CUSTOMERID'
                    THEN
                        CONVERT(SQL_Variant, [CustomerID])
                    WHEN
                        UPPER(@sortOrder) = 'DESC'
                        AND UPPER(@sortExpression) = 'TOTALDUE'
                    THEN
                        CONVERT(SQL_Variant, [TotalDue])
                    WHEN
                        UPPER(@sortOrder) = 'DESC'
                        AND UPPER(@sortExpression) = 'ORDERDATE'
                    THEN
                        CONVERT(SQL_Variant, [OrderDate])
                    ELSE
                        NULL
                END AS [DescendingSort],
                CASE
                    WHEN
                        UPPER(@sortOrder) = 'ASC'
                        AND UPPER(@sortExpression) = 'CUSTOMERID'
                    THEN
                        CONVERT(SQL_Variant, [CustomerID])
                    WHEN
                        UPPER(@sortOrder) = 'ASC'
                        AND UPPER(@sortExpression) = 'TOTALDUE'
                    THEN
                        CONVERT(SQL_Variant, [TotalDue])
                    WHEN
                        UPPER(@sortOrder) = 'ASC'
                        AND UPPER(@sortExpression) = 'ORDERDATE'
                    THEN
                        CONVERT(SQL_Variant, [OrderDate])
                    ELSE
                        NULL
                END AS [AscendingSort]
            FROM
                Sales.SalesOrderHeader Header
            WHERE
                Header.[OrderDate] >= @orderedOnStart
                AND Header.[OrderDate] < @orderedOnEnd
            ) AS Derived
        ORDER BY
            [AscendingSort] ASC,
            [DescendingSort] DESC,
            [UniqueValue] ASC
    END

    /*
    Select the first @pageSize records that come after the last sort
    data/unique value from the last page. If this is the first page,
    just get the first @pageSize records.
    */

    SET ROWCOUNT @pageSize

    SELECT
        [SalesOrderID],
        [OrderDate],
        [TotalDue],
        [CustomerID]
    FROM
    (
        SELECT
            [SalesOrderID],
            [OrderDate],
            [TotalDue],
            [CustomerID],
            [SalesOrderID] As [UniqueValue],
            CASE
                WHEN
                    UPPER(@sortOrder) = 'DESC'
                    AND UPPER(@sortExpression) = 'CUSTOMERID'
                THEN
                    CONVERT(SQL_Variant, [CustomerID])
                WHEN
                    UPPER(@sortOrder) = 'DESC'
                    AND UPPER(@sortExpression) = 'TOTALDUE'
                THEN
                    CONVERT(SQL_Variant, [TotalDue])
                WHEN
                    UPPER(@sortOrder) = 'DESC'
                    AND UPPER(@sortExpression) = 'ORDERDATE'
                THEN
                    CONVERT(SQL_Variant, [OrderDate])
                ELSE
                    NULL
            END AS [DescendingSort],
            CASE
                WHEN
                    UPPER(@sortOrder) = 'ASC'
                    AND UPPER(@sortExpression) = 'CUSTOMERID'
                THEN
                    CONVERT(SQL_Variant, [CustomerID])
                WHEN
                    UPPER(@sortOrder) = 'ASC'
                    AND UPPER(@sortExpression) = 'TOTALDUE'
                THEN
                    CONVERT(SQL_Variant, [TotalDue])
                WHEN
                    UPPER(@sortOrder) = 'ASC'
                    AND UPPER(@sortExpression) = 'ORDERDATE'
                THEN
                    CONVERT(SQL_Variant, [OrderDate])
                ELSE
                    NULL
            END AS [AscendingSort]
        FROM
            Sales.SalesOrderHeader Header
        WHERE
            Header.[OrderDate] >= @orderedOnStart
            AND Header.[OrderDate] < @orderedOnEnd
    ) Derived
    WHERE
    (
        @lastAscendingSortValue IS NULL
        AND @lastDescendingSortValue IS NULL
        AND @lastKeyValue IS NULL
    )
    OR
    (
        (@lastAscendingSortValue IS NOT NULL)
        AND
        (
            ([AscendingSort] > @lastAscendingSortValue)
            OR
            (
                [AscendingSort] = @lastAscendingSortValue
                AND [UniqueValue] > @lastKeyValue
            )
        )
    )
    OR
    (
        (@lastDescendingSortValue IS NOT NULL)
        AND
        (
            ([DescendingSort] < @lastDescendingSortValue)
            OR
            (
                [DescendingSort] = @lastDescendingSortValue
                AND [UniqueValue] > @lastKeyValue
            )
        )
    )
    ORDER BY
        [AscendingSort] ASC,
        [DescendingSort] DESC,
        [SalesOrderID] ASC

    SET ROWCOUNT 0
GO

快速提示

此方法要求您的数据集每行至少有一个唯一值（或值的组合）。我认为这往往是我们使用它们的大部分原因。

解释

它做什么？它获取特定日期范围内且以特定方式排序的销售订单页面。

它如何工作？首先，分页。这在其他地方有更详细的描述，但使用ROWCOUNT背后的想法是您忽略一系列特定的行。所以，如果我在第 4 页，每页有 10 条记录，我想忽略前 40 行。我们怎么做？ROWCOUNT指定将返回的行数，在本例中为 40，我们只需存储有关最后一行（第 40 行）的唯一信息，并强制最后一个select 语句返回第 40 行之后的行。

如前所述，当您添加排序时，这会导致一些困难。对使用此方法的常见回应是，“好吧，如果我硬编码我的排序值，这有效”，或者“好吧，这仅在我的排序值是唯一时才有效。” 所以，输入SQL_VARIANT。SQL_VARIANT很有趣，因为它保留了数据类型，这与 JavaScript 和 Visual Basic 等其他语言中的变体类型类似。所以，我们存储您的排序值和最后一行中的唯一值。拥有这两者很重要，因为如果我想按具有非唯一值的字段（例如[OrderDate]）排序怎么办？为了保证 SQL 中的顺序，我们需要确保在ORDER BY子句中包含有关行的唯一内容。这是使我们摆脱排序值唯一性问题的“魔力”。

但是硬编码的排序值呢？好吧，这就是那些CASE语句的作用。如果您查看，您会看到来自CASE语句的值仅用于ORDER BY。我想在某种意义上，我们已经从硬编码单个排序值转移到硬编码可用的排序值，但这是我愿意接受的。如果您真的需要即席查询，还有其他方法可能更适合您的需求。由于SQL_VARIANT保留了排序值的类型，并且我们正在派生查询中返回我们要排序的列，因此排序工作方式与应该的一样。IS NULL检查在那里是因为如果我们处于第一页，这些值应该为NULL。

就这样吗？

您想要更多吗？！就个人而言，这只是让我拍了自己的脑门，然后想，“当然！ 这就是你做它的方式。” 但是，还有一些事情要做。

• 确实需要在更大的数据集上进行性能测试。如果有人愿意，我很乐意发布结果（当然，会给予应有的荣誉）。我在 SQL Server 2005（因为我拥有这个）上的家里运行了这个过程，它看起来非常快。

无论如何，我希望这能帮助你们中的一些人。编码愉快！