Friday , March 29 2024

Create Index On the GROUP BY Column

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 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.

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“.

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.

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.

As you can see, the sort cost in the query no longer exists.

Loading

About dbtut

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

Categories