SQL View order of execution

I need some clarification around how the execution of a where clause works around a view. My view calls for functions for each Item and have a where clause within it for only active Items. If I have table1 with 1 M records and the following is my view….

Create View vwTable1 as
    select 
        t2.Name as table1Name,
        t2.ItemId as table1Id,
        dbo.GetTimezone(t2.ItemId) as TimeZoneKey,
        t2.AlternateId, t2.Created,t2.ParentKey,
        t1.table1TypeKey,
        t3.English_US as table1TypeKeyName,
    from 
        table1 as t1
    join 
        Item as t2 on t1.table1Id = t2.ItemId
    join 
        vwEnumValue as t3 on t3.EnumValueId = t1.[table1TypeKey]
    where 
        dbo.GetInheritedStatus(t2.ItemId) in (1, 2)

When I write a query like this

Select * 
From vwTable1 
Where ItemId = 14..

My understanding is that the view will

1. ONLY run for one record which is for ItemId = 14….
2. then will call GetTimezone ONLY for that Item 14….
3. Only check GetInheritedStatus for one Item which is 14.

Is that correct?

Or it will execute

1. Select Name, GetTimeZone() for all 1 million records,
2. then out of them get ALL Items with inheritedStatus with 1 or 2….
3. Then will pick ItemID =14.

This execution order makes a huge difference. Basically I see this as a big query like

Select * 
from 
    (Select 
         t2.Name as table1Name,
         t2.ItemId as table1Id,
         dbo.GetTimezone(t2.ItemId) as TimeZoneKey,
         t2.AlternateId, t2.Created,t2.ParentKey,
         t1.table1TypeKey,
         t3.English_US as table1TypeKeyName,
     from 
         table1 as t1
     join 
         Item as t2 on t1.table1Id = t2.ItemId
     join 
         vwEnumValue as t3 on t3.EnumValueId = t1.[table1TypeKey]
     where 
         dbo.GetInheritedStatus(t2.ItemId) in (1, 2) )
where 
    ItemId = 14

Will the inner select go first for all 1 M records and then its where clause or the outer Select go first with only ItemId = 14.

Thanks

Answer

My understanding is that the view will 1) ONLY run for one record…

No.

SQL is a declarative language, not an imperative one. This means the database engine is free to perform any operation to retrieve the information you requested in any way the engine determines it’s “better” to do it.

The better part is tricky. Usually a SQL statement is rephrased after being parsed. Then the SQL planner will produce many possible plans to execute it, and finally the SQL optimizer will choose one of these plans.

Which one? It depends on multiple things:

• Type of tables.
• Repertoire of access operations.
• Indexes you have on the tables.
• Histogram of the data.
• Selection filters you are applying on the query.
• etc.

Furthermore, you can easily get the execution plan for a query. However, this doesn’t meant this plan will stay static in the future. Tomorrow, the SQL optimizer may choose a different plan according to changes on any of the values above. Maybe even a new update of your database adds new operations to the database engine.