Merge Join Internals in SQL Server

Without additional optimizations, SQL Server's Merge Join algorithm is the fastest physical join operator available. In this episode we dive into the internals to understand how the merge join algorithm works, as well as what it means for our query performance.
Subscribe and turn on notifications to never miss a weekly video: czcams.com/users/DataWithBer...
Related blog post about Merge Joins:
bertwagner.com/2018/12/18/vis...
Be sure to check out part 1 on nested loops joins: • The Importance of Nest...
And part 3 on hash match joins: • Hash Match Join Intern...
The pessimistic costing of merge joins:
dba.stackexchange.com/questio...
Going further in-depth with merge joins:
sqlserverfast.com/epr/merge-j...
Follow me on Twitter:
/ bertwagner

[@VicFroman]

The you. I have worked with SQL for 20 years and developed in them for 5ish and have never been able to understand execution plans. This was very well explained

[@zanonilabuschagne7628]

Thanks for this one Bert. This "series" on join internals is really useful!! p.s. Have a great festive period !!

[@thomasmiller8289]

Wow, this is great, instant subscribe for this series!

[@Das.Kleine.Krokodil]

Очень доходчиво, спасибо!

[@tuval012]

Thank you! Good explanation

[@janasoliman9773]

Thank you that was so useful!

[@jeffh6846]

How would it work if you put a many to one relationship where the duplicate is in the first input but not in the second?

[@ahadbutt7481]

Love your videos!
What background music are you using btw?

[@andrew5407]

Excellent

[@user-od5ny6rv9n]

Thanks🎉

[@AmritanshDiwakar]

How can I create a scenario for the optimizer to select merge join? can anyone please tell.
I have already tried the below scenario:
--> 2 tables with 7 lakh records each
--> both tables have clustered index on the joined column

[@user-vg7jz1sz5k]

what is sound track plays?

[@rajkumar-oy3ni]

your content and explanation is good but for further videos can you please remove background music, difficult focus on what you said

[@tsunningwah3471]

可能，你，那麼，那麼，那麼，那麼，你，嗎你們，你，m