SQL Server VIEW Performance

Question

If I have a VIEW with a bunch of INNER JOINs, but I request only those VIEW SELECTing-only columns that come from the main table, does SQL Server ignore unnecessary connections in VIEW at runtime, or should these connections still happen for some reason?

If it is different from the other, this applies to SQL Server 2008 R2. In any case, I know that this is not a very good solution, but I'm trying to find the lesser of 2 evils.

Answer 1

He can ignore unions if they do not actually change the semantics. One example of this can be if you have a confining foreign key constraint between tables, and you select only columns from the link table ( See Example 9 in this article ).

You will need to check the implementation plan to make sure in a specific case.

Answer 2

If you do not pull fields from these tables, it may be faster to use the EXISTS clause - this will also prevent duplicates from the JOIN ed table causing errors in your results.

Answer 3

Even if the optimizer ignores unnecessary connections, you just need to create another view to handle your specific case. The use and misuse of opinions (for example, this case) can get out of control and lead to confusion, confusion, and very serious performance issues.

You might even think about how to reorganize the view you plan to use by connecting it to the "smaller" views to deliver the same dataset that it is doing now ... if it makes sense to do this, of course.