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

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.

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.

StackExchange Database Administrators Q#15363, answer score: 17