{"id":1480,"date":"2012-12-22T10:00:29","date_gmt":"2012-12-22T15:00:29","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=1480"},"modified":"2014-11-13T13:50:29","modified_gmt":"2014-11-13T18:50:29","slug":"a-join-a-day-the-sort-merge-join","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/1480\/a-join-a-day-the-sort-merge-join\/","title":{"rendered":"A Join A Day \u2013 The Sort Merge Join"},"content":{"rendered":"<div>\n<h3>Introduction<\/h3>\n<p>\nThis is the twenty-second post in my <a href=\"http:\/\/sqlity.net\/en\/1146\/a-join-a-day-introduction\/\">A Join A Day<\/a> series about SQL Server Joins. Make sure to let me know how I am doing or ask your burning join related questions by leaving a comment below.\n<\/p>\n<p>\nThe Sort Merge Join algorithm is the fastest of them all. However, there is a caveat. The algorithm is actually a two-step process. The first step is to sort both inputs in the same order. The second step is the Merge Join step. Here the rows from both inputs get matched together. The Merge part is the blazingly fast part. So if both inputs are sorted already because of an index or because of another sort requirement in the same query, the Sort Merge Join algorithm is the first choice. But if the inputs are not sorted, it rarely makes sense for SQL Server to first sort them.\n<\/p>\n<h3>The Sort Merge Join Algorithm<\/h3>\n<p>\n<a href=\"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/12\/merge-join-icon.png\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/12\/merge-join-icon.png\" alt=\"merge join icon\" title=\"merge join icon\" width=\"169\" height=\"103\" class=\"aligncenter size-full wp-image-1482\" srcset=\"https:\/\/sqlity.net\/wp-content\/uploads\/2012\/12\/merge-join-icon.png 169w, https:\/\/sqlity.net\/wp-content\/uploads\/2012\/12\/merge-join-icon-150x91.png 150w\" sizes=\"auto, (max-width: 169px) 100vw, 169px\" \/><\/a>\n<\/p>\n<p>\nSQL Server actually implements only half of this algorithm directly. For the actual join the Merge Join operator is used. However, that operator cannot sort the inputs itself. It instead requires the inputs to be presorted. The sort can happen either by using dedicated sort operators in the execution plan or by utilizing existing indexes. The optimizer makes sure to only use the Merge Join operator if the inputs are indeed sorted.\n<\/p>\n<p>\nThe Merge Join algorithm works like this: The first row of each input is read. This primes the algorithm. After that a loop is executed:<\/p>\n<ol>\n<li>Check if the rows match. If they do produce output row<\/li>\n<li>If the value in input A is smaller than the value in input B, attempt to read next row from input A and skip next step<\/li>\n<li>Attempt to read next row from Input B<\/li>\n<li>If read attempt was successful, loop<\/li>\n<\/ol>\n<p>\nAgain, this is a little simplified. This version requires the values in the join column(s) to be unique. If that is not the case, special handling of multi-row matches has to be introduced, but even then the process is still very similar to the above.\n<\/p>\n<p>\nThe following GIF animation shows the process step by step:\n<\/p>\n<p>\n<a href=\"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/12\/merge-join-algorithm.gif\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/12\/merge-join-algorithm.gif\" alt=\"merge join algorithm\" title=\"merge join algorithm\" width=\"800\" height=\"600\" class=\"aligncenter size-full wp-image-1484\" srcset=\"https:\/\/sqlity.net\/wp-content\/uploads\/2012\/12\/merge-join-algorithm.gif 800w, https:\/\/sqlity.net\/wp-content\/uploads\/2012\/12\/merge-join-algorithm-300x225.gif 300w, https:\/\/sqlity.net\/wp-content\/uploads\/2012\/12\/merge-join-algorithm-150x112.gif 150w\" sizes=\"auto, (max-width: 800px) 100vw, 800px\" \/><\/a>\n<\/p>\n<p>\nThis algorithm is so powerful, because SQL Server has to read and step through both inputs only once &ndash; in lockstep.\n<\/p>\n<p>\nLet's look at the numbers by reusing the tables we created in yesterday's post about the <a href=\"http:\/\/sqlity.net\/en\/1471\/a-join-a-day-the-nested-loops-join\/\">Nested Loops Join algorithm<\/a>. Here are the page and row counts for both tables again:\n<\/p>\n<p>\n<a href=\"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/12\/create-example-tables.jpg\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/12\/create-example-tables.jpg\" alt=\"create example tables\" title=\"create example tables\" width=\"1093\" height=\"573\" class=\"aligncenter size-full wp-image-1474\" srcset=\"https:\/\/sqlity.net\/wp-content\/uploads\/2012\/12\/create-example-tables.jpg 1093w, https:\/\/sqlity.net\/wp-content\/uploads\/2012\/12\/create-example-tables-300x157.jpg 300w, https:\/\/sqlity.net\/wp-content\/uploads\/2012\/12\/create-example-tables-1024x536.jpg 1024w, https:\/\/sqlity.net\/wp-content\/uploads\/2012\/12\/create-example-tables-150x78.jpg 150w\" sizes=\"auto, (max-width: 1093px) 100vw, 1093px\" \/><\/a>\n<\/p>\n<p>\nWe are going to use the same query again, just replacing the <span class=\"tt\">LOOP<\/span> hint with a <span class=\"tt\">MERGE<\/span> hint:\n<\/p>\n<div>\n[sql]\nSET STATISTICS IO ON;<br \/>\nGO<br \/>\nSELECT *<br \/>\nFROM dbo.Tbl100 A<br \/>\nINNER MERGE JOIN dbo.Tbl10 B<br \/>\nON A.Val = B.Val;<br \/>\nGO<br \/>\nSET STATISTICS IO OFF;<br \/>\n[\/sql]\n<\/div>\n<p>\nThe execution plan for this query shows the required two Sort operators, one for each input to the Merge Join operator:\n<\/p>\n<p>\n<a href=\"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/12\/heap-merge-join-execution-plan.jpg\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/12\/heap-merge-join-execution-plan.jpg\" alt=\"merge join execution plan with two heaps\" title=\"merge join execution plan with two heaps\" width=\"1093\" height=\"573\" class=\"aligncenter size-full wp-image-1483\" srcset=\"https:\/\/sqlity.net\/wp-content\/uploads\/2012\/12\/heap-merge-join-execution-plan.jpg 1093w, https:\/\/sqlity.net\/wp-content\/uploads\/2012\/12\/heap-merge-join-execution-plan-300x157.jpg 300w, https:\/\/sqlity.net\/wp-content\/uploads\/2012\/12\/heap-merge-join-execution-plan-1024x536.jpg 1024w, https:\/\/sqlity.net\/wp-content\/uploads\/2012\/12\/heap-merge-join-execution-plan-150x78.jpg 150w\" sizes=\"auto, (max-width: 1093px) 100vw, 1093px\" \/><\/a>\n<\/p>\n<p>\nThe tool tip for the two tables shows that each one got scanned once as we expected. Looking at the <span class=\"tt\">SET STATISTICS IO ON;<\/span> output however unveils a surprise:\n<\/p>\n<div>\n[sourcecode]\nTable 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.<br \/>\nTable 'Tbl10'. Scan count 1, logical reads 10, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.<br \/>\nTable 'Tbl100'. Scan count 1, logical reads 100, physical reads 0, read-ahead reads 1, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.<br \/>\n[\/sourcecode]\n<\/div>\n<p>\nThe two table's read counts show that they each got scanned once only as we expected. But what is the story with that Worktable?\n<\/p>\n<p>\nIf SQL Server does not know for sure that at least one of the two tables is unique in the join column(s) it has to prepare for the possibility of a many to many relationship. Because the merge algorithm is designed to touch every record in each table only once, it cannot handle a many to many relationship. To get around that SQL Server stores all rows of the second input that had a match in a worktable in tempdb until they are not needed anymore. A record from the second input does not need to be stored any longer, when a different value on the first input has been read. Every time the same value on the first input is repeated, SQL Server \"rewinds\" the stream by using this work table.\n<\/p>\n<p>\nBecause the data in our two example tables is actually unique, there was no data read from the work table, hence the 0 reads in the statistics. But keep this in mind when you have a query that is using a merge join. For each section of identical values, SQL Server actually executes a loop join, first writing and then multiple times reading from the additional storage in tempdb. If you have large islands of identical values in your data this can potentially be very expensive.\n<\/p>\n<p>\nThe Merge Join operator has a many-to-many property that you can see in the execution plan tooltip window. If this property is set to \"true\" it means that SQL Server is expecting duplicate rows on both inputs.\n<\/p>\n<h3>Sort Merge Join Strengths and Weaknesses<\/h3>\n<p>\nAs said before, the Sort Merge Join has two parts. The first part is the Sort which can be quite expensive. However, if the data is sorted already, the second part which is handled by the Merge Join operator has the following properties:<\/p>\n<ul>\n<li>+ There is no setup cost.<\/li>\n<li>+ It does not require memory.<\/li>\n<li>+ The first rows are returned immediately, as soon as a match is found.<\/li>\n<li>- At least one column in the join condition needs to use an equality comparison.<\/li>\n<li>- If there is a many to many relationship in the data, a tempdb work table is created and used to provide a rewind-ability of the data potentially hurting performance severely.<\/li>\n<\/ul>\n<p>\nThe equality comparison is required to be able to sort both inputs the same way. However, there is a special case. SQL Server can use the Merge Join operator when a nonequi full outer join is required. In that case the entire first input is copied into the work table. Then a nested loops algorithm is executed between the second input and the work table. Each row in the worktable that had a match is marked. In the end an additional scan of the worktable returns all non-matched rows. This is a very expensive process hidden under a seemingly fast operator. Be aware of this.\n<\/p>\n<h3>Summary<\/h3>\n<p>\nThe Merge Join algorithm is certainly the most efficient algorithm available to SQL Server. However, it requires both inputs to be sorted the same way. The cost of sorting is usually too high to make the use of this algorithm worthwhile, unless the data is sorted already or has to be sorted anyway for example because of an <span class=\"tt\">ORDER BY<\/span> clause in the query.\n<\/p>\n<p>\nYou also need to be aware of the fact that even the potential of a many to many relationship in the data causes a worktable to be created and filled. If there are actually any duplicate values, the data in the worktable is reread as often as necessary which can be detrimental to the performance of the query.\n<\/p>\n<h3>A Join A Day<\/h3>\n<p>\nThis post is part of my December 2012 \"A Join A Day\" blog post series. You can find the table of contents with all posts published so far in the introductory post: <a href=\"http:\/\/sqlity.net\/en\/1146\/a-join-a-day-introduction\/\">A Join A Day \u2013 Introduction<\/a>. Check back there frequently throughout the month.\n<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>This article introduces the Sort Merge Join algorithm. It shows its strengths and weaknesses to help you identify query situations for which the Merge Join operator in the execution plan is an appropriate choice.<\/p>\n<p> <a href=\"https:\/\/sqlity.net\/en\/1480\/a-join-a-day-the-sort-merge-join\/\">[more&#8230;]<\/a><\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_hide_featured_image":false,"_jetpack_featured_image_duplicate_dismissed":0,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[28,5,19,27,14],"tags":[],"class_list":["post-1480","post","type-post","status-publish","format-standard","hentry","category-a-join-a-day","category-general","category-performance","category-series","category-sql-server-internals"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v28.9 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>A Join A Day \u2013 The Sort Merge Join - sqlity.net<\/title>\n<meta name=\"description\" content=\"This article introduces the Sort Merge Join algorithm. It shows its strengths and weaknesses to help you identify query situations for which the Merge Join operator in the execution plan is an appropriate choice.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/sqlity.net\/en\/1480\/a-join-a-day-the-sort-merge-join\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"A Join A Day \u2013 The Sort Merge Join - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"This article introduces the Sort Merge Join algorithm. It shows its strengths and weaknesses to help you identify query situations for which the Merge Join operator in the execution plan is an appropriate choice.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/1480\/a-join-a-day-the-sort-merge-join\/\" \/>\n<meta property=\"og:site_name\" content=\"sqlity.net\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/sqlity.net\" \/>\n<meta property=\"article:published_time\" content=\"2012-12-22T15:00:29+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T18:50:29+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/12\/merge-join-icon.png\" \/>\n<meta name=\"author\" content=\"Sebastian Meine\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@sqlity\" \/>\n<meta name=\"twitter:site\" content=\"@sqlity\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Sebastian Meine\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"6 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1480\\\/a-join-a-day-the-sort-merge-join\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1480\\\/a-join-a-day-the-sort-merge-join\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/c2bb48ba26fa133f118feb33f1550c88\"},\"headline\":\"A Join A Day \u2013 The Sort Merge Join\",\"datePublished\":\"2012-12-22T15:00:29+00:00\",\"dateModified\":\"2014-11-13T18:50:29+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1480\\\/a-join-a-day-the-sort-merge-join\\\/\"},\"wordCount\":1253,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1480\\\/a-join-a-day-the-sort-merge-join\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2012\\\/12\\\/merge-join-icon.png\",\"articleSection\":[\"A Join A Day\",\"General\",\"Performance\",\"Series\",\"SQL Server Internals\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/1480\\\/a-join-a-day-the-sort-merge-join\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1480\\\/a-join-a-day-the-sort-merge-join\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1480\\\/a-join-a-day-the-sort-merge-join\\\/\",\"name\":\"A Join A Day \u2013 The Sort Merge Join - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1480\\\/a-join-a-day-the-sort-merge-join\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1480\\\/a-join-a-day-the-sort-merge-join\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2012\\\/12\\\/merge-join-icon.png\",\"datePublished\":\"2012-12-22T15:00:29+00:00\",\"dateModified\":\"2014-11-13T18:50:29+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/c2bb48ba26fa133f118feb33f1550c88\"},\"description\":\"This article introduces the Sort Merge Join algorithm. It shows its strengths and weaknesses to help you identify query situations for which the Merge Join operator in the execution plan is an appropriate choice.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1480\\\/a-join-a-day-the-sort-merge-join\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/1480\\\/a-join-a-day-the-sort-merge-join\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1480\\\/a-join-a-day-the-sort-merge-join\\\/#primaryimage\",\"url\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2012\\\/12\\\/merge-join-icon.png\",\"contentUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2012\\\/12\\\/merge-join-icon.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1480\\\/a-join-a-day-the-sort-merge-join\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"A Join A Day \u2013 The Sort Merge Join\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\",\"name\":\"sqlity.net\",\"description\":\"Quality for SQL\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/sqlity.net\\\/en\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/c2bb48ba26fa133f118feb33f1550c88\",\"name\":\"Sebastian Meine\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"caption\":\"Sebastian Meine\"},\"sameAs\":[\"http:\\\/\\\/sqlity.net\",\"https:\\\/\\\/x.com\\\/sqlity\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"A Join A Day \u2013 The Sort Merge Join - sqlity.net","description":"This article introduces the Sort Merge Join algorithm. It shows its strengths and weaknesses to help you identify query situations for which the Merge Join operator in the execution plan is an appropriate choice.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/sqlity.net\/en\/1480\/a-join-a-day-the-sort-merge-join\/","og_locale":"en_US","og_type":"article","og_title":"A Join A Day \u2013 The Sort Merge Join - sqlity.net","og_description":"This article introduces the Sort Merge Join algorithm. It shows its strengths and weaknesses to help you identify query situations for which the Merge Join operator in the execution plan is an appropriate choice.","og_url":"https:\/\/sqlity.net\/en\/1480\/a-join-a-day-the-sort-merge-join\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2012-12-22T15:00:29+00:00","article_modified_time":"2014-11-13T18:50:29+00:00","og_image":[{"url":"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/12\/merge-join-icon.png","type":"","width":"","height":""}],"author":"Sebastian Meine","twitter_card":"summary_large_image","twitter_creator":"@sqlity","twitter_site":"@sqlity","twitter_misc":{"Written by":"Sebastian Meine","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/sqlity.net\/en\/1480\/a-join-a-day-the-sort-merge-join\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/1480\/a-join-a-day-the-sort-merge-join\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/c2bb48ba26fa133f118feb33f1550c88"},"headline":"A Join A Day \u2013 The Sort Merge Join","datePublished":"2012-12-22T15:00:29+00:00","dateModified":"2014-11-13T18:50:29+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/1480\/a-join-a-day-the-sort-merge-join\/"},"wordCount":1253,"commentCount":0,"image":{"@id":"https:\/\/sqlity.net\/en\/1480\/a-join-a-day-the-sort-merge-join\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/12\/merge-join-icon.png","articleSection":["A Join A Day","General","Performance","Series","SQL Server Internals"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/1480\/a-join-a-day-the-sort-merge-join\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/1480\/a-join-a-day-the-sort-merge-join\/","url":"https:\/\/sqlity.net\/en\/1480\/a-join-a-day-the-sort-merge-join\/","name":"A Join A Day \u2013 The Sort Merge Join - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/sqlity.net\/en\/1480\/a-join-a-day-the-sort-merge-join\/#primaryimage"},"image":{"@id":"https:\/\/sqlity.net\/en\/1480\/a-join-a-day-the-sort-merge-join\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/12\/merge-join-icon.png","datePublished":"2012-12-22T15:00:29+00:00","dateModified":"2014-11-13T18:50:29+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/c2bb48ba26fa133f118feb33f1550c88"},"description":"This article introduces the Sort Merge Join algorithm. It shows its strengths and weaknesses to help you identify query situations for which the Merge Join operator in the execution plan is an appropriate choice.","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/1480\/a-join-a-day-the-sort-merge-join\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/1480\/a-join-a-day-the-sort-merge-join\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/sqlity.net\/en\/1480\/a-join-a-day-the-sort-merge-join\/#primaryimage","url":"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/12\/merge-join-icon.png","contentUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/12\/merge-join-icon.png"},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/1480\/a-join-a-day-the-sort-merge-join\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"A Join A Day \u2013 The Sort Merge Join"}]},{"@type":"WebSite","@id":"https:\/\/sqlity.net\/en\/#website","url":"https:\/\/sqlity.net\/en\/","name":"sqlity.net","description":"Quality for SQL","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/sqlity.net\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/c2bb48ba26fa133f118feb33f1550c88","name":"Sebastian Meine","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","caption":"Sebastian Meine"},"sameAs":["http:\/\/sqlity.net","https:\/\/x.com\/sqlity"]}]}},"jetpack_publicize_connections":[],"jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2wXuw-nS","jetpack-related-posts":[],"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/1480","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/comments?post=1480"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/1480\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=1480"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=1480"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=1480"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}