How to Create Indexes
To create an index on a table, you have to select on which columns to build the index, and then use the CREATE INDEX statement. As an example, to create the index called ndx_Customers_City on the City field of the Customers table, you must execute the statement of Listing 6.9.
Code Listing 6.9. Creating an Index Is Easy Using the CREATE INDEX Statement
|
USE Northwind
GO
CREATE INDEX ndx_Customers_City
ON Customers(City)
You can use Enterprise Manager to create indexes on tables using the Manage Indexes form. To open the Manage Indexes form, you must display the list of tables in a database, select the table in which you want to create the index, right-click with the mouse on the table name, or choose Action, All Tasks, Manage Indexes. Figure 6.9 shows the ManageIndexes form.
Figure 6.9. Getting information on indexes available for every table or view is easy using the Manage Indexes form.
You can select the databaseand the table in which to create the index by using the Database and Table/view drop-down list boxes.
To create an index, you can click the New command button, and the Create New Index form will appear. Figure 6.10 shows the Create New Index form.
Figure 6.10. Creating indexes is easy using the Create New Index form.
Every index must have a name, and the name must be unique for the table or view where the index is defined.
Below the index name, you can see the list of columns you can make part of the index. Clicking the check boxes on the left of the column name, you can specify which columns to include in the index. You also can select whether the column must be included in ascending or descending order.
You can change the order of the columns in the index by using the Up and Down buttons.
Caution
The order of columns in an index is important: An index created on the (CompanyName, Phone) columns, as in Listing 6.10, can be useful to search for telephone numbers of a certain company; however, an index on (Phone, CompanyName) could be useful to search for the company where a specific telephone number is installed.
Code Listing 6.10. You Can Create an Index on More Than One Column Using the CREATE INDEX Statement
|
USE Northwind
GO
CREATE INDEX ndx_Customers_Company_Phone
ON Customers(CompanyName, Phone)
CREATE INDEX ndx_Customers_Phone_Company
ON Customers(Phone, CompanyName)
After the index is created, nothing special happens. Our data still looks the same. However, having indexes gives SQL Query Optimizer more ways to optimize a query—and it will use them as necessary.
Later in this chapter, in the "Types of Indexes" and "Index Maintenance" sections, you will learn about more options that you can use to create different type ofindexes.