Tuesday, February 13, 2007

SQLServer: Joining Views in Queries

Two Words: Bad Idea!

The problem with this is that the SQLServer optimizer has to determine the best cost for the set of joins, and then base that on a view that has additional joins underneath it.

It's almost always more efficient to rewrite the query to join the actual tables, and the optimizer is much faster at finding the Cost. We were also able to determine that when evaluating joins to view, the optimizer doesn't handle hints within the view. So if your view is supposed to use a nolock hint (SQLServer 2000), but you join it to a couple tables outside the view, you end up getting shared locks anyway.


Bad Idea!