{"id":11883,"date":"2019-04-29T07:06:58","date_gmt":"2019-04-29T07:06:58","guid":{"rendered":"https:\/\/dbtut.com\/?p=11883"},"modified":"2019-04-29T07:06:58","modified_gmt":"2019-04-29T07:06:58","slug":"create-index-on-join-columns","status":"publish","type":"post","link":"https:\/\/dbtut.com\/index.php\/2019\/04\/29\/create-index-on-join-columns\/","title":{"rendered":"Create Index On Join Columns"},"content":{"rendered":"

Before you start reading this article, I suggest you read the article “Index Concept and Performance Effect on SQL Server<\/a>” for more detailed information about indexes.<\/p>\n

If you have queries that join the two tables on the same column, you should create indexes on the join columns. For example, in the AdventureWorks2014 database, let’s take a look at the execution plan of the following query.<\/p>\n

To take the Execution Plan, you must click on the following black frame in the query screen on the SSMS. For detailed information about the Execution plan, please read the article “What is Execution Plan On SQL Server<\/a>“.<\/p>\n

\"\"<\/p>\n

SELECT DISTINCT P.ProductID, P.Name\r\nFROM Sales.SalesOrderDetail AS SOD\r\n    JOIN Production.Product AS P \r\n    ON SOD.ProductID = p.ProductID \r\nWHERE P.ProductID = 707;\r\n<\/pre>\n
\"\"<\/p>\n
If you take the execution plan for the above query in the AdventureWorks database, the result may not appear as above. First, you must delete the index that is created on the ProductID in the SalesOrderDetail table, and then take the execution plan again.<\/p>\n
As seen in the Execution plan, the Clustered Index Scan process has been performed despite reading a single record for the SalesOrderDetail table. Because there is no index on the join column. I have deleted the index above to show the importance of having index on the Join column.<\/p>\n
Now re-create the index as below and take the query’s execution plan again.<\/p>\n
CREATE NONCLUSTERED INDEX [IX_ProductID] ON [Sales].[SalesOrderDetail]\r\n(\r\n\t[ProductID] ASC\r\n)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, \r\nONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)\r\n<\/pre>\n
\"\"<\/p>\n
As you can see, the Clustered Index Scan process has returned to Index Seek.<\/p>\n
You may want to read the article “Statistic Concept and Performance Effect on SQL Server<\/a>” to learn more about what is “Clustered Index Scan” and “Index Seek”.<\/p>\n\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"excerpt":{"rendered":"
Before you start reading this article, I suggest you read the article “Index Concept and Performance Effect on SQL Server” for more detailed information about indexes. If you have queries that join the two tables on the same column, you should create indexes on the join columns. For example, in the AdventureWorks2014 database, let’s take …<\/p>\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"author":1,"featured_media":11890,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"footnotes":""},"categories":[3],"tags":[3672,3671,3673,3677,3678,3668,3670,3676,3669,3674,3675],"class_list":["post-11883","post","type-post","status-publish","format-standard","has-post-thumbnail","","category-mssql","tag-create-join-index","tag-creating-indexes-on-columns-used-in-join","tag-creating-indexes-on-columns-used-to-join-tables","tag-how-to-use-index-in-inner-join-sql-server","tag-index-all-the-predicates-in-join","tag-index-on-join","tag-indexes-on-columns-used-in-join","tag-indexing-inner-join","tag-make-sure-all-join-columns-are-indexed","tag-non-clustered-index-on-join-columns","tag-use-index-on-join"],"aioseo_notices":[],"a3_pvc":{"activated":true,"total_views":1524,"today_views":0},"yoast_head":"\nCreate Index On Join Columns - Database Tutorials<\/title>\n<meta name=\"description\" content=\"Create Index On Join Columns\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/dbtut.com\/index.php\/2019\/04\/29\/create-index-on-join-columns\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Create Index On Join Columns - Database Tutorials\" \/>\n<meta property=\"og:description\" content=\"Create Index On Join Columns\" \/>\n<meta property=\"og:url\" content=\"https:\/\/dbtut.com\/index.php\/2019\/04\/29\/create-index-on-join-columns\/\" \/>\n<meta property=\"og:site_name\" content=\"Database Tutorials\" \/>\n<meta property=\"article:published_time\" content=\"2019-04-29T07:06:58+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/04\/Ads\u0131z-57.png\" \/>\n\t<meta property=\"og:image:width\" content=\"552\" \/>\n\t<meta property=\"og:image:height\" content=\"327\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"dbtut\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"dbtut\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/04\/29\/create-index-on-join-columns\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/04\/29\/create-index-on-join-columns\/\"},\"author\":{\"name\":\"dbtut\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408\"},\"headline\":\"Create Index On Join Columns\",\"datePublished\":\"2019-04-29T07:06:58+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/04\/29\/create-index-on-join-columns\/\"},\"wordCount\":258,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/04\/29\/create-index-on-join-columns\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/04\/Ads\u0131z-57.png\",\"keywords\":[\"CREATE JOIN INDEX\",\"creating indexes on columns used in join\",\"Creating indexes on columns used to join tables\",\"how to use index in inner join sql server\",\"index all the predicates in join\",\"Index on join\",\"indexes on columns used in join\",\"indexing inner join\",\"Make Sure All JOIN Columns are Indexed\",\"non clustered index on join columns\",\"use index on join\"],\"articleSection\":[\"MSSQL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2019\/04\/29\/create-index-on-join-columns\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/04\/29\/create-index-on-join-columns\/\",\"url\":\"https:\/\/dbtut.com\/index.php\/2019\/04\/29\/create-index-on-join-columns\/\",\"name\":\"Create Index On Join Columns - Database Tutorials\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/04\/29\/create-index-on-join-columns\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/04\/29\/create-index-on-join-columns\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/04\/Ads\u0131z-57.png\",\"datePublished\":\"2019-04-29T07:06:58+00:00\",\"description\":\"Create Index On Join Columns\",\"breadcrumb\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/04\/29\/create-index-on-join-columns\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2019\/04\/29\/create-index-on-join-columns\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/04\/29\/create-index-on-join-columns\/#primaryimage\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/04\/Ads\u0131z-57.png\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/04\/Ads\u0131z-57.png\",\"width\":552,\"height\":327},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/04\/29\/create-index-on-join-columns\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/dbtut.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Create Index On Join Columns\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/dbtut.com\/#website\",\"url\":\"https:\/\/dbtut.com\/\",\"name\":\"Database Tutorials\",\"description\":\"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux\",\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/dbtut.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/dbtut.com\/#organization\",\"name\":\"dbtut\",\"url\":\"https:\/\/dbtut.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"width\":223,\"height\":36,\"caption\":\"dbtut\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408\",\"name\":\"dbtut\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g\",\"caption\":\"dbtut\"},\"description\":\"We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.\",\"sameAs\":[\"http:\/\/NurullahCAKIR\"],\"url\":\"https:\/\/dbtut.com\/index.php\/author\/dbtut\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Create Index On Join Columns - Database Tutorials","description":"Create Index On Join Columns","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:\/\/dbtut.com\/index.php\/2019\/04\/29\/create-index-on-join-columns\/","og_locale":"en_US","og_type":"article","og_title":"Create Index On Join Columns - Database Tutorials","og_description":"Create Index On Join Columns","og_url":"https:\/\/dbtut.com\/index.php\/2019\/04\/29\/create-index-on-join-columns\/","og_site_name":"Database Tutorials","article_published_time":"2019-04-29T07:06:58+00:00","og_image":[{"width":552,"height":327,"url":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/04\/Ads\u0131z-57.png","type":"image\/png"}],"author":"dbtut","twitter_card":"summary_large_image","twitter_misc":{"Written by":"dbtut","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/dbtut.com\/index.php\/2019\/04\/29\/create-index-on-join-columns\/#article","isPartOf":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/04\/29\/create-index-on-join-columns\/"},"author":{"name":"dbtut","@id":"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408"},"headline":"Create Index On Join Columns","datePublished":"2019-04-29T07:06:58+00:00","mainEntityOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/04\/29\/create-index-on-join-columns\/"},"wordCount":258,"commentCount":0,"publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/04\/29\/create-index-on-join-columns\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/04\/Ads\u0131z-57.png","keywords":["CREATE JOIN INDEX","creating indexes on columns used in join","Creating indexes on columns used to join tables","how to use index in inner join sql server","index all the predicates in join","Index on join","indexes on columns used in join","indexing inner join","Make Sure All JOIN Columns are Indexed","non clustered index on join columns","use index on join"],"articleSection":["MSSQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/dbtut.com\/index.php\/2019\/04\/29\/create-index-on-join-columns\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/dbtut.com\/index.php\/2019\/04\/29\/create-index-on-join-columns\/","url":"https:\/\/dbtut.com\/index.php\/2019\/04\/29\/create-index-on-join-columns\/","name":"Create Index On Join Columns - Database Tutorials","isPartOf":{"@id":"https:\/\/dbtut.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/04\/29\/create-index-on-join-columns\/#primaryimage"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/04\/29\/create-index-on-join-columns\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/04\/Ads\u0131z-57.png","datePublished":"2019-04-29T07:06:58+00:00","description":"Create Index On Join Columns","breadcrumb":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/04\/29\/create-index-on-join-columns\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/dbtut.com\/index.php\/2019\/04\/29\/create-index-on-join-columns\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/index.php\/2019\/04\/29\/create-index-on-join-columns\/#primaryimage","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/04\/Ads\u0131z-57.png","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/04\/Ads\u0131z-57.png","width":552,"height":327},{"@type":"BreadcrumbList","@id":"https:\/\/dbtut.com\/index.php\/2019\/04\/29\/create-index-on-join-columns\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/dbtut.com\/"},{"@type":"ListItem","position":2,"name":"Create Index On Join Columns"}]},{"@type":"WebSite","@id":"https:\/\/dbtut.com\/#website","url":"https:\/\/dbtut.com\/","name":"Database Tutorials","description":"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux","publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/dbtut.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/dbtut.com\/#organization","name":"dbtut","url":"https:\/\/dbtut.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","width":223,"height":36,"caption":"dbtut"},"image":{"@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408","name":"dbtut","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g","caption":"dbtut"},"description":"We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.","sameAs":["http:\/\/NurullahCAKIR"],"url":"https:\/\/dbtut.com\/index.php\/author\/dbtut\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/11883","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/comments?post=11883"}],"version-history":[{"count":0,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/11883\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media\/11890"}],"wp:attachment":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media?parent=11883"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/categories?post=11883"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/tags?post=11883"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}