Why does SQL try to convert rows that are excluded by my where clause?

Question

I've come across this multiple times, and I'm sure there is a good reason for it - but how do I avoid it?

I'm sure it has to do with the quirks around isnumeric. In English, I have a view that filters on isnumeric(somefield) = 1. I then try to query it using an int in my where clause, and because some other field in the table has character values, the whole thing fails.

I created a SQL fiddle that shows the error.

I tried doing a cast/convert in the select on the view, but the query engine seems to ignore it.

So - why does this happen, and is there a clean way to deal with it?

The original fiddle had a very similar error due to me missing quotes on the insert. That is NOT the problem I was trying to highlight. I have fixed the view by adding quotes to all values being inserted.

Answer 1

The view is not being used, since it is being "optimized away" by the query optimizer. You can see this by looking at the query plan:

You could create an indexed materialized view, then use the NOEXPAND table hint on the target query to prevent this optimization.

An example:

USE tempdb;

IF OBJECT_ID(N'dbo.TestView', N'V') IS NOT NULL
BEGIN
    DROP VIEW dbo.TestView;
    DROP TABLE dbo.Test;
END
CREATE TABLE dbo.Test 
(
    TableKey int
    , TestField nvarchar(24)
);
GO

Create a the view, with SCHEMABINDING:

CREATE VIEW dbo.TestView 
WITH SCHEMABINDING
AS
    SELECT 
        t.TableKey
        , t.TestField
    FROM
       dbo.Test t
    WHERE ISNUMERIC(t.TestField) = 1;
GO

Create an index on the view:

CREATE UNIQUE CLUSTERED INDEX PK_TestView
ON dbo.TestView (TableKey);
GO

Insert the test data:

INSERT INTO dbo.Test
select 0,        '0'
union select 1,  'Rejected'
union select 2,  'Unlinked'
union select 0,  '0'
union select 3,  '1'
union select 162,'1000'
union select 16, '10000'
union select 17, '10010'
union select 18, '10011'
union select 19, '10012'
union select 20, '10031'
union select 21, '10041'

Query the view:

SELECT * 
FROM dbo.TestView WITH (NOEXPAND)
WHERE Testfield = 1000

Results:

╔══════════╦═══════════╗
║ TableKey ║ TestField ║
╠══════════╬═══════════╣
║ 162      ║ 1000      ║
╚══════════╩═══════════╝

The query plan:

Notice the triangle exclamation in the SELECT node in the query plan above - that is a warning:

Type conversion in expression (CONVERT_IMPLICIT(int,[tempdb].[dbo].[TestView].[TestField],0)) may affect "CardinalityEstimate" in query plan choice, Type conversion in expression (CONVERT_IMPLICIT(int,[tempdb].[dbo].[TestView].[TestField],0)=CONVERT_IMPLICIT(int,[@1],0)) may affect "SeekPlan" in query plan choice

The type conversion warning can be eliminated if we modify the view like so:

CREATE VIEW dbo.TestView 
WITH SCHEMABINDING
AS
    SELECT 
        t.TableKey
        , TestField = TRY_CONVERT(int, t.TestField)
    FROM
       dbo.Test t
    WHERE ISNUMERIC(t.TestField) = 1;

Now, the view will return the TestField column typed as an integer, and since we are using NOEXPAND in combination with persisted data, the implicit type conversion is eliminated:

Answer 2

As other answers have explained, the issue is with the INSERT statement and - when that is fixed - with the view being optimized away.

The result is that the query's where Testfield = 1000 will try to cast TestField to numeric (due to the data type precedence rules) for all rows and thus all values of the column.

Order of execution is very hard to force as the optimizer is free to do rewrites of a query, and provides no guarantees about the timing, order, or number of evaluations of scalar expressions.

One way to work around this is to use a CASE expression. It's still not absolutely guaranteed to avoid this type of issue, but it almost always works. Tested in sqlfiddle.com:

CREATE VIEW dbo.TestView 
AS
  SELECT  
    TableKey,
    TestField = CASE WHEN IsNumeric(TestField) = 1 THEN TestField END
  FROM
    dbo.Test
  WHERE
    IsNumeric(TestField) = 1 ;

See also this answer by Aaron Bertrand for a more detailed explanation: CTE Error (nvarchar to numeric) and the suggestion to use the more robust TRY_CONVERT() if you are in a recent enough version.

Answer 3

Ok - it may not have been clear in my question, but the main thing I wanted to do was to query my view without having to use quotes.

I was able to achieve this, thanks to newer versions of SQL and the try_convert function. If I added that to my view on the field in question, I can successfully query it without using quotes.

Fiddle showing change.

Oddly enough, the fiddle doesn't return results - but my actual code is working this way, so I'll call it a win.

Answer 4

It has nothing to do with the view. It has to do with the union. SQL Server is typing your union to (int, int) it needs to type it to (int, nvarchar(24)). While counter-intuitive, types in unions are always interesting. In your example, the insert is actually failing.

To fix this, just be sure you query TestField as if it was a text column (because that's what you made it), and in the insert make sure you're correctly typing the values so as not to confuse SQL Server.

insert into dbo.Test
select 0,   '0'
union select 1, 'Rejected'
union select 2, 'Unlinked'
union select 0, '0'
union select 3, '1'
union select 162,   '1000'
union select 16,    '10000'
union select 17,    '10010'
union select 18,    '10011'
union select 19,    '10012'
union select 20,    '10031'
union select 21,    '10041'
;
select * from dbo.Test
where Testfield = '1000';

Answer 5

I've created a slightly modified version of your sqlfiddle.com link.

The data populates correctly, can be selected using the same statement as in the view, but selecting from the view where id = 1000 still fails.

Why? It's the optimizer's fault.

The optimizer sees through the view, and basically treats:

SELECT TableKey, TestField
  FROM dbo.TestView
 WHERE TestField = 1000
;

as if it were:

SELECT TableKey, TestField
  FROM dbo.Test
 WHERE IsNumeric(TestField) = 1
   AND TestField = 1000
;

When it goes to optimize, it has to decide which will better limit the data returned - and the cardinality of TestField is a lot better than IsNumeric(TestField).

I admit, I am guessing there. It may ignore the function because only a numeric value could match, or simply evaluating it second because it can't easily determine the cardinality of the function result without scanning the table and running the function.

Still, the basic reality is that the optimizer isn't going to filter out the non-numeric rows first, even though that's what you want it to do.

If you made the view an indexed view, and used the NOEXPAND table hint, then you might get the results you're expecting; the optimizer should refuse to go straight to the table, and use the index on the view for what it needs.

The simplest solution is to avoid the implicit conversion. If you don't leave which side to convert up to SQL, then you've got control of what's happening.

Prior to SQL 2012, forcing your input to a string (using '1000' instead of 1000) is probably your simplest solution.

Starting with SQL 2012, you can use TRY_CONVERT (see the last query in the sqlfiddle). This returns a converted value where it can, and a NULL where it can't. Of course, at that point, you could do away with the view.

So:

1. SQL Server doesn't treat views like tables, and can rearrange them so your view's WHERE clause isn't evaluated before your query's WHERE clause.
2. Don't trust implicit conversions to do what you want the way you want them to.

Answer 6

You need to short-circuit the test not the value using case something like this:

  SELECT TableKey, TestField
  FROM dbo.Test
 WHERE 
   case when isnumeric(testfield)=1 then testfield else null end = 1000

Putting

case when isnumeric(testfield)=1 then testfield else null end as newTestField

in the view and testing against that in your select would also work.

Answer 7

Let's get fundamental.

• A view is - in the way you did it - evaluated at runtime.
• In order to check the filter it must compare every row whether it is numeric or not. There is no "optimization" in the view. The view would force a conversion of every row for a check of numeric.
• Same with your query. The query makes the view redundant - the check is thus eliminated logically. But as you compare to numeric value, you STILL must convert all rows.

Typical beginner trap. Function over field (in general) means no optimization (unless using features like an index over a computed field). Conversion in this scenario forced table scan AND conversion of every row. Bad table and data design coming back to bite you.

As RDFozz says, your best way is to force a string comparison (LIKE '1000') which is native to your data.