Sams Teach Yourself SQL in 24 Hours

Recommended Book

Sams Teach Yourself SQL in 24 Hours, 3rd Edition

Learn More Buy

Recommended Book

Recommended Book 

Sams Teach Yourself SQL in 24 Hours, 3rd Edition

Learn More Buy

Types of Indexes

There are different types of indexes that can be created on tables in a database, all of which serve the same goal—to improve database performance by expediting data retrieval. This hour discusses single-column indexes, composite indexes, and unique indexes.

Single-Column Indexes

Indexing on a single column of a table is the simplest and most common manifestation of an index. Obviously, a single-column index is one that is created based on only one table column. The basic syntax is as follows:

   
CREATE INDEX INDEX_NAME
ON TABLE_NAME (COLUMN_NAME)

For example, if you want to create an index on the EMPLOYEE_TBL table for employees' last names, the command used to create the index would look like the following:

CREATE INDEX NAME_IDX 
ON EMPLOYEE_TBL (LAST_NAME);

Unique Indexes

Unique indexes are used not only for performance, but also for data integrity. A unique index does not allow any duplicate values to be inserted into the table. Otherwise, the unique index performs the same way a regular index performs. The syntax is as follows:

   
CREATE UNIQUE INDEX INDEX_NAME
ON TABLE_NAME (COLUMN_NAME)

If you want to create a unique index on the EMPLOYEE_TBL table for an employee's last name, the command used to create the unique index would look like the following:

CREATE UNIQUE INDEX NAME_IDX 
ON EMPLOYEE_TBL (LAST_NAME);

The only problem with this index is that every individual's last name in the EMPLOYEE_TBL table must be unique—pretty impractical. However, a unique index should be created for a column, such as an individual's Social Security number, because each of these numbers for each individual is unique.

You may be wondering, "What if an employee's SSN were the primary key for a table?" An index is usually implicitly created when you define a primary key for a table. However, a company can use a fictitious number for an employee ID, but maintain each employees' SSN for tax purposes. You probably want to index this column and ensure that all entries into this column are unique values.

Composite Indexes

A composite index is an index on two or more columns of a table. You should consider performance when creating a composite index because the order of columns in the index has a measurable effect on data retrieval speed. Generally, the most restrictive value should be placed first for optimum performance. However, the columns that will always be specified should be placed first. The syntax is as follows:

   
CREATE INDEX INDEX_NAME
ON TABLE_NAME (COLUMN1, COLUMN2)

An example of a composite index follows:

CREATE INDEX ORD_IDX 
ON ORDERS_TBL (CUST_ID, PROD_ID);

In this example, you create a composite index based on two columns in the ORDERS_TBL table: CUST_ID and PROD_ID. You assume that these two columns are frequently used together as conditions in the WHERE clause of a query.

Single-Column Versus Composite Indexes

In deciding whether to create a single-column index or a composite index, take into consideration the column(s) that you may use very frequently in a query's WHERE clause as filter conditions. Should there be only one column used, a single-column index should be the choice. Should there be two or more columns that are frequently used in the WHERE clause as filters, a composite index would be the best choice.

Implicit Indexes

Implicit indexes are indexes that are automatically created by the database server when an object is created. Indexes are automatically created for primary key constraints and unique constraints. Why are indexes automatically created for these constraints? Imagine that you are the database server. A user adds a new product to the database. The product identification is the primary key on the table, which means that it must be a unique value. To efficiently check to make sure the new value is unique among hundreds or thousands of records, the product identifications in the table must be indexed. Therefore, when you create a primary key or unique constraint, an index is automatically created for you.