Share via

  • Article

Query Types and Indexes

When you are considering whether to create an index on a column, evaluate how the column is used in queries. The following tables describes the query types for which indexes are useful.

Note

The examples in the table are based on the AdventureWorks2008R2 sample database. When you run the examples in SQL Server Management Studio, you can view the indexes that are selected by the query optimizer by displaying the actual execution plan. For more information, see How to: Display an Actual Execution Plan.

Query in which the column predicate is one of these

Query description and example

Index to consider

Exact match to a specific value

Searches for an exact match in which the query uses the WHERE clause to specify a column entry with a specific value. For example:

SELECT BusinessEntityID, JobTitle 
FROM HumanResources.Employee 
WHERE BusinessEntityID = 228;

Nonclustered or clustered index on the BusinessEntityID column.

Exact match to a value in an IN (x,y,z) list

Searches for an exact match to a value in a specified list of values. For example:

SELECT BusinessEntityID, JobTitle 
FROM HumanResources.Employee 
WHERE BusinessEntityID IN (288, 30, 15);

Nonclustered or clustered index on the BusinessEntityID column.

Range of values

Searches for a range of values in which the query specifies any entry that has a value between two values. For example:

SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID BETWEEN 1 and 5;

Or

WHERE ProductModelID >= 1 AND ProductModelID <= 5

Clustered or nonclustered index on the ProductModelID column.

Join between tables

Searches for rows in a table that match a row in another table based on a join predicate. For example:

SELECT a.ProductAssemblyID, b.Name, a.PerAssemblyQty
FROM Production.BillOfMaterials AS a
JOIN Production.Product AS b 
ON a.ProductAssemblyID = b.ProductID
WHERE b.ProductID = 900;

Nonclustered or clustered index on the ProductID and ProductAssemblyID columns.

LIKE comparison

Searches for matching rows that start with a specific character string such as 'abc%'. For example:

SELECT CountryRegionCode, Name 
FROM Person.CountryRegion 
WHERE Name LIKE N'D%'

Nonclustered or clustered index on the Name column.

Sorted or aggregated

Requires an implicit or explicit sort order or an aggregation (GROUP BY). For example:

SELECT a.WorkOrderID, b.ProductID, a.OrderQty, a.DueDate 
FROM Production.WorkOrder AS a
JOIN Production.WorkOrderRouting AS b 
ON a.WorkOrderID = b.WorkOrderID
ORDER BY a.WorkOrderID;

Nonclustered or clustered index on the sorted or aggregated column.

For sort columns, consider specifying the ASC or DESC order of the column.

PRIMARY KEY or UNIQUE constraint

Searches for duplicates of new index key values in insert and update operations, to enforce PRIMARY KEY and UNIQUE constraints. For example:

INSERT INTO Production.UnitMeasure (UnitMeasureCode, Name, ModifiedDate)
VALUES ('OZ1', 'OuncesTest', GetDate());

Clustered or nonclustered index on the column or columns defined in the constraint.

UPDATE or DELETE operation in a PRIMARY KEY/FOREIGN KEY relationship

Searches for rows in an update or delete operation in which the column participates in a PRIMARY KEY/FOREIGN KEY relationship, with or without the CASCADE option.

Nonclustered or clustered index on the foreign key column.

Column is in the select list but not in the predicate.

Contains one or more columns in the select list that are not used for searching and lookups. For example:

SELECT Title, Revision, FileName
FROM Production.Document
WHERE Title LIKE N'%Maintenance%' AND Revision >= 0';

Nonclustered index with FileName specified in the INCLUDE clause.