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 time
The 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.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]