Erland Sommarskog (7/30/2013)
DesmoShane (7/30/2013)
You say that the view is created in tempdb and it can have a heavy impact ,so each time the view is accessed how is the view "rebuilt" does it leave the 20 million records in the tempdb and add extra records to the view or keep it in memory or is it recreated from scratch each timeThe person who said that was flat wrong.
A view is nothing but a macro. When SQL Server processes a query with a view, SQL Server expands the query in the view into the query, and then optimizes the resulting query. The view is never materialised as part of the process. However, depending on the resulting query, the query plan may include operators (spools, hashing, sorting etc) that use space in tempdb. But that has nothing to do with the fact that the query includes a view or that it spans multiple databases.
Nobody ever said the view is materialized nor that the full 20 million records are stored in tempdb. Selecting 20 million records and a select * however can have an impact on tempdb due to spooling, sorting, hash tables and spillover - far more than the impact on the user databases.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events