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

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.

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:

StackExchange Database Administrators Q#182728, answer score: 9