34

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.

Improve this question
9
  • 1
    why would you even use a view if you only have one table in it?
    – HLGEM
    Mar 21, 2012 at 17:11
  • 4
    @HLGEM security?
    – JNK
    Mar 21, 2012 at 17:13
  • 3
    @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, 2012 at 17:35
  • also see stackoverflow.com/a/6654525/27535
    – gbn
    Mar 21, 2012 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, 2012 at 20:00

3 Answers 3

Reset to default
16

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.

Improve this answer
3
  • 3
    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, 2012 at 19:07
  • 1
    If views are not for performance they are just for structure?
    – profimedica
    Jul 23, 2014 at 16:19
  • 2
    @profimedica indexed views can be created for performance reasons (e.g. to store intermediary results like aggregates instead of calculating them at runtime). If a view is not materialized, it can be for a variety of reasons: DRY (common join or filter performed in many different queries), security, obfuscation, schema simplification.
    – Aaron Bertrand
    Jul 23, 2014 at 17:13
9

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

Improve this answer
5
  • 1
    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, 2012 at 19:07
  • 1
    @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, 2012 at 19:19
  • 1
    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, 2012 at 20:09
  • 2
    @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, 2012 at 21:12
  • 2
    @Rachel - More info about this issue here
    – Martin Smith
    Mar 10, 2013 at 9:38
4

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

Improve this answer
7
  • I don't think it's an explicit text replacement like that.
    – Aaron Bertrand
    Mar 21, 2012 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, 2012 at 18:34
  • @Rachel - The substitution happens with the algebrized tree not at a textual level.
    – Martin Smith
    Mar 21, 2012 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, 2012 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, 2012 at 18:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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