Database Reference
In-Depth Information
combination must be unique. However, the uniqueness is not required for a single column on
which a clustered index is created because the clustered index enforces the uniqueness by creating
an associated sorting column containing only unique values.
8.3.1 Create Indexes
One way to create an index is to use the SQL statement CREATE INDEX. he CREATE INDEX
syntax has many optional parameters as shown below:
CREATE [UNIQUE][CLUSTERED|NONCLUSTERED] INDEX
index_name
ON [
table_name
|
view_name]
(
column_name
[ASC | DESC] [,...
n
])
[INCLUDE (
column_name
[,...
n
])]
[WHERE <filter_predicate>]
[WITH [
index_property
] [,...
n
]]
In the above syntax, the notation [,. . .
n
] is used to indicate that multiple columns or multiple
options can be listed. he keyword INCLUDE is used to include the name of the column in which
the rows are placed on the leaf nodes. he ilter_predicate in the WHERE clause includes the con-
ditions joint with logical operators such as AND, IN, and OR, and comparison operators such as
{IS|IS NOT|
=
|
<>
|!
=
|
>
|
>=
|!
>
|
<
|
<=
|!
<
}
he
index_property
in the WITH clause can be any of the following:
{
IGNORE_DUP_KEY
=
{ON|OFF} |
DROP_EXISTING
=
{ON | OFF} |
STATISTICS_NORECOMPUTE
=
{ON | OFF} |
ONLINE
=
{ON | OFF}
}
If the ONLINE parameter is set to ON, it allows the database administrator to manage an
index while it is used by another DML SQL statement. he example displayed in Figure 8.19 is
used to create a nonclustered index on the column CourseID, include the column TimeID, and
turn ONLINE to ON.
If the column CourseID is included in the conditions of a WHERE clause, the column
CourseID after the keyword ON is used to form the key of the index Course_Index. he index
key will be used to search the primary table CLASS to ind the rows that satisfy the conditions
Figure 8.19
Create index with CREATE INDEX statement.
