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.

Answer 2

I've added the case in the ORDER BY clause, but I'm getting the following error:

Msg 4145, Level 15, State 1, Line 33
An expression of non-boolean type specified in a context where a condition is expected, near 'THEN'.

One of the reasons that I've made the query like that is that the `ROW_NUMBER` function has the following signature: `ROW_NUMBER () OVER ([ ] )`, so if I indicate a distinct sorting column in the `OVER ()` and `ORDER BY` clauses I might get a result like the following:

    RowNumber            OrderID     CustomerID EmployeeID
    -------------------- ----------- ---------- -----------
    396                  10643       ALFKI      6
    445                  10692       ALFKI      4
    455                  10702       ALFKI      4
    588                  10835       ALFKI      1
    705                  10952       ALFKI      1
    764                  11011       ALFKI      3
    679                  10926       ANATR      4
    512                  10759       ANATR      3
    378                  10625       ANATR      3
    61                   10308       ANATR      7

instead of the correct result (or at least the one that makes sense using with ROW_NUMBER() for pagination):

    RowNumber            OrderID     CustomerID EmployeeID
    -------------------- ----------- ---------- -----------
    1                    10248       VINET      5
    2                    10249       TOMSP      6
    3                    10250       HANAR      4
    4                    10251       VICTE      3
    5                    10252       SUPRD      4
    6                    10253       HANAR      3
    7                    10254       CHOPS      5
    8                    10255       RICSU      9
    9                    10256       WELLI      3
    10                   10257       HILAA      4

Answer 3

Ok, the sorting works perfect, thanks. Now I'll have to figure out the pagination.

I can't see the reason why ROW_NUMBER() needs the OVER() clause, IMO it's pointless.

Answer 4

Well, thanks to Tomalak help I can tell you the following:

I've run the two versions of the query (online: my version and Tomalak's) and I've ran them in the SQL Server Studio Management alongside the SQL Server Profiler first, and then with the Database Engine Tuning Advisor. Results are following:

• Events: SQL: BatchCompleted.
• Columns: TextData, Duration, CPU, Reads, RowCounts, Writes

                Duration    CPU     Reads   Writes  RowCounts    
Mine            96          47      22      0       14
Tomalak's       41          0       22      0       14

As you may see, Tomalak's version is far superior in both speed and CPU usage. And I think it could even be far more efficient as when I've run it through the Database Engine Tuning Advisor it recommended to add a clustered index on column OrderID (maybe if it's sort in a different column will recommend something different). The funny thing is that when I've analysed my version of the query it doesn't recommend me nothing.

I've also made a script to iterate over all the "pages", computing statistics. It's clearly far more superior Tomalak's version:

                Duration    CPU     Reads   Writes
Mine            1364        1109    1834    0     
Tomalak's        961         203    1830    0     

Answer 5

I was searching the Net and found this website. based on this query, I tried the following, but it gives me the error. "Msg 4108, Level 15, State 1, Line 1 Windowed functions can only appear in the SELECT or ORDER BY clauses."

Any help is appreciated. I tried whatever possible, but, it's not giving me any result. Thanks Joseph

select employee_id from V_EMPLOYEE_CODE_TABLE where row_number() OVER ( order by employee_id ) > 0 ORDER By Employee_ID

Answer 6

I think the ORDER BY RowNumber clause at the end of your query is redundant. It's been already sorted using ROW_NUMBER() OVER (ORDER BY Orders.OrderID ASC) clause. Hasn't it?