Indexes (Visual Database Tools)

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 help you get that information faster than if you had to search all the rows in the table.

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 Dialog Box, XML Indexes Dialog Box or Full-Text Index Dialog Box for a selected table. An index is saved in the database when you save the table that it is attached to, or when you save the diagram in which that table appears. For more information, see Working with Indexes.

As a general rule, 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 must determine what columns to use and what type of index to create.

Index Columns

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:

• Examine the WHERE and JOIN clauses of your queries. Each column included in either clause is a possible candidate for an index.

• Experiment with the new index to examine its effect on the performance of running queries.

• Consider the number of indexes already created on your table. It is best to avoid a large number of indexes on a single table.

• Examine the definitions of the indexes already created on your table. It is best to avoid overlapping indexes that contain shared columns.

• Examine the number of unique data values in a column and compare that number with the number of rows in the table. The result is the selectivity of that column, which can help you decide if a column is a candidate for an index and, if so, what type of index.

Types of 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.

Unique Index

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 How to: Create Unique Indexes.

Primary Key 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 Working with Keys.

Clustered Index

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 How to: Create Clustered Indexes.

Full-text Index

Create a full-text index if you want to perform full-text searches on text-based columns in your database tables. A full-text index relies on a regular index, so you will need to create that first. The regular index must be created on a single, non-null column, and it is best to choose a column with small values rather than a column with large ones. For more information, see How to: Create Indexes.

XML Index

Use the XML Indexes dialog box to create indexes for columns of the data type XML, which cannot be indexed using the Index/Keys dialog box. Each XML column can have more than one XML Index, but the first one created (primary) will be the basis of the others. If you delete the primary XML index, the others will also be deleted. For more information about XML Indexes see How to: Create XML Indexes.

See Also

Reference

Indexes/Keys Dialog Box

Foreign Key Relationships Dialog Box

XML Indexes Dialog Box

Full-Text Index Dialog Box

Other Resources

Working with Indexes

Working with Keys