Blog

• A Join A Day – The Hash Join

  

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

  This article introduces the Hash Join algorithm The Hash Join algorithm and shows in which situations it is a good choice. This algorithm is the most CPU and memory intensive one but if you are dealing with large tables it will often also be the fastest.

  [more…]

• A Join A Day – The Sort Merge Join

  

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

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

  [more…]

• 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…]

• T-SQL Tuesday #37 – Roundup

  

  2012-12-18 - General, T-SQL Tuesday

  This is the roundup for this month’s T-SQL Tuesday #37. It includes a short review of all participating blog posts.

  [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…]

• A Join A Day – Intersect

  

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

  INTERSECT is a keyword that takes two query expressions and calculates the set theoretical intersection from the two row sets. Under the covers SQL Server often uses the Left Semi Join operator for INTERSECT queries. One peculiarity is, that duplicate rows will be removed from the final result.

  [more…]

• A Join A Day – Right Anti Semi Join

  

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

  The Right Anti Semi Join, like the Right Semi Join, does not have a corresponding T-SQL statement. In the right circumstances however, the SQL Server optimizer will use the Right Anti Semi Join operator to build an efficient plan.

  [more…]