Unexpected view behaviour

One of my coworkers came to me last week with a question. A good one too, because i had to take some time to figure out what was happening and why. He found some strange behaviour where a view returned an error when it should return data. The view contains a native function that parses data.

First of all, the code to describe the issue.

CREATE TABLE TEST
( INTEGER_COLUMN VARCHAR(500));
GO

INSERT INTO TEST (INTEGER_COLUMN) VALUES (1);
INSERT INTO TEST (INTEGER_COLUMN) VALUES ('1-0');
INSERT INTO TEST (INTEGER_COLUMN) VALUES (2);
GO

CREATE VIEW TEST_VIEW
AS
SELECT INTEGER_COLUMN 
FROM TEST
WHERE ISNUMERIC(INTEGER_COLUMN) = 1

We’re creating a table, adding three rows where one of the three isn’t a real integer and we’re creating a view that only gets the rows that are integers.

When i execute the view, this is the result.

SELECT INTEGER_COLUMN
FROM TEST_VIEW

Now, what happens when i want to filter the data from this view, i only need the second column in my resultset.

SELECT INTEGER_COLUMN
FROM TEST_VIEW 
WHERE INTEGER_COLUMN <> 1

The question from my coworker was simple. Why is this happening? Because he’s selecting from the view, his instinct is that the returned result set should be filtered within the view first and that the resultset can be narrowed down further with the regular query.

To see what happens, i’ve cut out the middle man (also know as the view) and executed this query:

select INTEGER_COLUMN
from TEST
WHERE ISNUMERIC(INTEGER_COLUMN) = 1
AND INTEGER_COLUMN <> 1

This query produces a neat output, as expected:

But what happens when the where clause get’s turned around?

select INTEGER_COLUMN
from TEST
WHERE INTEGER_COLUMN <> 1
and ISNUMERIC(INTEGER_COLUMN) = 1

Now, what seems to happen is this. When you build a view you get to filter data. But this filtering takes places after the filters you provide in your query that references the view. That means that the validation functions in a view are second to the filtering you want in your result set. This is something i didn’t know and i’m not really shure what to think of it. The fixes are easy but i hope this demo helps if you ever run into strange behaviour like this.

If any of you reading this has an opinion, please share!

Thanks for reading!