Category: A Join A Day

• A Join A Day – The Nested Loops Join

  

  2012-12-21 - A Join A Day, General, Performance, Series, SQL Server Internals

  This article introduces the Nested Loops Join algorithm. It shows its strengths and weaknesses to help you identify query situations for which the Nested Loops Join operator in the execution plan is an appropriate choice.

  [more…]

• A Join A Day – Join Hints

  

  2012-12-20 - A Join A Day, Fundamentals, General, Hints, Performance, Series

  SQL Server cannot do an exhaustive search of all possible execution plans when compiling a query. On its quest to find the best plan, it sometimes misses the mark. See how join hints and query hints can help in a situation like that. But be aware, using join hints can backfire. Know the risks before you start using them.

  [more…]

• A Join A Day – Nested Joins

  

  2012-12-19 - A Join A Day, Fundamentals, General, Series

  When joining more than two tables together, one additional JOIN key word is needed for each new table. there are many ways to group the tables in a join query together effectively building nested joins. However, the order or even grouping of tables does not change the query. Indeed, the query optimizer often makes use of that and produces an execution plan in which the order of the tables does not match the order in which they are mentioned in the query.

  [more…]

• A Join A Day – Equi-Join vs. Nonequi-Join

  

  2012-12-18 - A Join A Day, Fundamentals, General, Series

  A join that is using only equality comparisons is call “Equi-Join”. A join on the other hand that has at least one inequality comparison in the join condition is called “Nonequi-Join”. Find common use cases and limitations by reading on.

  [more…]

• A Join A Day – Except

  

  2012-12-17 - A Join A Day, Fundamentals, General, Series, T-SQL Statements

  The EXCEPT keyword can be used to calculate the set theoretical difference of two sets of rows. You can use EXCEPT every time you want to return rows from one table that are not in another table. Under the covers SQL Server uses a Left Anti Semi Join too fulfill an EXCEPT requests.

  [more…]