sql-server-2005 - over - row_number() sql server

Consulta paginada utilizando ordenación en columnas diferentes usando ROW_NUMBER() OVER() en SQL Server 2005

Question

Supongamos que estoy usando la base de datos de Northwind y me gustaría ejecutar una consulta a través de un procedimiento almacenado que contiene, entre otros parámetros, lo siguiente:

• @Offset para indicar dónde comienza la paginación
• @Limit para indicar el tamaño de la página,
• @SortColumn para indicar la columna utilizada para fines de clasificación,
• @SortDirection , para indicar la ordenación ascendente o descendiente.

La idea es hacer la paginación en la base de datos, ya que el conjunto de resultados contiene miles de filas, por lo que el almacenamiento en caché no es una opción (y usar VIEWSTATE ni siquiera se considera como, IMO, es una mierda).

Como sabrá, SQL Server 2005 proporciona la función ROW_NUMBER que devuelve el número secuencial de una fila dentro de una partición de un conjunto de resultados, comenzando en 1 para la primera fila en cada partición .

Necesitamos clasificar en cada columna devuelta (cinco en este ejemplo) y el SQL dinámico no es una opción, así que tenemos dos posibilidades: usar mucho IF ... ELSE ... y tener 10 consultas, lo cual es un infierno de mantener, o teniendo una consulta como la siguiente:

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

He intentado la consulta varias veces, con diferentes argumentos, y su rendimiento es bastante bueno en realidad, pero parece que podría ser optimizado de otra manera.

¿Pasa algo con esta consulta o lo harías de esta manera? ¿Propones un enfoque diferente?

Answer 1

Sencillo:

SELECT
  OrderID, CustomerID, EmployeeID, OrderDate, ShippedDate,
  @Offset, @Limit, @SortColumn, @SortDirection
FROM
  Orders
WHERE
  ROW_NUMBER() OVER 
  (
    ORDER BY
      /* same expression as in the ORDER BY of the whole query */
  ) BETWEEN (@PageNum - 1) * @PageSize + 1 AND @PageNum * @PageSize 
  /* AND more conditions ... */
ORDER BY
  CASE WHEN @SortDirection = ''A'' THEN
    CASE @SortColumn 
      WHEN ''OrderID''    THEN OrderID
      WHEN ''CustomerID'' THEN CustomerID
      /* more... */
    END
  END,
  CASE WHEN @SortDirection = ''D'' THEN
    CASE @SortColumn 
      WHEN ''OrderID''    THEN OrderID
      WHEN ''CustomerID'' THEN CustomerID
      /* more... */
    END 
  END DESC

Esto ordenará en NULL (DESC) si se selecciona el orden ASC, o viceversa.

Deje que la función ROW_NUMBER () funcione sobre la misma expresión ORDER BY.