Is It Possible Dynamic Order by in Row_Number Function
I had an sp which uses Row_Number function to generate row numbers like Row_Number() Over (Order by Date desc) RNO Using these Numbers we do the pagination let's say sp returned 340 records but as per policy we want to display only 1 to 20 records per page so a sql code is written select * ( select Row_Number() Over (Order by Date desc) RNO,* from table )A Where RNO between @From and @TO Now my question is is there any way I can use a dynamic order by in Row_Number function in above code is there any other way Note : 1)I cannot remove Row_Number function 2)Also I can write two If Clause like If @Orderby='ASC' then do this else I don't want to use multiple If code
Not dynamic as such, but you could use multiple row_number() functions to cover all the possible 'dynamic' sort orders, and then truly dynamically order based on that: use AdventureWorks2012; declare @orderby varchar(10) set @orderby = 'ASC' select inlineview.CustomerID, inlineview.PersonID, inlineview.StoreID, inlineview.TerritoryID, inlineview.AccountNumber, case when @orderby = 'ASC' then inlineview.rn_customerID_ASC when @orderby = 'DESC' then inlineview.rn_customerID_DESC end as RowNum from ( select CustomerID, PersonID, StoreID, TerritoryID, AccountNumber, row_number() over(order by CustomerID asc) as rn_customerID_ASC, row_number() over(order by CustomerID desc) as rn_customerID_DESC from Sales.Customer ) inlineview order by case when @orderby = 'ASC' then rn_customerID_ASC else null end asc, case when @orderby = 'DESC' then rn_customerID_DESC else null end ASC
If you want dynamic sort order, you could either use a totally dynamic SQL-string and sp_execute it. The other approach would be to have two columns - one with ASCending sort order, the other with DESCending sortorder and in the WHERE clause you could do: WHERE CASE WHEN @sortOrder='ASC' THEN RNO ELSE RNO_DESC END BETWEEN @FROM and @TO The first approach, with a dynamic SQL string, will most probably perform a lot better than two ROW_NUMBER-columns and a CASE-statement in the WHERE clause, but from a code maintainability perspective I prefer to not use dynamic SQL (plus a dynamic SQL approach would have to look out for input which would break the sql-statement or even worse harm your database). The big problem with the second approach is the WHERE-clause, which causes SQL Server to push all rows through each step of the execution plan until the very last filtering step, whilst the approach with dynamic SQL will only collect the correct number of rows in the first Place. When I tested on a table with 90000 rows, I got 2 logical reads with a dynamic SQL-approach, and more than 300 logical reads with the not dynamic SQL-approach. The number of logical reads in the dynamic SQL-approach will remain constant until you have so many rows that the index-tree for the date column will grow deeper, whilst the not dynamic SQL-approach will constantly grow with each page of the table. I also tried changing ROW_NUMBER to make that somewhat dynamic in itself: ROW_NUMBER() OVER(ORDER BY CASE WHEN @sortorder='ASC' THEN Date ASC END,Date DESC) But that performs even worse - it gives Three times as many reads as using two ROW_NUMBER-functions and CASE in the WHERE clause.