Database Reference
In-Depth Information
in the WHERE clause. he column in the INCLUDE clause can be used to search a record set
returned by a SELECT clause.
8.3.2 Manage Indexes
After an index is created, you can view, modify, and delete it. SQL Database provides several ways
to manage existing indexes. he SQL Statement ALTER INDEX can be used to rebuild, disable,
or modify an existing index. With ALTER INDEX, you do not need to drop the existing index
and recreate it to defragment. he syntax of ALTER INDEX is given below:
ALTER INDEX {
index_name | All
}
ON [
table_name
|
view_name]
(
column_name
[ASC | DESC] [,...
n
])
{REBUILD [WITH [
rebuild_index_option
] [,...
n
]] |
DISABLE |
SET (
<
set_index_option
>
[,...n])}
In the above syntax, [,. . .
n
] is used to indicate that multiple columns or multiple options can be
listed. he meanings of the keywords in the ALTER INDEX statement are as follows:
◾
REBUILD: Rebuilds an index by dropping and recreating an existing index
◾
DISABLE: Makes an index temporarily unavailable
◾
SET: Sets options for accessing and maintaining indexes
he parameter
rebuild_index_option
for the keyword REBUILD has similar options as
index_
property
in the WITH clause in the CREATE INDEX statement, except the DROP_EXISTING
option. herefore, you cannot use ALTER INDEX to drop an existing index. he parameter
set_index_option
has the following options:
{
IGNORE_DUP_KEY
=
{ON|OFF} |
STATISTICS_NORECOMPUTE
=
{ON|OFF}
}
8.3.3 Remove Indexes
If an index is no longer used, you can remove it by using the DROP INDEX statement, which has
the following syntax:
DROP INDEX index_name
ON {
table_or_ view_name
}
You may need to add the name of the schema and table in front of the index name to make sure
that the right index gets dropped. he notation [,
...n
] indicates that you can drop multiple indexes.
In Figure 8.20, the index Course_Index deined on the table CLASS is removed.
he stored procedures
sp_help
and
sp_helpindex
can be used to obtain information about
an index. For example, if you want to know the information about indexes created for the table
CLASS, enter and execute the SQL statement in Figure 8.21.
As shown in Figure 8.21, the stored procedure sp_helpindex returns one index. he index is
the index PK_CLASS_CB1927A001738BAC, which is created by Windows Azure SQL Database
automatically when the table CLASS is created.
