SQL Server - Order by case statement very slow

Question

I have the following query in a SP which executes fine:

SELECT * FROM MyTable u
ORDER BY 
u.[Id] DESC
OFFSET @PageSize * (@PageNumber - 1) ROWS
FETCH NEXT @PageSize ROWS ONLY

However, when I make the ORDER BY configured from a SP parameter the query goes from executing in ms to several seconds:

SELECT * FROM MyTable u
ORDER BY 
CASE WHEN @Sort = 0 THEN u.[Id] END DESC,
CASE WHEN @Sort = 1 THEN u.[Id] END ASC,
CASE WHEN @Sort = 2 THEN u.[LastName] END ASC,
CASE WHEN @Sort = 3 THEN u.[LastName] END DESC
OFFSET @PageSize * (@PageNumber - 1) ROWS
FETCH NEXT @PageSize ROWS ONLY

What is the reason now for the slowdown even though the same field is being used for the order? (ID DESC?)

Is there a way to make this perform better?

I have tried in SQL Server 2016 and SQL Azure.

EDIT: Execution plan/live stats without case ordering:

Execution plan/live stats with case ordering:

Answer 1

Any calculation or function, when applied to an index key column, will prevent SQL Server from using that index.

In your case, ORDER BY Id DESC can make use of an ordered scan of the Clustered Index on your table, as Id is the key column on that index. That means, only 20 rows have to be read.

Once you apply the CASE statement, SQL Server cannot use that index anymore and instead has to scan the entire row set and execute (very expensive) sorting on all rows to find the 20 rows you are interested in.

One of the solutions is to use dynamic SQL to include the appropriate ORDER BY without any calculations (as Lamak suggested in the comments above) or to just write the query 4 times and use an IF statement to decide which one to execute.