SQL SERVER – Explanation SQL SERVER Merge Join

The Merge Join transformation provides an output that is generated by joining two sorted data sets using a FULL, LEFT, or INNER join. The Merge Join transformation requires that both inputs be sorted and that the joined columns have matching meta-data. User cannot join a column that has a numeric data type with a column that has a character data type. If the data has a string data type, the length of the column in the second input must be less than or equal to the length of the column in the first input with which it is merged.

USE pubs
GO
SELECT a.au_id
FROM authors a
JOIN titleauthor b ON a.au_id = b.au_id
OPTION (MERGE JOIN)
GO

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. Because the query optimizer usually selects the best execution plan for a given select statement, it is not necessary to enforce the desirable join type, but sometimes it can be useful. User can enforce the desirable join type by using the OPTION clause.

Following query will be benefited by MERGE JOIN because authors and titleauthor both has primary key index on au_id. Due to Primary Key on au_id it is physically sorted by au_id.

This article is the in-depth analysis of the MERGE JOIN. Very informative.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

9 thoughts on “SQL SERVER – Explanation SQL SERVER Merge Join

  1. Hey Pinal
    congratulations for the MVP

    now in sql server 2005
    can we merge the output of 2 queries or 2 tables using the merge join option
    or it exists only in SSIS
    if it exists in sql server 2005 please show me a valid example
    the one u showed above in giving error

    Like

  2. hi
    i have 2 question:
    1. “If the data has a string data type, the length of the column in the second input must be less than or equal to the length of the column in the first input with which it is merged” -> why?
    2. The Merge Join Component is different from the Lookup Component in that it does not support OLE DB
    or ADO.NET connections to the reference dataset.->why?
    Pls hepl me.
    thanks!

    Like

  3. I have 2 database having the same table and columns
    database is expressed as follows

    First Database is :

    create database one
    use one
    create table enter
    (
    AreaAllotted int,
    Location varchar(200),
    State varchar(200)
    )
    create table Registration
    (
    RegID varchar(50),
    FName varchar(50),
    LName varchar(50),
    Address varchar(50)
    )
    Second Database is :

    create database two
    use two
    create table enter
    (
    AreaAllotted int,
    Location varchar(200),
    State varchar(200)
    )
    create table Registration
    (
    RegID varchar(50),
    FName varchar(50),
    LName varchar(50),
    Address varchar(50)
    )

    I want whenever i insert into enter table of database one it should fire a trigger that will insert in the Registration table .Database named two have its own data as well.
    Both of the database has its own trigger on Enter table that will insert the value into Registration table.I have done thall these tasks yet
    but the actual problem is i want to merge both the database .if enter table of database one has like this

    AreaAllotted Location State
    125 Bhilai C.G

    and Registration table
    RegId FName LName Address
    R1 SS PP abc

    and second database has value like this

    AreaAllotted Location State
    189 Durgapur Calcutta

    and Registration table
    RegId FName LName Address
    R11 tt jj xyz

    after merging
    so the result must be in this way

    Enter table
    AreaAllotted Location State
    125 Bhilai C.G
    189 Durgapur Calcutta

    and Registration table
    RegId FName LName Address
    R1 SS PP abc
    R11 tt jj xyz

    Pls help me in this problem……………………

    Like

  4. Pingback: SQL SERVER – MERGE or INSERT, UPDATE, DELETE – Quiz – Puzzle – 19 of 31 « SQL Server Journey with SQL Authority

  5. Pingback: SQL SERVER – Weekly Series – Memory Lane – #027 | SQL Server Journey with SQL Authority

    • The WHERE clause is specified by the ON part. Note that this also satisfies the requirement that the WHERE clause is an equality.

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s