3

My problem seems to be very simple but I'm stuck here. I have a table which has an "nvarchar" column called "SrcID" and I store both numbers and strings in that. Now, when I try to check for "IsNumeric" on that column in a "Join" condition, something like below,

   ISNUMERIC(SrcID) = 1 AND SrcID > 15

I am getting the following error:

  Msg 245, Level 16, State 1, Line 47
  Conversion failed when converting the nvarchar value 'Test' to data type int.

Amazingly, when I remove the check "SrcID > 15", my query is running properly. Should I include anything else in this statement?

Please help me in fixing the issue. Thanks in advance!!

Praveen
  • 1,449
  • 16
  • 25

2 Answers2

4

You can't count on the order in which a database will evaluate filtering expressions. There is a query optimizer that will evaluate your SQL and build a plan to execute the query based on what it perceives will yield the best performance.

In this context, IsNumeric() cannot be used with an index, and it means running a function against every row in the table. Therefore, it will almost never provide the best perceived performance. Compare this with the SrcID > 15 expression, which can be matched with an index (if one exists), and is just a single operator expression even if one doesn't. It can also be used to filter down the number of potential rows where the IsNumeric() function needs to run.

You can likely get around this with a view, a subquery, a CTE, a CASE statement, or a computed column. Here's a CTE example:

With NumericOnly As 
(
    SELECT <columns> FROM MyTable WHERE IsNumeric(SrcID) = 1
)
SELECT <columns> FROM NumericOnly WHERE SrcID > 15

And here's a CASE statement option:

SELECT <columns> FROM MyTable WHERE CASE WHEN IsNumeric(SrcIC) = 1 THEN Cast(SrcID As Int) ELSE 0 END > 15
Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
  • The `CTE` should work too if you are 2008 as tagged - you may need to add a semicolon before the `WITH`: `;WITH Numericonly AS...` – JNK Jan 17 '12 at 15:16
  • I'm using 2012 and the CTE option is not working for me either. – Carlos Apr 03 '13 at 15:01
3

The filters in a WHERE clause are not evaluated in any particular order.

This is a common misconception with SQL Server - the optimizer will check whichever conditions it thinks it can the fastest/easiest, and try to limit the data in the most efficient way possible.

In your example, you probably have an index on SrcID, and the optimizer thinks it will be quicker to FIRST limit the results to where the SrcID > 15, then run the function on all those rows (since the function will need to check every single row otherwise).

You can try to force an order of operations with parentheses like:

WHERE (ISNUMERIC(SrcID) = 1) AND SrcID > 15

Or with a case statement:

WHERE CASE WHEN ISNUMERIC(SrcID) = 1 THEN SrcID > 15 ELSE 1=0 END

JNK
  • 63,321
  • 15
  • 122
  • 138
  • 1st one doesn't work (just tested on a SQL Server 2008), second one works like a charm. EDIT.- This comment was intented on another answer, sorry. – CMPerez Jan 17 '12 at 15:14