Interviews Questions and Answers



What is the significance of an Index on the column used in the GROUP BY clause?

Posted by: James
On 25 Mar 2012 | Views: 1138 | Categories: Database (SQL Server)
Interview Answers(1)

 On 25 Mar 2012 10:49:16 AM
Creating an Index on the column, that is used in the GROUP BY clause, can greatly improve the perofrmance. We use a GROUP BY clause to group records and aggregate values, for example, counting the number of products with the same UnitPrice. To process a query with a GROUP BY clause, the database will often sort the results on the columns included in the GROUP BY.

The following query counts the number of products at each price by grouping together records with the same UnitPrice value.
SELECT UnitPrice, Count(*) FROM tblProducts GROUP BY UnitPrice

The database can use the index (Index on UNITPRICE column) to retrieve the prices in order. Since matching prices appear in consecutive index entries, the database is able to count the number of products at each price quickly. Indexing a field used in a GROUP BY clause can often speed up a query.

Post your Answers


Other lins

.Net Techies
Other lins