1

My query looks a bit like this:

Select top (@n) *
From MyTable
Where IsNumeric(MyColumn) = 1
Order By Cast(MyColumn as int) desc

Occasionally Sql will produce a query plan that places the Sort operator ahead of the filter operator. This is problematic since the table can contain values which are non-numeric.

Is there a query hint I can apply to force the filter to always come first?

Or would it be better to write my order-by clause to safely handle non-numeric values? I've found two options for this, but I'm not sure which one is better:

Order By Cast(Stuff(MyColumn, 1, Patindex('%[0-9]%', 1), '') as int) desc

Or

Order By Case IsNumeric(MyColumn) When 1 Then Cast(MyColumn as int) else 0 end desc
Joel
  • 111
  • 3
  • 7
    Related question and answer: http://dba.stackexchange.com/questions/64489/cte-error-nvarchar-to-numeric/64493#64493 – ypercubeᵀᴹ Nov 07 '16 at 19:54
  • Your last option ORDER BY CASE is better, but as Aaron Bertrand pointed out in his answer, CASE does not always guarantee left-to-right evaluation. So, the safest method would be two queries. First insert result of SELECT ... WHERE IsNumeric = 1 into a temp table, then select from this temp table your final result. – Vladimir Baranov Nov 08 '16 at 06:58

0 Answers0