Vamos supor que estou usando o banco de dados Northwind e gostaria de executar uma consulta por meio de um procedimento armazenado que contenha, entre outros parâmetros, o seguinte:

@Offset para indicar onde a paginação começa,@Limit para indicar o tamanho da página,@SortColumn para indicar a coluna usada para fins de classificação,@SortDirection, para indicar a classificação ascendente ou descendente.

A idéia é fazer a paginação no banco de dados, pois o conjunto de resultados contém milhares de linhas, portanto o armazenamento em cache não é uma opção (e usar VIEWSTATE nem sequer é considerado IMO, é uma porcaria).

Como você deve saber, o SQL Server 2005 fornece a funçãoROW_NUMBER qualretorna o número seqüencial de uma linha dentro de uma partição de um conjunto de resultados, iniciando em 1 para a primeira linha em cada partição.

Precisamos classificar todas as colunas retornadas (cinco neste exemplo) e o SQL dinâmico não é uma opção; portanto, temos duas possibilidades: usar muitasIF ... ELSE ... e ter 10 consultas, o que é um inferno para manter, ou ter uma consulta como a seguinte:

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

Eu tentei a consulta várias vezes, com argumentos diferentes, e seu desempenho é bastante bom, na verdade, mas parece que pode ser otimizado de outra maneira.

Há algo de errado com esta consulta ou você faria dessa maneira? Você propõe uma abordagem diferente?