Index Guidelines
Choosing the correct columns and types for an index is an important step in creating
an effective index. In this article, we will talk about two main points, namely
short index keys and selective indexes (we'll explain what selective indexes are
in just a moment).
Always Keep Index Keys Short
The larger an index key is, the harder a database has to work to use the index.
For instance, an integer key is smaller in size then a character field for holding
100 characters. In particular, keep clustered indexes as short as possible.
There are several approaches to keeping an index key short. First, try to limit
the index to as few columns as possible. While composite indexes are useful and
can sometimes optimize a query, they are also larger and cause more disk reads for
the database. Secondly, try to choose a compact data type for an index column, based
on the number of bytes required for each data type. Integer keys are small and easy
for the database to compare. In contrast, strings require a character-by-character
comparison.
As a rule of thumb, try to avoid using character columns in an index, particularly
primary key indexes. Integer columns will always have an advantage over character
fields in ability to boost the performance of a query.
Keep Distinct Index Keys
The most effective indexes are the indexes with a small percentage of duplicated
values. Think of having a phone book for a city where 60% of the population has
the last name of Smith. A phone book in this area might be easier to use if the
entries were sorted by the resident's first names instead. A good index will allow
the database to disregard as many records as possible during a search.
selective indexes
An index with a high percentage of unique values is a selective index. Obviously,
a unique index is the most selective index of all, because there are no duplicate
values. SQL Server will track statistics for indexes and will know how selective
each index is. The query optimizer utilizes these statistics when selecting the
best index to use for a query.
Maintaining Indexes
In addition to creating an index, we'll need to view existing indexes, and sometimes
delete or rename them. This is part of the ongoing maintenance cycle of a database
as the schema changes, or even naming conventions change.
View Existing Indexes
A list of all indexes on a table is available in the dialog box we used to create
an index. Click on the Selected index drop down control and scroll through the available
indexes.
There is also a stored procedure named sp_helpindex. This stored procedure gives
all of the indexes for a table, along with all of the relevant attributes. The only
input parameter to the procedure is the name of the table, as shown below.
EXEC sp_helpindex Customers
Rename an Index
We can also rename any user created object with the sp_rename stored procedure,
including indexes. The sp_rename procedure takes, at a minimum, the current name
of the object and the new name for the object. For indexes, the current name must
include the name of the table, a dot separator, and the name of the index, as shown
below:
EXEC sp_rename 'Products.IX_UnitPrice', 'IX_Price'
This will change the name of the IX_UnitPrice index to IX_Price.
Delete an Index
It is a good idea to remove an index from the database if the index is not providing
any benefit. For instance, if we know the queries in an application are no longer
searching for records on a particular column, we can remove the index. Unneeded
indexes only take up storage space and diminish the performance of modifications.
You can remove most indexes with the Delete button on the index dialog box, which
we saw earlier. The equivalent SQL command is shown below.
DROP Index Products.IX_Price
Again, we need to use the name of the table and the name of the index, with a dot
separator. Some indexes are not so easy to drop, namely any index supporting a unique
or primary key constraint. For example, the following command tries to drop the
PK_Products index of the Products table.
DROP INDEX Products.PK_Products
Since the database uses PK_Products to enforce a primary key constraint on the Products
table, the above command should produce the following error.
An explicit DROP INDEX is not allowed on index 'Products.PK_Products'. It is being
used for PRIMARY KEY constraint enforcement.
Removing a primary key constraint from a table is a redesign of the table, and requires
careful thought. It makes sense to know the only way to achieve this task is to
either drop the table and use a CREATE TABLE command to recreate the table without
the index, or to use the ALTER TABLE command.