Inside SQL Server Joins

 

NESTED-LOOP  JOIN

The optimizer chose one table to process first,and for each row that qualified ( based on any WHERE clause conditions involving columns in that table),SQL Server used the JOIN clause to find all matching rows in the second table. The JOIN clause usually performed an equality comparison between a column in the first table and a column in the second.You can think of nested-loop joins as being the default type of join.

 

MERGE  JOIN

It is appropriate when both input sets to the JOIN operation are ordered by the values in the join column, as would be the case when both have a clustered index on the column you're useing to join the tables. The optimizer usually chooses a merge join when clusted indexes exist on the join column in both tables. SQL Server can perform merge joins only when at leat one of the inputs is known to have unique values in the join column.If both inputs can have duplicates, SQL Server can't process a merge jon by making only one pass through each table,so the optimizer usually choose a nested-loop join instead.

 

HASH  JOIN

SQL Server most often uses hash joins when no useful indexs for joining the tables exists. When performing a hash join on two tables, SQL Server uses one tables (called the build input) to build the hash buckets, each of which contains all the existing data values that generate the same value when the hash function is applied to them. Then, it inspects the other table(called the probe input) one row at a time an tries to find matching values in the hash buckets.

 

EXECUTE  ORDER

The optimizer evaluates possible plans from the simplest to more complex plans

 

The simplest plan is to use the "default" nested-loop join ,so the optimizer evalueates that type first.

If neither nested-loop join nor merge joins will give good performance, the optimizer considers hash joins.

 

Scans Versus Seeks

Don’t mistake Index Scans for Index Seeks

Seek is the only efficient strategy for finding individual rows or a range of qualifying rows

 

Scan generally involves reading all rows of an index and is expensive for large tables unless you really need most of them

If a table has a Clustered Index, a ‘Table’ scan will always be shown as a Clustered Index Scan