Paginated query using sorting on different columns using ROW_NUMBER() OVER () in SQL Server 2005

Question

Let's suppose I'm using the Northwind database and I would like to run a query via a stored procedure that contains, among other parameters, the following:

• @Offset to indicate where the pagination starts,
• @Limit to indicate the page size,
• @SortColumn to indicate the column used for sorting purposes,
• @SortDirection, to indicate ascendant or descendant sorting.

The idea is to do the pagination on the database, as the result set contains thousands of rows so caching is not an option (and using VIEWSTATE is not even considered as, IMO, sucks).

As you may know SQL Server 2005 provides the function ROW_NUMBER which returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

We need sorting on every returned column (five in this example) and dynamic SQL is not an option, so we have two possibilities: using plenty of IF ... ELSE ... and having 10 queries, which is a hell to maintain, or having a query like the following:

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

I've tried the query several times, with different arguments, and its performance it is quite good actually, but it stills looks like it might be optimized some other way.

Is anything wrong with this query or you would do it this way? Do you propose a different approach?

Answer 1

Simple:

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

This will sort on NULL (DESC) if ASC order is selected, or vice versa.

Let the ROW_NUMBER() function work over the same ORDER BY expression.