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“.
|
1 2 3 4 5 |
SELECT ProductID, AVG(UnitPrice) AS 'Average Price', SUM(LineTotal) AS SubTotal FROM Sales.SalesOrderDetail WHERE SalesOrderDetailId=9 GROUP BY ProductID |
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.
|
1 2 3 4 5 |
CREATE NONCLUSTERED INDEX [IX_ProductID] ON [Sales].[SalesOrderDetail] ( [ProductID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) |
As you can see, the sort cost in the query no longer exists.
