15Sep 2009 by Grant Fritchey 30 Comments

No Join Predicate

SQL Server, T-SQL
You could be looking at an execution plan on a query and see this message: Warning, No Join Predicate. With a very few exceptions (very few), that's not a warning, like "Mind the gap" or "Your Mileage May Vary" or "Never fight a land war in Asia." No. It's a screaming shout saying "BY ALL THE GODS IN VALHALA, YOU DON'T HAVE ANY JOIN PREDICATES IN THIS QUERY!" Seriously, that's exactly what it says. But, you might be asking yourself, that's what it says, but what does it mean? Please allow me to explain. The tables in your system, whether it's a classic BI star schema, or a highly normalized OLTP system, or even (shudder) ORM objects, are related to one another. Usually they're related through the use of primary…
Read More
29Sep 2008 by Grant Fritchey 3 Comments

Loop Joins, More rows on top or bottom?

T-SQL
I've seen this question come by in the web searches multiple times. The nested loop join is also called an iterative join. This is because it takes the rows from the inner part of the join and compares them through an iterative process (one-by-one) to the rows in the outer part of the join. So, if the optimizer has correctly chosen this operation for your query, you should see FEWER rows in the top, or outer, part of the join and MORE rows in the bottom, or inner, part of the join. Take this query as an example (run against AdventureWorks2008): SELECT * FROM [Sales].[SalesOrderHeader] soh JOIN [Sales].[SalesOrderDetail] sod ON soh.[SalesOrderID] = sod.[SalesOrderID] WHERE soh.[SalesOrderID] = 47716 Here we have a single row from the SalesOrderHeader table and 55 rows…
Read More