Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
1  
why would you even use a view if you only have one table in it? –  HLGEM Mar 21 '12 at 17:11
2  
@HLGEM security? –  JNK Mar 21 '12 at 17:13
    
@HLGEM The View actually contains multiple queries to multiple databases on different servers, and it joins them all by a UNION ALL. It's much easier to use a View than to have to rewrite the UNION query anytime I need the data. –  Rachel Mar 21 '12 at 17:35
    
also see stackoverflow.com/a/6654525/27535 –  gbn Mar 21 '12 at 17:49
1  
@datagod I'll keep that in mind, thanks :) In this case, there's a fairly small app that gathers data from a bunch of servers, runs some calculations, and spits out a bunch of reports. It has it's own database because some of the calculations are fairly resource intensive, and I wanted to separate it from everything else. –  Rachel Mar 21 '12 at 20:00
show 4 more comments

3 Answers

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.

share|improve this answer
    
What about this question, which is asking why a query with the where clause outside the view takes so much longer than when it's put in the view? –  Rachel Mar 21 '12 at 19:07
add comment

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:

enter image description here

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:

enter image description here

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).

share|improve this answer
    
What about this question, which is asking why a query with the where clause outside the view takes so much longer than when it's put in the view? –  Rachel Mar 21 '12 at 19:07
    
@Rachel I think gbn explained it rather well in his post, and the article he pointed to. I don't know how else to put it. –  Thomas Stringer Mar 21 '12 at 19:19
    
I linked that because in that case, the execution plans were not the same, which is different from what your answer says. –  Rachel Mar 21 '12 at 20:09
    
@Rachel The issue in that example is a missing transformation rule. It doesn't just apply to views but also CTEs and other table expressions. In the general case it is not valid to push the predicate down into table expressions containing ranking functions as that will change the result. The reason it is valid in this case is because the Where clause fits the PARTITION BY. SQL Server 2008 seems to have a new rule SelOnSeqPrj to recognize this particular case. –  Martin Smith Mar 21 '12 at 21:12
    
@Rachel - More info about this issue here –  Martin Smith Mar 10 '13 at 9:38
add comment

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

share|improve this answer
    
I don't think it's an explicit text replacement like that. –  Aaron Bertrand Mar 21 '12 at 18:19
    
@AaronBertrand You might be right. I honestly have no idea... I'm learning as I go :) That assumption was based on other things I've been reading about how views are like macros. I edited the question slightly to specify that I'm referring to standard views, not indexed views. –  Rachel Mar 21 '12 at 18:34
    
@Rachel - The substitution happens with the algebrized tree not at a textual level. –  Martin Smith Mar 21 '12 at 18:49
    
@MartinSmith Hrrmm isn't that what I said? That the execution plans should be the same, not that the text would be the same? I'm not sure that I'm understanding "algebrized tree" –  Rachel Mar 21 '12 at 18:53
    
It was just in response to your comment on the Q itself which says that it "inserts the text of a View into your query" and Aaron's comment above. Some info about parse/compile stages here. Actually your answer does mention text substitution as well. Whether this is a distinction worth making. Not sure! But I guess it explains why sp_refreshview is needed which the text substitution concept wouldn't. –  Martin Smith Mar 21 '12 at 18:56
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.