{"id":11987,"date":"2019-05-01T20:05:17","date_gmt":"2019-05-01T20:05:17","guid":{"rendered":"https:\/\/dbtut.com\/?p=11987"},"modified":"2019-05-01T20:05:17","modified_gmt":"2019-05-01T20:05:17","slug":"create-index-on-the-group-by-column","status":"publish","type":"post","link":"https:\/\/dbtut.com\/index.php\/2019\/05\/01\/create-index-on-the-group-by-column\/","title":{"rendered":"Create Index On the GROUP BY Column"},"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

I explained in the article “Create Index on the Order By Column(ASC,DESC)<\/a>” that the index created in the Order By column reduced the sort cost in the query to zero. Also, the index created on the GROUP BY column reduces the sort cost to zero. Let’s take a look at how this works.<\/p>\n

Then retrieve the execution plan of the following query. To retrieve the Execution plan, you must click on the following black frame in the query screen on the SSMS. For more information about the Execution plan, read the article “What is Execution Plan on SQL Server<\/a>“.<\/p>\n

\"\"<\/p>\n

SELECT ProductID,  AVG(UnitPrice) AS 'Average Price', \r\n    SUM(LineTotal) AS SubTotal\r\nFROM Sales.SalesOrderDetail\r\nWHERE SalesOrderDetailId=9\r\nGROUP BY ProductID\r\n<\/pre>\n
\"\"<\/p>\n
As you can see, there is a Sort cost in the query because of the Group By statement. In our query this cost seems to be 1% but in your query this rate may be more.<\/p>\n
Now let’s create an index in the ProductID column in the Group By statement as follows and re-create the query’s execution plan.<\/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 sort cost in the query no longer exists.<\/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. I explained in the article “Create Index on the Order By Column(ASC,DESC)” that the index created in the Order By column reduced the sort cost in the query to …<\/p>\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"author":1,"featured_media":11992,"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":[3777,3775,3790,3789,3781,3782,3780,3787,3788,3784,3785,3779,3778,3776,3786,3783],"class_list":["post-11987","post","type-post","status-publish","format-standard","has-post-thumbnail","","category-mssql","tag-can-an-index-speed-up-a-query-with-group-by","tag-creating-indexes-for-group-by-fields","tag-group-by-index-ms-sql","tag-group-by-index-mssql","tag-group-by-index-mysql","tag-group-by-index-pandas","tag-group-by-index-postgres","tag-group-by-index-sql","tag-group-by-index-sql-server","tag-how-to-improve-group-by-performance-in-oracle","tag-index-all-the-predicates-in-join-where-order-by-and-group-by-clauses","tag-index-on-group-by-column-in-sql-server","tag-indexing-for-group-by","tag-indexing-group-by","tag-nonclustered-index-group-by","tag-sql-group-by-max-index"],"aioseo_notices":[],"a3_pvc":{"activated":true,"total_views":973,"today_views":0},"yoast_head":"\nCreate Index On the GROUP BY Column - Database Tutorials<\/title>\n<meta name=\"description\" content=\"Create Index On the GROUP BY Column\" \/>\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\/05\/01\/create-index-on-the-group-by-column\/\" \/>\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 GROUP BY Column - Database Tutorials\" \/>\n<meta property=\"og:description\" content=\"Create Index On the GROUP BY Column\" \/>\n<meta property=\"og:url\" content=\"https:\/\/dbtut.com\/index.php\/2019\/05\/01\/create-index-on-the-group-by-column\/\" \/>\n<meta property=\"og:site_name\" content=\"Database Tutorials\" \/>\n<meta property=\"article:published_time\" content=\"2019-05-01T20:05:17+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/05\/Ads\u0131z.png\" \/>\n\t<meta property=\"og:image:width\" content=\"587\" \/>\n\t<meta property=\"og:image:height\" content=\"314\" \/>\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=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/05\/01\/create-index-on-the-group-by-column\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/05\/01\/create-index-on-the-group-by-column\/\"},\"author\":{\"name\":\"dbtut\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408\"},\"headline\":\"Create Index On the GROUP BY Column\",\"datePublished\":\"2019-05-01T20:05:17+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/05\/01\/create-index-on-the-group-by-column\/\"},\"wordCount\":207,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/05\/01\/create-index-on-the-group-by-column\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/05\/Ads\u0131z.png\",\"keywords\":[\"Can an index speed up a query with GROUP BY\",\"Creating Indexes for Group By Fields\",\"group by index ms sql\",\"group by index mssql\",\"group by index mysql\",\"group by index pandas\",\"group by index postgres\",\"group by index sql\",\"group by index sql server\",\"how to improve group by performance in oracle\",\"index all the predicates in join where order by and group by clauses\",\"index on group by column in sql server\",\"Indexing for GROUP BY\",\"Indexing GROUP BY\",\"nonclustered index group by\",\"sql group by max index\"],\"articleSection\":[\"MSSQL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2019\/05\/01\/create-index-on-the-group-by-column\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/05\/01\/create-index-on-the-group-by-column\/\",\"url\":\"https:\/\/dbtut.com\/index.php\/2019\/05\/01\/create-index-on-the-group-by-column\/\",\"name\":\"Create Index On the GROUP BY Column - Database Tutorials\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/05\/01\/create-index-on-the-group-by-column\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/05\/01\/create-index-on-the-group-by-column\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/05\/Ads\u0131z.png\",\"datePublished\":\"2019-05-01T20:05:17+00:00\",\"description\":\"Create Index On the GROUP BY Column\",\"breadcrumb\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/05\/01\/create-index-on-the-group-by-column\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2019\/05\/01\/create-index-on-the-group-by-column\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/05\/01\/create-index-on-the-group-by-column\/#primaryimage\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/05\/Ads\u0131z.png\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/05\/Ads\u0131z.png\",\"width\":587,\"height\":314},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/05\/01\/create-index-on-the-group-by-column\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/dbtut.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Create Index On the GROUP BY Column\"}]},{\"@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 GROUP BY Column - Database Tutorials","description":"Create Index On the GROUP BY Column","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\/05\/01\/create-index-on-the-group-by-column\/","og_locale":"en_US","og_type":"article","og_title":"Create Index On the GROUP BY Column - Database Tutorials","og_description":"Create Index On the GROUP BY Column","og_url":"https:\/\/dbtut.com\/index.php\/2019\/05\/01\/create-index-on-the-group-by-column\/","og_site_name":"Database Tutorials","article_published_time":"2019-05-01T20:05:17+00:00","og_image":[{"width":587,"height":314,"url":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/05\/Ads\u0131z.png","type":"image\/png"}],"author":"dbtut","twitter_card":"summary_large_image","twitter_misc":{"Written by":"dbtut","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/dbtut.com\/index.php\/2019\/05\/01\/create-index-on-the-group-by-column\/#article","isPartOf":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/05\/01\/create-index-on-the-group-by-column\/"},"author":{"name":"dbtut","@id":"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408"},"headline":"Create Index On the GROUP BY Column","datePublished":"2019-05-01T20:05:17+00:00","mainEntityOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/05\/01\/create-index-on-the-group-by-column\/"},"wordCount":207,"commentCount":0,"publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/05\/01\/create-index-on-the-group-by-column\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/05\/Ads\u0131z.png","keywords":["Can an index speed up a query with GROUP BY","Creating Indexes for Group By Fields","group by index ms sql","group by index mssql","group by index mysql","group by index pandas","group by index postgres","group by index sql","group by index sql server","how to improve group by performance in oracle","index all the predicates in join where order by and group by clauses","index on group by column in sql server","Indexing for GROUP BY","Indexing GROUP BY","nonclustered index group by","sql group by max index"],"articleSection":["MSSQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/dbtut.com\/index.php\/2019\/05\/01\/create-index-on-the-group-by-column\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/dbtut.com\/index.php\/2019\/05\/01\/create-index-on-the-group-by-column\/","url":"https:\/\/dbtut.com\/index.php\/2019\/05\/01\/create-index-on-the-group-by-column\/","name":"Create Index On the GROUP BY Column - Database Tutorials","isPartOf":{"@id":"https:\/\/dbtut.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/05\/01\/create-index-on-the-group-by-column\/#primaryimage"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/05\/01\/create-index-on-the-group-by-column\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/05\/Ads\u0131z.png","datePublished":"2019-05-01T20:05:17+00:00","description":"Create Index On the GROUP BY Column","breadcrumb":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/05\/01\/create-index-on-the-group-by-column\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/dbtut.com\/index.php\/2019\/05\/01\/create-index-on-the-group-by-column\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/index.php\/2019\/05\/01\/create-index-on-the-group-by-column\/#primaryimage","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/05\/Ads\u0131z.png","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/05\/Ads\u0131z.png","width":587,"height":314},{"@type":"BreadcrumbList","@id":"https:\/\/dbtut.com\/index.php\/2019\/05\/01\/create-index-on-the-group-by-column\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/dbtut.com\/"},{"@type":"ListItem","position":2,"name":"Create Index On the GROUP BY Column"}]},{"@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\/11987","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=11987"}],"version-history":[{"count":0,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/11987\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media\/11992"}],"wp:attachment":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media?parent=11987"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/categories?post=11987"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/tags?post=11987"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}