SQL Server : Star and Snowflake Schema in Data Warehouse with Examples

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

What is Multidimensional schema?

Multidimensional Schema is especially designed to model data warehouse systems. The schemas are designed to address the unique needs of very large databases designed for the analytical purpose (OLAP).

Types of Data Warehouse Schema:

Following are 3 chief types of multidimensional schemas each having its unique advantages.

• Star Schema
• Snowflake Schema
• Galaxy Schema

In this tutorial, you will learn more about-

What is a Star Schema?

Star Schema in data warehouse, in which the center of the star can have one fact table and a number of associated dimension tables. It is known as star schema as its structure resembles a star. The Star Schema data model is the simplest type of Data Warehouse schema. It is also known as Star Join Schema and is optimized for querying large data sets.

In the following Star Schema example, the fact table is at the center which contains keys to every dimension table like Dealer_ID, Model ID, Date_ID, Product_ID, Branch_ID & other attributes like Units sold and revenue.

Example of Star Schema Diagram

Characteristics of Star Schema:

• Every dimension in a star schema is represented with the only one-dimension table.
• The dimension table should contain the set of attributes.
• The dimension table is joined to the fact table using a foreign key
• The dimension table are not joined to each other
• Fact table would contain key and measure
• The Star schema is easy to understand and provides optimal disk usage.
• The dimension tables are not normalized. For instance, in the above figure, Country_ID does not have Country lookup table as an OLTP design would have.
• The schema is widely supported by BI Tools

What is a Snowflake Schema?

Snowflake Schema in data warehouse is a logical arrangement of tables in a multidimensional database such that the ER diagram resembles a snowflake shape. A Snowflake Schema is an extension of a Star Schema, and it adds additional dimensions. The dimension tables are normalized which splits data into additional tables.

In the following Snowflake Schema example, Country is further normalized into an individual table.

Example of Snowflake Schema

Characteristics of Snowflake Schema:

• The main benefit of the snowflake schema it uses smaller disk space.
• Easier to implement a dimension is added to the Schema
• Due to multiple tables query performance is reduced
• The primary challenge that you will face while using the snowflake Schema is that you need to perform more maintenance efforts because of the more lookup tables.

Star Schema Vs Snowflake Schema: Key Differences

Following is a key difference between Star Schema and Snowflake Schema:

Star Schema	Snowflake Schema
Hierarchies for the dimensions are stored in the dimensional table.	Hierarchies are divided into separate tables.
It contains a fact table surrounded by dimension tables.	One fact table surrounded by dimension table which are in turn surrounded by dimension table
In a star schema, only single join creates the relationship between the fact table and any dimension tables.	A snowflake schema requires many joins to fetch the data.
Simple DB Design.	Very Complex DB Design.
Denormalized Data structure and query also run faster.	Normalized Data Structure.
High level of Data redundancy	Very low-level data redundancy
Single Dimension table contains aggregated data.	Data Split into different Dimension Tables.
Cube processing is faster.	Cube processing might be slow because of the complex join.
Offers higher performing queries using Star Join Query Optimization. Tables may be connected with multiple dimensions.	The Snowflake schema is represented by centralized fact table which unlikely connected with multiple dimensions.

What is a Galaxy Schema?

A Galaxy Schema contains two fact table that share dimension tables between them. It is also called Fact Constellation Schema. The schema is viewed as a collection of stars hence the name Galaxy Schema.

Example of Galaxy Schema

As you can see in above example, there are two facts table

1. Revenue
2. Product.

In Galaxy schema shares dimensions are called Conformed Dimensions.

Characteristics of Galaxy Schema:

• The dimensions in this schema are separated into separate dimensions based on the various levels of hierarchy.
• For example, if geography has four levels of hierarchy like region, country, state, and city then Galaxy schema should have four dimensions.
• Moreover, it is possible to build this type of schema by splitting the one-star schema into more Star schemes.
• The dimensions are large in this schema which is needed to build based on the levels of hierarchy.
• This schema is helpful for aggregating fact tables for better understanding.

What is Star Cluster Schema?

Snowflake schema contains fully expanded hierarchies. However, this can add complexity to the Schema and requires extra joins. On the other hand, star schema contains fully collapsed hierarchies, which may lead to redundancy. So, the best solution may be a balance between these two schemas which is Star Cluster Schema design.

Example of Star Cluster Schema

Overlapping dimensions can be found as forks in hierarchies. A fork happens when an entity acts as a parent in two different dimensional hierarchies. Fork entities then identified as classification with one-to-many relationships.

Summary:

• Multidimensional schema is especially designed to model data warehouse systems
• The star schema is the simplest type of Data Warehouse schema. It is known as star schema as its structure resembles a star.
• A Snowflake Schema is an extension of a Star Schema, and it adds additional dimensions. It is called snowflake because its diagram resembles a Snowflake.
• In a star schema, only single join defines the relationship between the fact table and any dimension tables.
• Star schema contains a fact table surrounded by dimension tables.
• Snowflake schema is surrounded by dimension table which are in turn surrounded by dimension table
• A snowflake schema requires many joins to fetch the data.
• A Galaxy Schema contains two fact table that shares dimension tables. It is also called Fact Constellation Schema.
• Star cluster schema contains attributes of Star and Snowflake Schema.

MSSQL Server : Multiple ways we can do the update statements.

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

Multiple ways we can do the update statements.

 

In MSSQL Server  update statement we can write the different types.

See the below example.  

 

Created below the following tables.

 

-- Creating two tables.

 

Create  Table dbo.Table1(ID Int,Name VarChar(50));

GO

Create Table dbo.Table2(ID Int,Name VarChar(50));

 

GO

 

Insert Into dbo.Table1

Values(1,'A'),(2,'B'),(3,'C')

GO

Insert Into dbo.Table2

(ID,Name)

Values(1,'D'),(2,'E'),(3,'F')

 

GO

 

-- WITH Single Table

Update Table1 SET Name=(Select Name From Table2 T2 Where T2.ID=Table1.ID)

 

-- With Join Condition.

UPDATE T1 SET T1.Name=T2.Name

FROM dbo.Table1 T1 Inner JOin dbo.Table2 T2 On T1.ID=T2.ID

 

--Join Statement

Update Table1  SET Name=T2.Name

From dbo.Table1 T1 Inner Join dbo.Table2 T2 On T1.ID=T2.ID

 

--Using  Sub Query

Update Table1 SET Name=T2.Name

From (Select ID,Name

From Table2 ) T2 Where T2.ID=Table1.ID

 

 

--Clean up Tables

Drop Table dbo.Table1,dbo.Table2

PostgreSQL : How to Handle Multiple Transactions in PostgreSQL

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

Multiple Transactions in PostgreSQL

Note : One Stored Procedure/Function calling multiple stored Procedure/Functions.

Current Situation :

Note: if we are calling multiple stored procedure in one Stored Procedure inside, until complete the execution, before we cannot be able to check the data which is succeed with completed stored procedure, if we any exception occurred in between any of the stored procedure  succeed stored procedure also will rollback.

 

How to handle:

If we are executing nested stored procedure or functions within one SP/Function, with DML operation, if we required succussed stored procedure should do the commit condition.

Do the Commit Condition after stored Procedure?

 

Following below example.

Create or Replace Procedure Multiple_Transaction()

As

$$

Begin

                                    CALL StoredProcedure_1();

                                    Commit;

                                    CALL StoredProcedure_2();

                                    Commit;

                                    CALL StoredProcedure_();

End

$$

Language plpgsql;

SQL Server : Nested Loop, Hash and Merge Joins

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

Nested Loop, Hash and Merge Joins

When I was explaining some of my developers about the SQL Server Execution Plan, we came across the different types of joins SQL Server performs to optimize data retrieval. They are Nested Loop Join, Hash Join, Sort Merge Join. Interesting questions came up, what are the difference between them and how SQL Server determines which algorithm to use. So here is my take on explaining those questions.

 

Nested Loop Join (or NL Join)

A Nested Join compares each row from the Outer table to each row from the Inner table looking for the rows which satisfy the Join predicate.

For example, if you have statement like this one;

   SELECT A.*, B.* FROM  
   Customer A,  
   Sales B    
   WHERE A.CusomerId = B.CustomerId

It is interpreted as

For Each Row CR in Customer
    For Each Row SR in Sales
        IF CR join with SR Then
          Return (CR, SR)

The total number of rows compared and the cost of this algorithm is proportional to the size of the outer table multiplied by the size of the inner table. Since this cost grows quickly as the size of the input tables grow, in practice we try to minimize the cost by reducing the number of inner rows that we must consider for each outer row.

Lets take a look at the below SQL statement;

set statistics profile on;
select * from Sales.Store A 
Inner Join Sales.SalesPerson B on A.BusinessEntityID = B.BusinessEntityID 

You will see the execution plan as shown below.

Rows    Executes    StmtText    StmtId    NodeId    Parent    PhysicalOp    LogicalOp    Argument    DefinedValues    EstimateRows    EstimateIO    EstimateCPU    AvgRowSize    TotalSubtreeCost    OutputList    Warnings    Type    Parallel    EstimateExecutions

2    1    select * from Sales.Store A  Inner Join Sales.SalesPerson B on A.BusinessEntityID = B.BusinessEntityID    1    1    0    NULL    NULL    NULL    NULL    1    NULL    NULL    NULL    0.00918446    NULL    NULL    SELECT    0    NULL

2    1      |--Nested Loops(Inner Join, OUTER REFERENCES:([B].[BusinessEntityID]))    1    2    1    Nested Loops    Inner Join    OUTER REFERENCES:([B].[BusinessEntityID])    NULL    1    0    7.106E-05    4176    0.00918446    [A].[BusinessEntityID], [A].[Name], [A].[SalesPersonID], [A].[Demographics], [A].[rowguid], [A].[ModifiedDate], [B].[BusinessEntityID], [B].[TerritoryID], [B].[SalesQuota], [B].[Bonus], [B].[CommissionPct], [B].[SalesYTD], [B].[SalesLastYear], [B].[rowguid], [B].[ModifiedDate]    NULL    PLAN_ROW    0    1

18    1           |--Clustered Index Scan(OBJECT:([AdventureWorks2008R2].[Sales].[SalesPerson].[PK_SalesPerson_BusinessEntityID] AS [B]))    1    3    2    Clustered Index Scan    Clustered Index Scan    OBJECT:([AdventureWorks2008R2].[Sales].[SalesPerson].[PK_SalesPerson_BusinessEntityID] AS [B])    [B].[BusinessEntityID], [B].[TerritoryID], [B].[SalesQuota], [B].[Bonus], [B].[CommissionPct], [B].[SalesYTD], [B].[SalesLastYear], [B].[rowguid], [B].[ModifiedDate]    17    0.003125    0.0001757    76    0.0033007    [B].[BusinessEntityID], [B].[TerritoryID], [B].[SalesQuota], [B].[Bonus], [B].[CommissionPct], [B].[SalesYTD], [B].[SalesLastYear], [B].[rowguid], [B].[ModifiedDate]    NULL    PLAN_ROW    0    1

2    18           |--Clustered Index Seek(OBJECT:([AdventureWorks2008R2].[Sales].[Store].[PK_Store_BusinessEntityID] AS [A]), SEEK:([A].[BusinessEntityID]=[AdventureWorks2008R2].[Sales].[SalesPerson].[BusinessEntityID] as [B].[BusinessEntityID]) ORDERED FORWARD)    1    4    2    Clustered Index Seek    Clustered Index Seek    OBJECT:([AdventureWorks2008R2].[Sales].[Store].[PK_Store_BusinessEntityID] AS [A]), SEEK:([A].[BusinessEntityID]=[AdventureWorks2008R2].[Sales].[SalesPerson].[BusinessEntityID] as [B].[BusinessEntityID]) ORDERED FORWARD    [A].[BusinessEntityID], [A].[Name], [A].[SalesPersonID], [A].[Demographics], [A].[rowguid], [A].[ModifiedDate]    1    0.003125    0.0001581    4107    0.0058127    [A].[BusinessEntityID], [A].[Name], [A].[SalesPersonID], [A].[Demographics], [A].[rowguid], [A].[ModifiedDate]    NULL    PLAN_ROW    0    17

In this case the outer table is Sales.Store and the inner table is Sales.SalesPerson. As you can see there is a clustered index on column BusinessEntityID on Sales.SalesPerson table and Clustered Index on BusinessEntityID on Sales.Stores table. In this the SQL optimized determines that the optimal execution is to take SalesPerson as Outer table and Stores as inner table. Thus it performs an Clustered Index Scan on the Sales.SalesPerson table and Clustered Index Seek on the Stores table.

Notice that the index seek depends on A. BusinessEntityID which comes from the outer table of the join, which in this plan is SalesPerson. Each time we execute the index seek, A. BusinessEntityID has a different value. We refer to A. BusinessEntityID as a “correlated parameter”. If a nested loops join has correlated parameters, it is outputted in the plan as “OUTER REFERENCES.” This type of nested loops join where we have an index seek that depends on a correlated parameter is referred as an “index join.”

There are 3 variants of nested Join. In the simplest case, the search scans an entire table or index; this is called a naive nested loops join. If the search exploits an index, it is called an index nested loops join. If the index is built as part of the query plan (and destroyed upon completion of the query), it is called a temporary index nested loops join.

All these variants are considered by the query optimizer. A nested loops join is particularly effective if the outer input is quite small and the inner input is pre-indexed and quite large. In many small transactions, such as those affecting only a small set of rows, index nested loops joins are far superior to both merge joins and hash joins. In large queries, however, nested loops joins are often not the optimal choice.

 

Merge Join

The merge join requires that both inputs be sorted on the merge columns, which are defined by the equality (WHERE) clauses of the join predicate. The query optimizer typically scans an index, if one exists on the proper set of columns, or places a sort operator below the merge join. In rare cases, there may be multiple equality clauses, but the merge columns are taken from only some of the available equality clauses.

Because each input is sorted, the Merge Join operator gets a row from each input and compares them. For example, for inner join operations, the rows are returned if they are equal. If they are not equal, whichever row has the lower value is discarded and another row is obtained from that input. This process repeats until all rows have been processed.

The full operation is done as shown below.

Get First Row T1R from Table1
Get First Row T2R from Table2

While Not End of Table1 and Not End of Table2
  If T1R joins with T2R
      Get Next Row from Table2
      Returns (T1R, T2R)
  Else T1R < T2R
      Get NEXT Row from Table1
  Else
      Get Next Row from Table2
End Loop

the below statement in SQL Server Management Studio, and look at the execution plan.

set statistics profile on;
select * from Production.Product A
Inner Join Production.ProductInventory B On A.ProductID = B.ProductID

Rows    Executes    StmtText    StmtId    NodeId    Parent    PhysicalOp    LogicalOp    Argument    DefinedValues    EstimateRows    EstimateIO    EstimateCPU    AvgRowSize    TotalSubtreeCost    OutputList    Warnings    Type    Parallel    EstimateExecutions
1069    1    select * from Production.Product A  Inner Join Production.ProductInventory B On A.ProductID = B.ProductID    1    1    0    NULL    NULL    NULL    NULL    968.269    NULL    NULL    NULL    0.03063076    NULL    NULL    SELECT    0    NULL
1069    1      |--Merge Join(Inner Join, MERGE:([A].[ProductID])=([B].[ProductID]), RESIDUAL:([AdventureWorks2008R2].[Production].[ProductInventory].[ProductID] as [B].[ProductID]=[AdventureWorks2008R2].[Production].[Product].[ProductID] as [A].[ProductID]))    1    2    1    Merge Join    Inner Join    MERGE:([A].[ProductID])=([B].[ProductID]), RESIDUAL:([AdventureWorks2008R2].[Production].[ProductInventory].[ProductID] as [B].[ProductID]=[AdventureWorks2008R2].[Production].[Product].[ProductID] as [A].[ProductID])    NULL    968.269    0    0.009000127    274    0.03063076    [A].[ProductID], [A].[Name], [A].[ProductNumber], [A].[MakeFlag], [A].[FinishedGoodsFlag], [A].[Color], [A].[SafetyStockLevel], [A].[ReorderPoint], [A].[StandardCost], [A].[ListPrice], [A].[Size], [A].[SizeUnitMeasureCode], [A].[WeightUnitMeasureCode], [A].[Weight], [A].[DaysToManufacture], [A].[ProductLine], [A].[Class], [A].[Style], [A].[ProductSubcategoryID], [A].[ProductModelID], [A].[SellStartDate], [A].[SellEndDate], [A].[DiscontinuedDate], [A].[rowguid], [A].[ModifiedDate], [B].[ProductID], [B].[LocationID], [B].[Shelf], [B].[Bin], [B].[Quantity], [B].[rowguid], [B].[ModifiedDate]    NULL    PLAN_ROW    0    1
504    1           |--Clustered Index Scan(OBJECT:([AdventureWorks2008R2].[Production].[Product].[PK_Product_ProductID] AS [A]), ORDERED FORWARD)    1    3    2    Clustered Index Scan    Clustered Index Scan    OBJECT:([AdventureWorks2008R2].[Production].[Product].[PK_Product_ProductID] AS [A]), ORDERED FORWARD    [A].[ProductID], [A].[Name], [A].[ProductNumber], [A].[MakeFlag], [A].[FinishedGoodsFlag], [A].[Color], [A].[SafetyStockLevel], [A].[ReorderPoint], [A].[StandardCost], [A].[ListPrice], [A].[Size], [A].[SizeUnitMeasureCode], [A].[WeightUnitMeasureCode], [A].[Weight], [A].[DaysToManufacture], [A].[ProductLine], [A].[Class], [A].[Style], [A].[ProductSubcategoryID], [A].[ProductModelID], [A].[SellStartDate], [A].[SellEndDate], [A].[DiscontinuedDate], [A].[rowguid], [A].[ModifiedDate]    504    0.01201389    0.0007114    229    0.01272529    [A].[ProductID], [A].[Name], [A].[ProductNumber], [A].[MakeFlag], [A].[FinishedGoodsFlag], [A].[Color], [A].[SafetyStockLevel], [A].[ReorderPoint], [A].[StandardCost], [A].[ListPrice], [A].[Size], [A].[SizeUnitMeasureCode], [A].[WeightUnitMeasureCode], [A].[Weight], [A].[DaysToManufacture], [A].[ProductLine], [A].[Class], [A].[Style], [A].[ProductSubcategoryID], [A].[ProductModelID], [A].[SellStartDate], [A].[SellEndDate], [A].[DiscontinuedDate], [A].[rowguid], [A].[ModifiedDate]    NULL    PLAN_ROW    0    1
1069    1           |--Clustered Index Scan(OBJECT:([AdventureWorks2008R2].[Production].[ProductInventory].[PK_ProductInventory_ProductID_LocationID] AS [B]), ORDERED FORWARD)    1    4    2    Clustered Index Scan    Clustered Index Scan    OBJECT:([AdventureWorks2008R2].[Production].[ProductInventory].[PK_ProductInventory_ProductID_LocationID] AS [B]), ORDERED FORWARD    [B].[ProductID], [B].[LocationID], [B].[Shelf], [B].[Bin], [B].[Quantity], [B].[rowguid], [B].[ModifiedDate]    1069    0.007569444    0.0013329    54    0.008902345    [B].[ProductID], [B].[LocationID], [B].[Shelf], [B].[Bin], [B].[Quantity], [B].[rowguid], [B].[ModifiedDate]    NULL    PLAN_ROW    0    1

The merge join operation may be either a regular or a many-to-many operation. A many-to-many merge join uses a temporary table to store rows. If there are duplicate values from each input, one of the inputs will have to rewind to the start of the duplicates as each duplicate from the other input is processed. If a residual predicate is present, all rows that satisfy the merge predicate will evaluate the residual predicate, and only those rows that satisfy it will be returned.

Merge join itself is very fast, but it can be an expensive choice if sort operations are required. However, if the data volume is large and the desired data can be obtained presorted from existing B-tree indexes, merge join is often the fastest available join algorithm.

 

Hash Join

The hash join has two inputs: the build input and probe input. The query optimizer assigns these roles so that the smaller of the two inputs is the build input.

Hash joins are used for many types of set-matching operations: inner join; left, right, and full outer join; left and right semi-join; intersection; union; and difference. Moreover, a variant of the hash join can do duplicate removal and grouping (such as SUM(salary) GROUP BY department). These modifications use only one input for both the build and probe roles.

Similar to a merge join, a hash join can be used only if there is at least one equality (WHERE) clause in the join predicate. However, because joins are typically used to reassemble relationships, expressed with an equality predicate between a primary key and a foreign key, most joins have at least one equality clause. The set of columns in the equality predicate is called the hash key, because these are the columns that contribute to the hash function. Additional predicates are possible and are evaluated as residual predicates separate from the comparison of hash values. The hash key can be an expression, as long as it can be computed exclusively from columns in a single row. In grouping operations, the columns of the group by list are the hash key. In set operations such as intersection, as well as in the removal of duplicates, the hash key consists of all columns.

 

In-Memory Hash Join

The hash join first scans or computes the entire build input and then builds a hash table in memory. Each row is inserted into a hash bucket depending on the hash value computed for the hash key. If the entire build input is smaller than the available memory, all rows can be inserted into the hash table. This build phase is followed by the probe phase. The entire probe input is scanned or computed one row at a time, and for each probe row, the hash key’s value is computed, the corresponding hash bucket is scanned, and the matches are produced.

 

Grace Hash Join

If the build input does not fit in memory, a hash join proceeds in several steps. Each step has a build phase and probe phase. Initially, the entire build and probe inputs are consumed and partitioned (using a hash function on the hash keys) into multiple files. The number of such files is called the partitioning fan-out. Using the hash function on the hash keys guarantees that any two joining records must be in the same pair of files. Therefore, the task of joining two large inputs has been reduced to multiple, but smaller, instances of the same tasks. The hash join is then applied to each pair of partitioned files.

 

Recursive Hash Join

If the build input is so large that inputs for a standard external merge sorts would require multiple merge levels, multiple partitioning steps and multiple partitioning levels are required. If only some of the partitions are large, additional partitioning steps are used for only those specific partitions. In order to make all partitioning steps as fast as possible, large, asynchronous I/O operations are used so that a single thread can keep multiple disk drives busy.

Lets take a look at the execution plan of the below sql statement.

set statistics profile on;
select * from Sales.Customer C
Inner Join Sales.SalesOrderHeader O on C.CustomerID = O.CustomerID

You can see SQL optimizer chose Hash Join to retrieve data in this plan.

Rows    Executes    StmtText    StmtId    NodeId    Parent    PhysicalOp    LogicalOp    Argument    DefinedValues    EstimateRows    EstimateIO    EstimateCPU    AvgRowSize    TotalSubtreeCost    OutputList    Warnings    Type    Parallel    EstimateExecutions
31465    1    select * from Sales.Customer C  Inner Join Sales.SalesOrderHeader O on C.CustomerID = O.CustomerID    1    1    0    NULL    NULL    NULL    NULL    31098.69    NULL    NULL    NULL    1.455217    NULL    NULL    SELECT    0    NULL
31465    1      |--Hash Match(Inner Join, HASH:([C].[CustomerID])=([O].[CustomerID]))    1    2    1    Hash Match    Inner Join    HASH:([C].[CustomerID])=([O].[CustomerID])    NULL    31098.69    0    0.7886466    389    1.455217    [C].[CustomerID], [C].[PersonID], [C].[StoreID], [C].[TerritoryID], [C].[rowguid], [C].[ModifiedDate], [C].[AccountNumber], [O].[SalesOrderID], [O].[RevisionNumber], [O].[OrderDate], [O].[DueDate], [O].[ShipDate], [O].[Status], [O].[OnlineOrderFlag], [O].[PurchaseOrderNumber], [O].[AccountNumber], [O].[CustomerID], [O].[SalesPersonID], [O].[TerritoryID], [O].[BillToAddressID], [O].[ShipToAddressID], [O].[ShipMethodID], [O].[CreditCardID], [O].[CreditCardApprovalCode], [O].[CurrencyRateID], [O].[SubTotal], [O].[TaxAmt], [O].[Freight], [O].[Comment], [O].[rowguid], [O].[ModifiedDate], [O].[SalesOrderNumber], [O].[TotalDue]    NULL    PLAN_ROW    0    1
0    0           |--Compute Scalar(DEFINE:([C].[AccountNumber]=[AdventureWorks2008R2].[Sales].[Customer].[AccountNumber] as [C].[AccountNumber]))    1    3    2    Compute Scalar    Compute Scalar    DEFINE:([C].[AccountNumber]=[AdventureWorks2008R2].[Sales].[Customer].[AccountNumber] as [C].[AccountNumber])    [C].[AccountNumber]=[AdventureWorks2008R2].[Sales].[Customer].[AccountNumber] as [C].[AccountNumber]    19820    0    0.001982    56    0.1179369    [C].[CustomerID], [C].[PersonID], [C].[StoreID], [C].[TerritoryID], [C].[rowguid], [C].[ModifiedDate], [C].[AccountNumber]    NULL    PLAN_ROW    0    1
0    0           |    |--Compute Scalar(DEFINE:([C].[AccountNumber]=isnull('AW'+[AdventureWorks2008R2].[dbo].[ufnLeadingZeros]([AdventureWorks2008R2].[Sales].[Customer].[CustomerID] as [C].[CustomerID]),'')))    1    4    3    Compute Scalar    Compute Scalar    DEFINE:([C].[AccountNumber]=isnull('AW'+[AdventureWorks2008R2].[dbo].[ufnLeadingZeros]([AdventureWorks2008R2].[Sales].[Customer].[CustomerID] as [C].[CustomerID]),''))    [C].[AccountNumber]=isnull('AW'+[AdventureWorks2008R2].[dbo].[ufnLeadingZeros]([AdventureWorks2008R2].[Sales].[Customer].[CustomerID] as [C].[CustomerID]),'')    19820    0    0.001982    56    0.1159549    [C].[CustomerID], [C].[PersonID], [C].[StoreID], [C].[TerritoryID], [C].[AccountNumber], [C].[rowguid], [C].[ModifiedDate]    NULL    PLAN_ROW    0    1
19820    1           |         |--Clustered Index Scan(OBJECT:([AdventureWorks2008R2].[Sales].[Customer].[PK_Customer_CustomerID] AS [C]))    1    5    4    Clustered Index Scan    Clustered Index Scan    OBJECT:([AdventureWorks2008R2].[Sales].[Customer].[PK_Customer_CustomerID] AS [C])    [C].[CustomerID], [C].[PersonID], [C].[StoreID], [C].[TerritoryID], [C].[rowguid], [C].[ModifiedDate]    19820    0.09201389    0.021959    47    0.1139729    [C].[CustomerID], [C].[PersonID], [C].[StoreID], [C].[TerritoryID], [C].[rowguid], [C].[ModifiedDate]    NULL    PLAN_ROW    0    1
0    0           |--Compute Scalar(DEFINE:([O].[SalesOrderNumber]=[AdventureWorks2008R2].[Sales].[SalesOrderHeader].[SalesOrderNumber] as [O].[SalesOrderNumber], [O].[TotalDue]=[AdventureWorks2008R2].[Sales].[SalesOrderHeader].[TotalDue] as [O].[TotalDue]))    1    12    2    Compute Scalar    Compute Scalar    DEFINE:([O].[SalesOrderNumber]=[AdventureWorks2008R2].[Sales].[SalesOrderHeader].[SalesOrderNumber] as [O].[SalesOrderNumber], [O].[TotalDue]=[AdventureWorks2008R2].[Sales].[SalesOrderHeader].[TotalDue] as [O].[TotalDue])    [O].[SalesOrderNumber]=[AdventureWorks2008R2].[Sales].[SalesOrderHeader].[SalesOrderNumber] as [O].[SalesOrderNumber], [O].[TotalDue]=[AdventureWorks2008R2].[Sales].[SalesOrderHeader].[TotalDue] as [O].[TotalDue]    31465    0    0.0031465    341    0.548631    [O].[SalesOrderID], [O].[RevisionNumber], [O].[OrderDate], [O].[DueDate], [O].[ShipDate], [O].[Status], [O].[OnlineOrderFlag], [O].[PurchaseOrderNumber], [O].[AccountNumber], [O].[CustomerID], [O].[SalesPersonID], [O].[TerritoryID], [O].[BillToAddressID], [O].[ShipToAddressID], [O].[ShipMethodID], [O].[CreditCardID], [O].[CreditCardApprovalCode], [O].[CurrencyRateID], [O].[SubTotal], [O].[TaxAmt], [O].[Freight], [O].[Comment], [O].[rowguid], [O].[ModifiedDate], [O].[SalesOrderNumber], [O].[TotalDue]    NULL    PLAN_ROW    0    1
0    0                |--Compute Scalar(DEFINE:([O].[SalesOrderNumber]=isnull(N'SO'+CONVERT(nvarchar(23),[AdventureWorks2008R2].[Sales].[SalesOrderHeader].[SalesOrderID] as [O].[SalesOrderID],0),N'*** ERROR ***'), [O].[TotalDue]=isnull(([AdventureWorks2008R2].[Sales].[SalesOrderHeader].[SubTotal] as [O].[SubTotal]+[AdventureWorks2008R2].[Sales].[SalesOrderHeader].[TaxAmt] as [O].[TaxAmt])+[AdventureWorks2008R2].[Sales].[SalesOrderHeader].[Freight] as [O].[Freight],($0.0000))))    1    13    12    Compute Scalar    Compute Scalar    DEFINE:([O].[SalesOrderNumber]=isnull(N'SO'+CONVERT(nvarchar(23),[AdventureWorks2008R2].[Sales].[SalesOrderHeader].[SalesOrderID] as [O].[SalesOrderID],0),N'*** ERROR ***'), [O].[TotalDue]=isnull(([AdventureWorks2008R2].[Sales].[SalesOrderHeader].[SubTotal] as [O].[SubTotal]+[AdventureWorks2008R2].[Sales].[SalesOrderHeader].[TaxAmt] as [O].[TaxAmt])+[AdventureWorks2008R2].[Sales].[SalesOrderHeader].[Freight] as [O].[Freight],($0.0000)))    [O].[SalesOrderNumber]=isnull(N'SO'+CONVERT(nvarchar(23),[AdventureWorks2008R2].[Sales].[SalesOrderHeader].[SalesOrderID] as [O].[SalesOrderID],0),N'*** ERROR ***'), [O].[TotalDue]=isnull(([AdventureWorks2008R2].[Sales].[SalesOrderHeader].[SubTotal] as [O].[SubTotal]+[AdventureWorks2008R2].[Sales].[SalesOrderHeader].[TaxAmt] as [O].[TaxAmt])+[AdventureWorks2008R2].[Sales].[SalesOrderHeader].[Freight] as [O].[Freight],($0.0000))    31465    0    0.0031465    341    0.5454844    [O].[SalesOrderID], [O].[RevisionNumber], [O].[OrderDate], [O].[DueDate], [O].[ShipDate], [O].[Status], [O].[OnlineOrderFlag], [O].[SalesOrderNumber], [O].[PurchaseOrderNumber], [O].[AccountNumber], [O].[CustomerID], [O].[SalesPersonID], [O].[TerritoryID], [O].[BillToAddressID], [O].[ShipToAddressID], [O].[ShipMethodID], [O].[CreditCardID], [O].[CreditCardApprovalCode], [O].[CurrencyRateID], [O].[SubTotal], [O].[TaxAmt], [O].[Freight], [O].[TotalDue], [O].[Comment], [O].[rowguid], [O].[ModifiedDate]    NULL    PLAN_ROW    0    1
31465    1                     |--Clustered Index Scan(OBJECT:([AdventureWorks2008R2].[Sales].[SalesOrderHeader].[PK_SalesOrderHeader_SalesOrderID] AS [O]))    1    14    13    Clustered Index Scan    Clustered Index Scan    OBJECT:([AdventureWorks2008R2].[Sales].[SalesOrderHeader].[PK_SalesOrderHeader_SalesOrderID] AS [O])    [O].[SalesOrderID], [O].[RevisionNumber], [O].[OrderDate], [O].[DueDate], [O].[ShipDate], [O].[Status], [O].[OnlineOrderFlag], [O].[PurchaseOrderNumber], [O].[AccountNumber], [O].[CustomerID], [O].[SalesPersonID], [O].[TerritoryID], [O].[BillToAddressID], [O].[ShipToAddressID], [O].[ShipMethodID], [O].[CreditCardID], [O].[CreditCardApprovalCode], [O].[CurrencyRateID], [O].[SubTotal], [O].[TaxAmt], [O].[Freight], [O].[Comment], [O].[rowguid], [O].[ModifiedDate]    31465    0.5075694    0.0347685    305    0.542338    [O].[SalesOrderID], [O].[RevisionNumber], [O].[OrderDate], [O].[DueDate], [O].[ShipDate], [O].[Status], [O].[OnlineOrderFlag], [O].[PurchaseOrderNumber], [O].[AccountNumber], [O].[CustomerID], [O].[SalesPersonID], [O].[TerritoryID], [O].[BillToAddressID], [O].[ShipToAddressID], [O].[ShipMethodID], [O].[CreditCardID], [O].[CreditCardApprovalCode], [O].[CurrencyRateID], [O].[SubTotal], [O].[TaxAmt], [O].[Freight], [O].[Comment], [O].[rowguid], [O].[ModifiedDate]    NULL    PLAN_ROW    0    1

 

Note: If the build input is larger but not a lot larger than the available memory, elements of in-memory hash join and grace hash join are combined in a single step, producing a hybrid hash join.

It is not always possible during optimization to determine which hash join will be used. Therefore,  SQL Server starts using an in-memory hash join and gradually transitions to grace hash join, and recursive hash join, depending on the size of the build input.

If the optimizer anticipates wrongly which of the two inputs is smaller and, therefore, should have been the build input, the build and probe roles are reversed dynamically. The hash join makes sure that it uses the smaller overflow file as build input. This technique is called role reversal.

Conclusion

SQL Server Optimizer determines the best, optimized plan for executing the query. The optimizer evaluate the  indices on the table columns, number of rows in the tables , type of joins  etc. when it chose the best plan. 

Nested loop is generally chosen for smaller tables and if it is possible to do Index seek on the inner table to ensure better performance.

Merge Join is considered to be more efficient for large tables with the keys columns sorted. It only need to read the keys once, and does not require lot of CPU cycles.  

Hash Join is also better suited for large tables. This requires less IO, but need more CPU and requires lot of memory.

That being said, it is possible  to override the optimizer’s choice of the join by using the OPTION hint. However, the optimizer is very smart, it does not make very many bad choices. So use caution when you use the  OPTION hint in your queries.  Updating statistics to fix your indexes help the optimizer to choose  the right Join.