CODEWITHSUNDEEP
sqlsql-servert-sql
Gavin
Gavin

Reputation: 2351

Internals of SELECT FROM view WHERE

Say I have a simple view, MyView.

If I do:

SELECT * FROM MyView

I understand that the underlying query behind MyView is executed to build the view.

Now, if I was to:

SELECT * FROM MyView WHERE MyValue BETWEEN 2 AND 5

Am I right to think that the whole underlying query must still be executed before the WHERE is applied?

I believe this to be the case from looking at the Execution Plan window, but I think my example may be too simple to see any real difference.

(Implication being that as things get more complex, depending on the conditions of the WHERE and and the conditions in the joins that make up the view, would it be more efficient to write the query without using the view?)

Upvotes: 6

Views: 8820

Answers (1)

GSerg
GSerg

Reputation: 78181

No. A where clause externally applied to the view is "inserted" into the view as if it was an integral part of it. Whether or not that would cause all records of the view fetched depends on the nature of the column you are querying, but the result will basically be the same as if you were executing an adhoc query consisting of the view body plus your where clause.

For instance, this view:

create view that_view as select * from Orders

will be executed with a scan on Orders table, returning all records, whereas 

select * from that_view where order_number = 1

will be executed with an index seek on order_number (provided you have the index), only returning one row without building the whole table.

Some gotchas exist though.

Upvotes: 9

Related Questions