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.
