MERGE JOIN STRATEGIES
In a previous mail, we discussed SQL Server join strategies and nested 
iterations. We outlined a dual looping strategy that SQL Server 
performs in order to get a result set.
Merge joins are another way that SQL Server obtains result sets outside 
of the more common nested iteration methodology. These types of joins 
were introduced with v7.0.
The most important aspect to remember is that SQL Server will, more 
than likely, choose to perform a merge join on two tables where both of 
the join columns are in sorted order. Using a query from Microsoft 
Press Inside SQL Server may help to explain the simple scenario below.
Assume the following query and datasets.SELECT dept.deptno, empl.deptno 
FROM departments dept JOIN employees empl    ON dept.deptno=empl.deptno  
dept           empl deptno         deptno ------         ---- 1              1 
2              3 3              4 5              5 6             
The query would return something like:dept.deptno  empl.deptno 
-----------  ----------- 1            1 3             3 5            5
The merge join makes one pass through the list and eliminates the 
values that do not fit the join criteria. The MS Press Book example has 
a very good analogy of this process.
* Assume you have two sorted stacks of paperwork on deptno.
* You need to consolidate the two stacks based on the join criteria.
Most likely, you will process each stack in unison, pulling out papers 
from either stack that do not match. Once you make it through both 
stacks of paperwork and toss out the values that did not meet the join 
criteria, there will be no need to pass through them again. Passing 
through each stack once will set your result.
In a future mail, we'll discuss the hash join strategy. For more 
information regarding join strategies, check out Inside Microsoft(r) SQL 
Server(TM) 7.0.http://click.online.com/Click?q=c3-x1sCQJLyNQCJWjQI5gdWNccLHdRR 
-------------------------------------------