SQL Server Engine optimization in Views

Question

I've two DataBases DB1 and DB2 in the same SQL Server instance. Here's the structure of both DBs

DB1

TableA:

[key]      Id   int  
[required] Name nvarchar(50)

DB2

TableB:

[key]      Id           int
[required] Total        real
[required] IdTableA     int  // Foreign key

In DB1 I also have a view:

TableBView:

select * from DB2.dbo.TableB

Now I run the following query

select Total
from DB1.TableBView
where Total > 100

I wanna know whether the SQL Engine:

a) is running first the query defined by the view (which return all rows and all columns) b) and then select the rows where Total > 100 or
it's applying the where statement directly to the View's query and it's only returning the column Total from the View's query?

Seems like a duplicate of this: http://stackoverflow.com/a/439061/3915817 or this: http://stackoverflow.com/a/1867979/3915817 — KHeaney, Dec 11 '14 at 22:01

score 0 · Answer 1 · answered Dec 11 '14 at 22:01

So when you run a VIEW the optimizer goes to the VIEW definition and grabs that QUERY PLAN. It then executes that QUERY PLAN, then it executes your WHERE clause. So to answer question all rows will be selected then your filter will be applied. Now all this can change depending on if you materialized your view (put an index on it) then, all rows wouldn't be returned. The query would be filtered and then only rows total > 100 would be processed.

SQL Server Engine optimization in Views

1 Answers1