The CREATE INDEX statement creates an index for the particular table. The general form of this statement is
CREATE [UNIQUE] [CLUSTERED |NONCLUSTERED] INDEX index_name
ON table_name (column1 [ASC | DESC] ,...)
[ INCLUDE ( column_name [ ,... ] ) ]
[WITH
[FILLFACTOR=n]
[[, ] PAD_INDEX = {ON | OFF}]
[[, ] DROP_EXISTING = {ON | OFF}]
[[, ] SORT_IN_TEMPDB = {ON | OFF}]
[[, ] IGNORE_DUP_KEY = {ON | OFF}]
[[, ] ALLOW_ROW_LOCKS = {ON | OFF}]
[[, ]ALLOW_PAGE_LOCKS = {ON | OFF}]
[[, ] STATISTICS_NORECOMPUTE = {ON | OFF}]
[[, ]ONLINE = {ON | OFF}]]
[ON file_group | "default"]
Code language: PHP (php)
index_name identifies the name of the created index. An index can be established for one or more columns of a single table (table_name). column1 is the name of the column for which the index is created. (As you can see from the syntax of the CREATE INDEX statement, you can specify an index for several columns of a table.) The Database Engine supports indices on views too. Such views, called indexed views, are discussed in the next chapter.
An index can be either single or composite. A single index has one column, whereas a composite index is built on more than one column. Each composite index has certain restrictions concerning its length and number of columns. The maximum size of an index is 900 bytes, while the index can contain up to 16 columns.
The UNIQUE option specifies that each data value can appear only once in an indexed column. For a unique composite index, the combination of data values of all columns in each row must be unique. If UNIQUE is not specified, duplicate values in the indexed column(s) are allowed.
The CLUSTERED option specifies a clustered index. The NONCLUSTERED option (the default) specifies that the index does not change the order of the rows in the table. The Database Engine allows a maximum of 249 nonclustered indices per table.
The Database Engine has been enhanced to support indices with descending order on column values. The ASC option after the column name specifies that the index is created on the ascending order of the column’s values, while DESC specifies the descending order. This gives you more flexibility for using an index. Descending indices should be used when you create a composite index on columns that have opposite sorting directions.
The INCLUDE option allows you to specify the nonkey columns, which are added to the leaf pages of the nonclustered index. Column names cannot be repeated in the INCLUDE list and cannot be used simultaneously as both key and nonkey columns. To understand the benefit of the INCLUDE option, you have to know what a covering index is. Significant performance gains can be achieved when all columns in a query are included in the index, because the query optimizer can locate all the column values within the index pages without having to access table data. This feature is called a covering index or covered query. So, if you include additional nonkey columns in the leaf pages of the nonclustered index, more queries will be covered and their performance will be significantly better. (Further discussion of this topic, as well as an example of how the query optimizer handles a covering index, can be found later in this chapter in the section “Covering Index.”)
FILLFACTOR=n defines the storage percentage for each index page at the time the index is created. You can set the value of FILLFACTOR from 1 to 100. If the value of n is set to 100, each index page will be 100 percent filled—that is, the existing index leaf pages as well as nonleaf pages will have no space for the insertion of new rows. Therefore, this value is recommended only for static tables. (The default value, 0, also indicates that the leaf index pages are filled and the intermediate nonleaf pages contain one free entry each.)
If you set the FILLFACTOR option to a value between 1 and 99, the new index structure will be created with leaf pages that are not completely full. The bigger the value of FILLFACTOR, the smaller the space that is left free on an index page. For instance, setting FILLFACTOR to 60 means that 40 percent of each leaf index page is left free for future insertion of index rows. (Index rows will be inserted when you execute either the INSERT or the UPDATE statement.) For this reason, the value 60 could be a reasonable value for tables with rather frequent data modification. For all values of the FILLFACTOR option between 1 and 99, the intermediate nonleaf pages contain one free entry each.
The PAD_INDEX option is tightly connected to the FILLFACTOR option. The FILLFACTOR option mainly specifies the percentage of space that is left free on leaf index pages. On the other hand, the PAD_INDEX option specifies that the FILLFACTOR setting should be applied to the index pages as well as to the data pages in the index.
The DROP_EXISTING option allows you to enhance performance when recreating a clustered index on a table that also has a nonclustered index. See the section “Rebuilding an Index” later in the chapter for more details.
The SORT_IN_TEMPDB option is used to place into the tempdb system database the data from intermediate sort operations used while creating the index. This can result in a performance benefit if the tempdb database is placed on another disk drive from the data itself.
The IGNORE_DUP_KEY option causes the system to ignore the attempt to insert duplicate values in the indexed column(s). This option should be used only to avoid the termination of a long transaction in cases where the INSERT statement inserts duplicate data in the indexed column(s). If this option is activated and an INSERT statement attempts to insert rows that would violate the uniqueness of the index, the database system returns a warning rather than causing the entire statement to fail. The Database Engine does not insert the rows that would add duplicate key values; it merely ignores those rows and adds the rest. (If this option is not set, the statement as a whole will be aborted.)
The ALLOW_ROW_LOCKS option specifies that the system uses row locks when this option is activated (set to ON). Similarly, the ALLOW_PAGE_LOCKS option specifies that the system uses page locks when this option is set to ON.
The STATISTICS_NORECOMPUTE option specifies that statistics of the specified index should not be automatically recomputed. The ON option creates either the specified index on the default file group (“default”) or on the specified file group (file_group).
If you activate the ONLINE option, you can create, rebuild, or drop an index online. This option allows concurrent modifications to the underlying table or clustered index data and any associated indices during index execution. For example, while a clustered index is being rebuilt, you can continue to make updates to the underlying data and perform queries against the data.
Example 10.1 shows the creation of a nonclustered index.
Create an index for the emp_no column of the employee table:
USE sample;
CREATE INDEX i_empno ON employee (emp_no);
Code language: PHP (php)
Example 10.2 shows the creation of a unique composite index.
Create a composite index for the columns emp_no and project_no on the works_on table. The compound values in both columns must be unique. Eighty percent of each index leaf page should be filled.
USE sample;
CREATE UNIQUE INDEX i_empno_prno
ON works_on (emp_no, project_no)
WITH FILLFACTOR= 80;
Code language: PHP (php)
The creation of a unique index for a column is not possible if the column already contains duplicate values. The creation of such an index is possible if each existing data value (including the NULL value) occurs only once. Also, any attempt to insert or modify an existing data value into a column with an existing unique index will be rejected by the system.