views:

22

answers:

1

If I have a view, and embed the view in a query, will the view have to be processed fully before the rest of the query?

Example:

CREATE VIEW dbo.ExpensiveView AS
    SELECT IndexedColumn, NonIndexedColumn 
    FROM dbo.BigHairyTable 
    WHERE NonIndexedColumn BETWEEN 500000000 AND 500050000
GO

SELECT * FROM dbo.ExpensiveView
WHERE IndexedColumn BETWEEN 1000 AND 1099
GO
A: 

No. SQL Server will "expand" the view definition and optimize it in the context of the enclosing query.

onupdatecascade
http://sqlblog.com/blogs/merrill_aldrich/archive/2010/02/11/busting-a-persistent-myth-views-are-executed-before-enclosing-queries.aspx (full disclosure - I wrote that, and this question, and this answer, because I see this misconception so often)
onupdatecascade
@onupdatecascade: Provided the view definition contains no aggregates...or an ORDER BY
Mitch Wheat
@Mitch: even if there are aggregates, view's definition still may be flattened out.
AlexKuznetsov
It's not "expanding" the view - it is passing on the predicate(s) to the view. And it typically happens only when the view SELECT only has simple column references (no functions), and/or does not use DISTINCT/GROUP BY. Additionally, the example does not perform functions on the columns returned from the view...
OMG Ponies