Although the Database Engine does not have any practical limitations concerning the number of indices, it is advisable to limit them, for a couple of reasons. First, each index uses a certain amount of disk space, so it is possible that the total number of index pages could exceed the number of data pages within a database. Second, in contrast to the benefits of using an index for retrievals, inserts and updates have a direct impact on the maintenance of the index. The more indices on the tables, the more index reorganizations that are necessary. The rule of thumb is to choose indices wisely for frequent queries and evaluate index usage afterwards.
This section gives some recommendations for creating and using indices.
Indices and Conditions in the WHERE Clause
If the WHERE clause in a SELECT statement contains a search condition with a single column, you should create an index on this column. The use of an index is especially recommended if the selectivity of the condition is high. The selectivity of a condition is defined as the ratio of the number of rows satisfying the condition to the total number of rows in the table. (High selectivity corresponds to a small ratio.) The most successful processing of a retrieval with the indexed column will be achieved if the selectivity of a condition is 5 percent or less.
The column should not be indexed if the selectivity of the condition is constantly 80 percent or more. In such a case, additional I/O operations will be needed for the existing index pages, which would eliminate any time savings gained by index access. In this particular case, a table scan would be faster, and the query optimizer would usually choose to use a table scan, rendering the index useless.
If a search condition in a frequently used query contains one or more AND operators, it is best to create a composite index that includes all the columns of the table specified in the WHERE clause of the SELECT statement. Example 10.5 shows the creation of a composite index that includes all the columns specified in the WHERE clause of the SELECT statement.
USE sample;
CREATE INDEX i_works ON works_on(emp_no, enter_date);
SELECT emp_no, project_no, enter_date
FROM works_on
WHERE emp_no = 29346 AND enter_date='1.4.2006';Code language: PHP (php)The AND operator in this query contains two conditions. As such, both of the columns appearing in each condition should be indexed using a composite nonclustered index.
Indices and the Join Operator
In the case of a join operation, it is recommended that you index each join column. Join columns often represent the primary key of one table and the corresponding foreign key of the other or the same table. If you specify the PRIMARY KEY and FOREIGN KEY integrity constraints for the corresponding join columns, only a nonclustered index for the column with the foreign key should be created, because the system will implicitly create the clustered index for the PRIMARY KEY column.
Example 10.6 shows the creation of indices, which should be used if you have a query with a join operation and an additional filter.
USE sample;
SELECT emp_lname, emp_fname
FROM employee, works_on
WHERE employee.emp_no = works_on.emp_no
AND enter_date = '10.15.2007';Code language: PHP (php)For Example 10.6, the creation of two separate indices for the emp_no column in both the employee and works_on tables is recommended. Also, an additional index should be created for the enter_date column.
Covering Index
As you already know, significant performance gains can be achieved when all columns in the query are included in the index. Example 10.7 shows a covering index.
USE AdventureWorks;
GO
DROP INDEX Person.Address.IX_Address_StateProvinceID;
GO
CREATE INDEX i_address_zip
ON Person.Address (PostalCode)
INCLUDE (City, StateProvinceID);
GO
SELECT City, StateProvinceID
FROM Person.Address
WHERE PostalCode = 84407;Code language: PHP (php)