Предположим, я использую базу данных Northwind и хочу выполнить запрос с помощью хранимой процедуры, которая содержит, среди прочего, следующее:

@Offset чтобы указать, где начинается нумерация страниц,@Limit указать размер страницы,@SortColumn указать столбец, используемый для сортировки,@SortDirection, чтобы указать сортировку по возрастанию или потомку.

Идея состоит в том, чтобы сделать нумерацию страниц в базе данных, так как результирующий набор содержит тысячи строк, поэтому кэширование не является опцией (а использование VIEWSTATE даже не рассматривается как IMO, отстой).

Как вы знаете, SQL Server 2005 предоставляет функциюROW_NUMBER которыйвозвращает порядковый номер строки в разделе набора результатов, начиная с 1 для первой строки в каждом разделе.

Нам нужна сортировка по каждому возвращаемому столбцу (пять в этом примере), и динамический SQL не вариант, поэтому у нас есть две возможности: использовать множествоIF ... ELSE ... и иметь 10 запросов, что является адским обслуживанием, или иметь запрос, подобный следующему:

WITH PaginatedOrders AS (
    SELECT
        CASE (@SortColumn + ':' + @SortDirection)
            WHEN 'OrderID:A' THEN ROW_NUMBER() OVER (ORDER BY Orders.OrderID ASC)
            WHEN 'OrderID:D' THEN ROW_NUMBER() OVER (ORDER BY Orders.OrderID DESC)
            WHEN 'CustomerID:A' THEN ROW_NUMBER() OVER (ORDER BY Orders.CustomerID ASC)
            WHEN 'CustomerID:D' THEN ROW_NUMBER() OVER (ORDER BY Orders.CustomerID DESC)
            WHEN 'EmployeeID:A' THEN ROW_NUMBER() OVER (ORDER BY Orders.EmployeeID ASC)
            WHEN 'EmployeeID:D' THEN ROW_NUMBER() OVER (ORDER BY Orders.EmployeeID DESC)
            WHEN 'OrderDate:A' THEN ROW_NUMBER() OVER (ORDER BY Orders.OrderDate ASC)
            WHEN 'OrderDate:D' THEN ROW_NUMBER() OVER (ORDER BY Orders.OrderDate DESC)
            WHEN 'ShippedDate:A' THEN ROW_NUMBER() OVER (ORDER BY Orders.OrderID ASC)
            WHEN 'ShippedDate:D' THEN ROW_NUMBER() OVER (ORDER BY Orders.OrderID DESC)
        END AS RowNumber,
        OrderID, CustomerID, EmployeeID, OrderDate, ShippedDate
    FROM Orders
    -- WHERE clause goes here
)
SELECT
    RowNumber, OrderID, CustomerID, EmployeeID, OrderDate, ShippedDate,
    @Offset, @Limit, @SortColumn, @SortDirection
FROM PaginatedOrders
WHERE RowNumber BETWEEN @Offset AND (@Offset + @Limit - 1)
ORDER BY RowNumber

Я пробовал запрос несколько раз, с разными аргументами, и его производительность на самом деле довольно хорошая, но он все равно выглядит так, как будто его можно оптимизировать другим способом.

Что-то не так с этим запросом, или вы бы сделали это так? Вы предлагаете другой подход?