SQL view vs Stored Procedre

[TRACEYSQL]

I am pretty sure that when you run a view
i.e select * from myview where mycolumn='a'

The view is basically
select * from mytable

and shall return the entire data set into memory then perform the filter.

However, I cannot find anything on the whitepapers indicating this and with using Statistics on I cannot see that is happening. Anyway to see this so I can show it to a few developers.

Thanks

[ScottPletcher]

SQL will apply the WHERE as early in the process as it can. For a non-nested view, SQL should always apply the filter when reading from the table.

The problem is that when you get nested views, especially deeply nested views, SQL can "give up" on trying to get the WHERE condition(s) in the right place and just scan the table.

A good alternative can be in-line-table-valued functions. These give you the SELECT ... FROM capabilities of a view but are much better for performance than a view (or even a proc).

[JeffModen]

@ScottPletcher

SQL will apply the WHERE as early in the process as it can. For a non-nested view, SQL should always apply the filter when reading from the table.

The problem is that when you get nested views, especially deeply nested views, SQL can "give up" on trying to get the WHERE condition(s) in the right place and just scan the table.

A good alternative can be in-line-table-valued functions. These give you the SELECT ... FROM capabilities of a view but are much better for performance than a view (or even a proc).

+1000... especially on the iTVF suggestion.