{"id":11926,"date":"2019-04-30T13:46:20","date_gmt":"2019-04-30T13:46:20","guid":{"rendered":"https:\/\/dbtut.com\/?p=11926"},"modified":"2019-05-01T20:06:46","modified_gmt":"2019-05-01T20:06:46","slug":"create-index-on-the-order-by-columnascdesc","status":"publish","type":"post","link":"https:\/\/dbtut.com\/index.php\/2019\/04\/30\/create-index-on-the-order-by-columnascdesc\/","title":{"rendered":"Create Index on the Order By Column(ASC,DESC)"},"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 there is no index in the Order By column, the query will sort the result set, and there will also be a sort cost. In addition, if we want to create the index for the Order By column, the most important thing is how the column is sorted.<\/p>\n

\u00a0I’ll go through an example to make it easier to understand.<\/p>\n

We will do our tests in the AdventureWorks2014 database.<\/p>\n

First, let’s take a look at the execution plan of the following query as shown in the picture below.<\/p>\n

SELECT RejectedQty, ((RejectedQty\/OrderQty)*100) AS RejectionRate,\n    ProductID, DueDate\nFROM Purchasing.PurchaseOrderDetail\nwhere ProductID between 100 AND 1000\nORDER BY RejectedQty DESC, ProductID ASC;\n<\/pre>\n
\"\"<\/p>\n
As you can see, most of the cost of the query is a sort operation. Let’s create an index that will cover the query as below, and look at the execution plan again.<\/p>\n
CREATE NONCLUSTERED INDEX IX_PurchaseOrderDetail_RejectedQty\nON Purchasing.PurchaseOrderDetail\n    (RejectedQty ASC, ProductID ASC, DueDate, OrderQty);\n<\/pre>\n
\"\"<\/p>\n
The query used the index we created, but the sort operation is still a large part of the cost. The reason for this is that the index that we created is sorted with ASC, but this column is sorted with DESC in the query.<\/p>\n
Now let’s create the index with a different name by simply converting the ASC to DESC.<\/p>\n
CREATE NONCLUSTERED INDEX IX_PurchaseOrderDetail_RejectedQtyWITHDESC\nON Purchasing.PurchaseOrderDetail\n    (RejectedQty DESC, ProductID ASC, DueDate, OrderQty);\n<\/pre>\n
\"\"<\/p>\n
As you can see, we just recreated the index by changing the index’s sorting, and the cost of the query fell dramatically. The cost of Index SCAN increases from 8% to 82%. It shows how much the total cost of this query is reduced. We also no longer see the cost of the sort operation in the query. Because the index is sorted correctly. So there is no need for reordering.<\/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 there is no index in the Order By column, the query will sort the result set, and there will also be a sort cost. In addition, if we …<\/p>\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"author":1,"featured_media":11934,"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":[3713,3708,3704,3707,3705,3717,3714,3718,3711,3710,3716,3719,3715,3706,3709,3712],"class_list":["post-11926","post","type-post","status-publish","format-standard","has-post-thumbnail","","category-mssql","tag-building-sql-server-indexes-in-ascending-vs-descending-order","tag-create-index-desc","tag-create-index-on-order-by","tag-describe-how-the-use-of-an-index-will-make-queries-using-order-by-much-faster","tag-does-index-help-order-by","tag-does-order-by-slow-down-query","tag-how-to-improve-order-by-performance","tag-how-to-reduce-sort-cost-in-execution-plan","tag-indexing-for-sort-performance","tag-order-by-column-should-have-index-or-not","tag-order-by-index","tag-order-by-optimization","tag-performance-tuning-order-by-clause","tag-sql-index-sort-order","tag-sql-order-by","tag-using-indexes-to-optimize-order-by-queries"],"aioseo_notices":[],"a3_pvc":{"activated":true,"total_views":778,"today_views":0},"yoast_head":"\nCreate Index on the Order By Column(ASC,DESC) - Database Tutorials<\/title>\n<meta name=\"description\" content=\"Create Index on the Order By Column(ASC,DESC)\" \/>\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\/30\/create-index-on-the-order-by-columnascdesc\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Create Index on the Order By Column(ASC,DESC) - Database Tutorials\" \/>\n<meta property=\"og:description\" content=\"Create Index on the Order By Column(ASC,DESC)\" \/>\n<meta property=\"og:url\" content=\"https:\/\/dbtut.com\/index.php\/2019\/04\/30\/create-index-on-the-order-by-columnascdesc\/\" \/>\n<meta property=\"og:site_name\" content=\"Database Tutorials\" \/>\n<meta property=\"article:published_time\" content=\"2019-04-30T13:46:20+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2019-05-01T20:06:46+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/04\/Ads\u0131z-64.png\" \/>\n\t<meta property=\"og:image:width\" content=\"615\" \/>\n\t<meta property=\"og:image:height\" content=\"375\" \/>\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\/30\/create-index-on-the-order-by-columnascdesc\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/04\/30\/create-index-on-the-order-by-columnascdesc\/\"},\"author\":{\"name\":\"dbtut\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408\"},\"headline\":\"Create Index on the Order By Column(ASC,DESC)\",\"datePublished\":\"2019-04-30T13:46:20+00:00\",\"dateModified\":\"2019-05-01T20:06:46+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/04\/30\/create-index-on-the-order-by-columnascdesc\/\"},\"wordCount\":288,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/04\/30\/create-index-on-the-order-by-columnascdesc\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/04\/Ads\u0131z-64.png\",\"keywords\":[\"Building SQL Server Indexes in Ascending vs Descending Order\",\"create index desc\",\"create index on order by\",\"describe how the use of an index will make queries using order by much faster\",\"Does index Help order by?\",\"does order by slow down query\",\"how to improve order by performance\",\"how to reduce sort cost in execution plan\",\"Indexing for Sort Performance\",\"Order by column should have index or not?\",\"order by index\",\"order by optimization\",\"performance tuning order by clause\",\"sql index sort order\",\"sql order by\",\"Using Indexes to Optimize ORDER BY Queries\"],\"articleSection\":[\"MSSQL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2019\/04\/30\/create-index-on-the-order-by-columnascdesc\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/04\/30\/create-index-on-the-order-by-columnascdesc\/\",\"url\":\"https:\/\/dbtut.com\/index.php\/2019\/04\/30\/create-index-on-the-order-by-columnascdesc\/\",\"name\":\"Create Index on the Order By Column(ASC,DESC) - Database Tutorials\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/04\/30\/create-index-on-the-order-by-columnascdesc\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/04\/30\/create-index-on-the-order-by-columnascdesc\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/04\/Ads\u0131z-64.png\",\"datePublished\":\"2019-04-30T13:46:20+00:00\",\"dateModified\":\"2019-05-01T20:06:46+00:00\",\"description\":\"Create Index on the Order By Column(ASC,DESC)\",\"breadcrumb\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/04\/30\/create-index-on-the-order-by-columnascdesc\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2019\/04\/30\/create-index-on-the-order-by-columnascdesc\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/04\/30\/create-index-on-the-order-by-columnascdesc\/#primaryimage\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/04\/Ads\u0131z-64.png\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/04\/Ads\u0131z-64.png\",\"width\":615,\"height\":375},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/04\/30\/create-index-on-the-order-by-columnascdesc\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/dbtut.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Create Index on the Order By Column(ASC,DESC)\"}]},{\"@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 the Order By Column(ASC,DESC) - Database Tutorials","description":"Create Index on the Order By Column(ASC,DESC)","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\/30\/create-index-on-the-order-by-columnascdesc\/","og_locale":"en_US","og_type":"article","og_title":"Create Index on the Order By Column(ASC,DESC) - Database Tutorials","og_description":"Create Index on the Order By Column(ASC,DESC)","og_url":"https:\/\/dbtut.com\/index.php\/2019\/04\/30\/create-index-on-the-order-by-columnascdesc\/","og_site_name":"Database Tutorials","article_published_time":"2019-04-30T13:46:20+00:00","article_modified_time":"2019-05-01T20:06:46+00:00","og_image":[{"width":615,"height":375,"url":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/04\/Ads\u0131z-64.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\/30\/create-index-on-the-order-by-columnascdesc\/#article","isPartOf":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/04\/30\/create-index-on-the-order-by-columnascdesc\/"},"author":{"name":"dbtut","@id":"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408"},"headline":"Create Index on the Order By Column(ASC,DESC)","datePublished":"2019-04-30T13:46:20+00:00","dateModified":"2019-05-01T20:06:46+00:00","mainEntityOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/04\/30\/create-index-on-the-order-by-columnascdesc\/"},"wordCount":288,"commentCount":0,"publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/04\/30\/create-index-on-the-order-by-columnascdesc\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/04\/Ads\u0131z-64.png","keywords":["Building SQL Server Indexes in Ascending vs Descending Order","create index desc","create index on order by","describe how the use of an index will make queries using order by much faster","Does index Help order by?","does order by slow down query","how to improve order by performance","how to reduce sort cost in execution plan","Indexing for Sort Performance","Order by column should have index or not?","order by index","order by optimization","performance tuning order by clause","sql index sort order","sql order by","Using Indexes to Optimize ORDER BY Queries"],"articleSection":["MSSQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/dbtut.com\/index.php\/2019\/04\/30\/create-index-on-the-order-by-columnascdesc\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/dbtut.com\/index.php\/2019\/04\/30\/create-index-on-the-order-by-columnascdesc\/","url":"https:\/\/dbtut.com\/index.php\/2019\/04\/30\/create-index-on-the-order-by-columnascdesc\/","name":"Create Index on the Order By Column(ASC,DESC) - Database Tutorials","isPartOf":{"@id":"https:\/\/dbtut.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/04\/30\/create-index-on-the-order-by-columnascdesc\/#primaryimage"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/04\/30\/create-index-on-the-order-by-columnascdesc\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/04\/Ads\u0131z-64.png","datePublished":"2019-04-30T13:46:20+00:00","dateModified":"2019-05-01T20:06:46+00:00","description":"Create Index on the Order By Column(ASC,DESC)","breadcrumb":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/04\/30\/create-index-on-the-order-by-columnascdesc\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/dbtut.com\/index.php\/2019\/04\/30\/create-index-on-the-order-by-columnascdesc\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/index.php\/2019\/04\/30\/create-index-on-the-order-by-columnascdesc\/#primaryimage","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/04\/Ads\u0131z-64.png","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/04\/Ads\u0131z-64.png","width":615,"height":375},{"@type":"BreadcrumbList","@id":"https:\/\/dbtut.com\/index.php\/2019\/04\/30\/create-index-on-the-order-by-columnascdesc\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/dbtut.com\/"},{"@type":"ListItem","position":2,"name":"Create Index on the Order By Column(ASC,DESC)"}]},{"@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\/11926","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=11926"}],"version-history":[{"count":0,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/11926\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media\/11934"}],"wp:attachment":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media?parent=11926"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/categories?post=11926"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/tags?post=11926"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}