You can use an index to gain fast access to specific information in a database table. An index is a structure that orders the values of one or more columns in a database table, for example the last name (lname) column of the employee table. If you were looking for a specific employee by his or her last name, the index would enable you to get that information faster than if you had to search all the rows in the table, which is sorted by employee ID.
The index provides pointers to the data values stored in specified columns of the table, and then orders those pointers according to the sort order you specify. The database uses the index much as you use an index in a book: it searches the index to find a particular value and then follows the pointer to the row containing that value.
In database diagrams, you can create, edit, or delete each type of index in the Indexes/Keys property page for a selected table. An index is saved in the database when you save the table that it’s attached to, or when you save the diagram in which that table appears. For details, see Creating an Index.
Note Not all databases work with indexes in the same way. For more information, see Database Server Considerations, or consult your database documentation.
As a rule of thumb, you should create an index on a table only if the data in the indexed columns will be queried frequently. Indexes take up disk space and slow the adding, deleting, and updating of rows. In most situations, the speed advantages of indexes for data retrieval greatly outweigh these disadvantages. However, if your application updates data very frequently or if you have disk space constraints, you might want to limit the number of indexes.
Before creating an index, you have two important tasks:
You can create indexes based on a single column or on multiple columns in a database table. Multiple-column indexes enable you to distinguish between rows in which one column may have the same value.
Indexes are also helpful if you often search or sort by two or more columns at a time. For example, if you often set criteria for last name and first name columns in the same query, it makes sense to create a multiple-column index on those two columns.
To determine the usefulness of an index:
Depending on the functionality of your database, you can create three types of indexes - unique, primary key, and clustered - in Database Designer. For details about the indexing functionality supported by your database, see your database documentation.
Tip Although a unique index will help locate information, for the best performance results it is recommended that you use primary key or unique constraints instead. For more information about these constraints, see Primary Key Constraints and Unique Constraints.
A unique index is one in which no two rows are permitted to have the same index value.
Most databases prevent you from saving a table with a newly created unique index when there are duplicate key values in the existing data. Your database may also prevent the addition of new data that would create duplicate key values in the table. For example, if you create a unique index on the employee’s last name (lname) in the employee table, then no two employees can share the same last name.
For more information about unique indexes, see Creating a Unique Index.
A database table often has a column or combination of columns whose value uniquely identifies each row in the table. This column is called the primary key of the table.
Defining a primary key for a table in a database diagram automatically creates a primary key index that is a specific type of unique index. This index requires each value in the primary key to be unique. It also permits fast access to data when you use the primary key index in queries. For more information about primary keys, see Defining a Primary Key.
In a clustered index, the physical order of the rows in the table is the same as the logical (indexed) order of the key values. A table can contain only one clustered index.
If an index is not clustered, the physical order of the rows in the table does not match the logical order of the key values. A clustered index usually provides faster access to data than does a nonclustered index.
For more information about using a clustered index, see Creating a Clustered Index.