Why does SQL try to convert rows that are excluded by my where clause?
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:
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.
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.