Join Simplification With Table Variables

Erik Darling has an example of how adding a key constraint to a table variable allowed the optimizer to filter it out:

I was trying to come up with a demo for something totally different. Don’t ask. Seriously. It’s top secret.

Okay, so it’s just embarrassing.

Anyway. I had these two queries. Which are actually the same query twice. The only difference is the table variable definition.

Click through for the demo and additional information.

Related Posts

Combinatorics With Joins

Dmitry Zaytsev explains the math behind why query plans can be so inefficient when dealing with a large number of joins: Let’s talk about the sequence of table joins in detail. It is very important to understand that the possible number of table joins grows exponentially, not linearly. Fox example, there are only 2 possible […]

Read More

Hints In Oracle Versus SQL Server

Kellyn Pot’Vin-Gorman shows an example of query hints in Oracle and in SQL Server: Oracle hints were quite common during the infancy of the Oracle Cost Based Optimizer, (CBO).  It could be frustrating for a database administrator who was accustomed to the Rules Based Optimizer, (rules, people!  If there’s an index, use it!) to give […]

Read More

Categories

April 2017
MTWTFSS
« Mar May »
 12
3456789
10111213141516
17181920212223
24252627282930