SQL Server script Error: Insufficient result space to convert uniqueidentifier value to char.

Nested Loop join,  Hash Join and Merge Join

SQL server has three types of internal joins. Although there are different kinds of logical joins at the query level, SQL Server implement these all with three different physical join operators based on the scenarios. They are:

1. Nested Loop join
2. Hash Join
3. Merge Join

 1. Nested Loop Join

The optimizer may choose the Nested Loops join when one of the joining tables is small, considered as the outer table and the other one is large, considered as the inner table which is indexed on the column that is in the join.One of the joining table is taken as the outer table and the other one as the inner table. For each row of the outer table, all the rows from the inner table are matched one by one. It cycles through the outer table, then search for matches in the inner table. 

The optimizer considers three variants for a Nested Loops join:

1. Naive nested loops join when the search scans the whole table or index
2. Index nested loops join when the search can utilize an existing index to perform lookups
3. Temporary index nested loops join if the optimizer creates a temporary index as part of the query plan and drop it after query execution completes. 

2.Hash Join:

SQL server would select this join strategy in following scenarios:

1. when tables to be joined are large and dissimilar in size
2. when tables to be joined don’t have a useful index. 

It build hashing tables to sort through the result.

3.Merge Join:

SQL server would select this join strategy when tables to be joined are large and similar in size.

 It takes a row from each table and compares them. 

Ref:

http://www.mssqltips.com/sqlservertip/2115/understanding-sql-server-physical-joins/