Internals of SELECT FROM view WHERE
sql, sql-server, t-sql
Solution
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.
Problem
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?)