DesmoShane (7/30/2013)
E.G. select top 1 no problem at all , select * from is going to take a while and have heavy usage of tempdb .
Maybe. All depending on the view definition, SELECT TOP 1 without ORDER BY could be an expensive operation, although it is less likely. SELECT * without a WHERE clause will take more resources, but again, not necessarily in tempdb. If the query can be implemented with only streaming operators, that will not happen.
And, again, the fact that this is a view or that it spans multiple databases, have zero importance here. It's a query, plain and simple.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]