Are Views optimized when I add a WHERE clause to them?

Question

Does it make a difference if you filter a View inside or outside the View?

For example, is there any difference between these two queries?

SELECT Id
FROM MyTable
WHERE SomeColumn = 1

Or

SELECT Id
FROM MyView
WHERE SomeColumn = 1

And MyView is defined as

SELECT Id, SomeColumn
FROM MyTable

And is the answer any different if the source table is located on a Linked Server?

I'm asking because I have to query a large table (44mil rows) twice from a linked server, and get an aggregate of the results. I want to know if I should create two views to access the data, one for each query, or if I can get away with a single view and a WHERE clause.

Answer 1

You should see absolutely no difference in the plans or the performance between these two choices. When the view is queried, it is expanded out to a query against the base table, which means the same seek or scan will be used.

Now, depending on the data type and selectivity of MyColumn, if you wanted to create a filtered index on the base table (when you move to SQL Server 2008+), you might get better performance, but this again will not be different via the view or without.

Answer 2

Here's just a quick example showing that there should be no difference. The database is the AdventureWorks database.

Two View definitions:

create view Person.vContactWhere
as

    select *
    from person.Contact
    where ContactID = 24

go

create view Person.vContactNoWhere
as

    select *
    from person.Contact

go

Here would be the first query, with the WHERE clause included in the view definition:

select *
from person.vContactWhere

Here is the execution plan:

And the second query, with the WHERE clause not in the view definition, but in the SELECT query:

select *
from person.vContactNoWhere
where ContactID = 24

Here is that execution plan:

As you can see from these execution plans, they are identical with identical results. I don't know of a situation where this type of logic/design would ouput different results. So I'd be willing to say you are safe either way, and go with personal preference (or shop procedures).

Answer 3

Based on what I'm reading, SQL will use a standard view like a sub query when determining the execution plan.

So using my example query,

SELECT Id
FROM MyView
WHERE SomeColumn = 1

where MyView is defined as

SELECT Id, SomeColumn
FROM MyTable

it should generate the same execution plan as

SELECT Id
FROM 
(
    SELECT Id, SomeColumn
    FROM MyTable
) as T
WHERE SomeColumn = 1

but this execution plan may be different from what would be generated with

SELECT Id
FROM MyTable
WHERE SomeColumn = 1

I'm not sure if this answer would be the same for Indexed Views