For SQL Server 6.5 information, see CREATE INDEX Statement in What's New for SQL Server 6.5.
Creates an index on a given table that either changes the physical ordering of the table or provides the optimizer with a logical ordering of the table to increase efficiency for queries. When creating an index for the primary key, use the table- and column-level PRIMARY KEY constraint provided with the CREATE TABLE statement.
CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX index_name
ON [[database.]owner.]table_name (column_name [, column_name]...)
[WITH
[FILLFACTOR = x]
[[,] IGNORE_DUP_KEY]
[[,] {SORTED_DATA | SORTED_DATA_REORG}]
[[,] {IGNORE_DUP_ROW | ALLOW_DUP_ROW}]]
[ON segment_name]
where
Specifying a unique index makes sense only when uniqueness is a characteristic of the data itself. If uniqueness is required, create a UNIQUE or PRIMARY KEY constraint on the column. Specify a unique index only when manually enforcing a primary key (not recommended).
When a unique index exists, UPDATE or INSERT statements that would generate duplicate key values are rolled back, and SQL Server displays an error message. This is true even if the UPDATE or INSERT statement would have changed many rows but caused only one duplicate. If you try to change data on which there is a unique index and you have used the IGNORE_DUP_KEY option, only the rows that violate the UNIQUE index will fail. (For details, see the IGNORE_DUP_KEY option, later in this section.)
You cannot create a unique index on columns that already include duplicate values, whether or not IGNORE_DUP_KEY is set. If you attempt to do so, SQL Server displays an error message and lists the duplicate values. You must eliminate duplicates before you can create a unique index on the column.
Using a clustered index to find data is almost always faster than using a nonclustered index. In addition, using a clustered index is advantageous when many rows with contiguous key values are being retrieved ¾ that is, on columns that are often searched for ranges of values. Once the row with the first key value is found, rows with subsequent indexed values are guaranteed to be physically adjacent.
For example, it would be helpful on an employee table to include a nonclustered index on the primary key of emp_id; however, the clustered index could be created on lname, fname (last name, first name) as that is often how employees are grouped.
If you do not specify CLUSTERED, a nonclustered index will be created.
Note Because the leaf level of a clustered index and its data pages are the same by definition, creating a clustered index and using the ON segment_name clause effectively moves a table from the device on which the table was created to the new segment. Before creating tables or indexes on specific segments, verify which segments are available and that they have enough empty space for the index. It is important that the segment have at least 1.2 times the space required for the entire table.
You can have as many as 249 nonclustered indexes per table (regardless of how they are created: implicitly, with constraints, or explicitly, with CREATE INDEX). Each can provide access to the data in a different sorted order.
Composite indexes are used when two or more columns are best searched as a unit. You can combine as many as 16 columns into a single composite index. All the columns in a composite index must be in the same table. The maximum allowable size of the combined index values is 256 bytes. (That is, the sum of the lengths of the columns that make up the composite index cannot exceed 256.)
The amount of empty space on an index page is important because when an index page fills up, the system must take time to split it to make room for new rows. The value of the original FILLFACTOR is stored with the index in the sysindexes table.
Note Using an explicit FILLFACTOR setting applies only when the index is first created. SQL Server does not dynamically keep the specified percentage of empty space in the pages.
User-specified FILLFACTOR values can be from 1 through 100. If you don't specify a value, the default is 0. When FILLFACTOR is set to 0, only the leaf pages are filled. Space is left in nonleaf pages for at least one entry (two for nonunique clustered indexes). You can change the default with the sp_configure system stored procedure.
Use a FILLFACTOR of 100 only when there will be no inserts or updates. If FILLFACTOR is 100, SQL Server creates indexes with each page 100 percent full. A FILLFACTOR of 100 makes sense only for read-only tables. An INSERT or UPDATE made after the creation of an index with a 100 percent FILLFACTOR would cause page splits for each INSERT and possibly each UPDATE. The default FILLFACTOR algorithm assumes that there will be at least enough space for one more entry in an internal page.
Smaller FILLFACTOR values (except 0) cause SQL Server to create new indexes with pages that are not completely full. For example, a FILLFACTOR of 10 can be a reasonable choice if you are creating an index on a table that you know contains a small portion of the data it will eventually hold. Smaller FILLFACTOR values also cause each index to take more storage space.
When FILLFACTOR is set to any value other than 0 or 100, space is left in nonleaf pages for one entry (two for nonunique clustered indexes), and the space is left in leaf pages such that no leaf page is more full than the percentage specified by FILLFACTOR, as shown in the following table.
| FILLFACTOR | Internal page | Leaf page |
|---|---|---|
| 0% | One free slot* | 100% full |
| 1 - 99% | One free slot* | <= FILLFACTOR % full |
| 100% | 100% full | 100% full |
| *Two free slots for nonunique clustered indexes | ||
Important Creating a clustered index with a FILLFACTOR affects the amount of storage space your data occupies, since SQL Server redistributes the data when it creates the clustered index.
Other changes to the database caused by the UPDATE or INSERT attempt (for example, changes to index pages) are also backed out. However, if the UPDATE or INSERT attempt affects multiple rows, the other rows are added or changed as usual. If IGNORE_DUP_KEY is not specified, no rows will be inserted by the UPDATE or INSERT statement. For more information on duplicate rows, see the IGNORE_DUP_ROW | ALLOW_DUP_ROW option, later in this section.
You cannot create a unique index on a column that already includes duplicate values, whether or not IGNORE_DUP_KEY is set. If you attempt to do so, SQL Server displays an error message and lists the duplicate values. You must eliminate duplicates before you can create a unique index on the column.
Caution With IGNORE_DUP_KEY set, if you try to update a row in a way that creates a duplicate key, that row is discarded. Neither the new value nor the original value of the row that would produce the duplicate exists in the updated table. For example, if you try to update "Smith" to "Jones" and "Jones" already exists, you have one "Jones" and no "Smith." Essentially, the original row is lost because an UPDATE statement is actually a DELETE followed by an INSERT. SQL Server has no way to know about the disallowed duplicate when it deletes the row, and the whole transaction can't be rolled back because the purpose of IGNORE_DUP_KEY (and of the IGNORE_DUP_ROW option) is to allow a transaction to proceed in spite of the presence of duplicates.
SORTED_DATA_REORG differs from SORTED_DATA because it physically reorganizes the data. This option is useful when a FILLFACTOR is specified to compact or expand the pages on which a table is stored. The effects of these options change slightly if used with the ON segment_name option. For details, see the ON segment_name section, later in this section.
Reorganizing the data is a good idea when a table becomes fragmented. To determine whether or not a table is contiguous, use the DBCC statement's SHOW_CONTIG. For details about what causes table fragmentation and recommended solutions, see the DBCC statement.
The IGNORE_DUP_KEY, IGNORE_DUP_ROW, and ALLOW_DUP_ROW index options control what happens when a duplicate key or duplicate row is created with the INSERT or UPDATE statement. This table shows when these options can be used.
| Index type | Options |
|---|---|
| Clustered | IGNORE_DUP_ROW or ALLOW_DUP_ROW |
| Unique clustered | IGNORE_DUP_KEY |
| Nonclustered | None |
| Unique nonclustered | IGNORE_DUP_KEY |
This table illustrates how ALLOW_DUP_ROW and IGNORE_DUP_ROW affect attempts to create a nonunique clustered index on a table that includes duplicate rows and attempts to enter duplicate rows into a table.
Option set |
Table has duplicate rows |
Insert duplicate rows |
|---|---|---|
| Neither option | CREATE INDEX statement fails. | INSERT statement fails. |
| ALLOW_DUP_ROW | Statement is completed. | Statement is completed. |
| IGNORE_DUP_ROW | Index created but duplicate rows deleted; error message returned. | All rows accepted except duplicates; error message returned (see earlier caution). |
The ON segment_name clause can affect the placement of the data portion and the index portions of a table:
Caution If you create a table on a segment and then create a clustered index on that table without specifying a segment name, the entire table migrates to the default segment (unless the SORTED_DATA option is used).
Space is allocated to tables and indexes in increments of one extent (8 2K pages) at a time. Each time an extent is filled, another is allocated. For a report on the amount of space allocated and used by an index, use the sp_spaceused system stored procedure.
Creating a clustered index requires space available in your database equal to approximately 1.2 times the size of the data. This is space in addition to the space used by the existing table; the data is duplicated (unless SORTED_DATA is specified) in order to create the clustered index, and the old, nonindexed data is deleted when the index is complete.
You can neither create an index on a view nor create indexes on columns of bit, text, and image types. As the syntax signifies, you can create an index on a table in another database as long as you are the owner of that table.
You can create an index on a temporary table. When the table is dropped or the session ends, all indexes and triggers are dropped.
If there is no data in the table when an index is created, run the UPDATE STATISTICS statement after data is added. To check when the statistics were last updated, use DBCC SHOW_STATISTICS.
A composite index, like any other index, is represented by one row in the sysindexes table.
To display a report on an object's indexes, execute the sp_helpindex system stored procedure.
CREATE INDEX permission defaults to the table owner and is not transferable.
This example creates an index on the au_id column of the authors table.
CREATE INDEX au_id_ind
ON authors (au_id)
This example creates an index on the au_id column of the authors table that enforces uniqueness. This index will physically order the data on disk because the CLUSTERED option is specified.
CREATE UNIQUE CLUSTERED INDEX au_id_ind
ON authors (au_id)
This example creates an index on the au_id and title_id columns of the authors table.
CREATE INDEX ind1
ON titleauthor (au_id, title_id)
This example uses the FILLFACTOR option set to 100. A FILLFACTOR of 100 fills every page completely and is useful only when you know that no index values in the table will ever change.
CREATE NONCLUSTERED INDEX zip_ind
ON authors (zip)
WITH FILLFACTOR = 100
This example creates a unique clustered index on the stores table. If a duplicate key is entered, the INSERT or UPDATE statement will be ignored.
CREATE UNIQUE CLUSTERED INDEX stor_id_ind
ON stores(stor_id)
WITH IGNORE_DUP_KEY
| CREATE TABLE | sp_helpindex |
| DROP INDEX | sp_spaceused |
| INSERT | UPDATE |
| RECONFIGURE | UPDATE STATISTICS |
| SET |