Creates an index on a given table. This statement either changes the physical ordering of the table or provides the optimizer with a logical ordering of the table to increase query efficiency. When an index is created for the primary key, use the table- and column-level PRIMARY KEY constraints by specifying the PRIMARY KEY keywords provided with either the CREATE TABLE or ALTER TABLE statements.
Only the table owner can create indexes on that table. The owner of a table can create an index at any time, whether or not there is data in the table. Indexes can be created on tables in another database by specifying a qualified database name.
CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED]
INDEX index_name ON table (column [,...n])
[WITH
[PAD_INDEX]
[[,] FILLFACTOR = fillfactor]
[[,] IGNORE_DUP_KEY]
[[,] DROP_EXISTING]
[[,] STATISTICS_NORECOMPUTE]
]
[ON filegroup]
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 changes many rows but causes only one duplicate. If an attempt is made to enter data for which there is a unique index and the IGNORE_DUP_KEY clause is specified, only the rows violating the UNIQUE index fail. When processing an UPDATE statement, IGNORE_DUP_KEY has no effect. (For more information, see the IGNORE_DUP_KEY clause, later in this topic.)
SQL Server does not allow the creation of a unique index on columns that already include duplicate values, whether or not IGNORE_DUP_KEY is set. If attempted, SQL Server displays an error message; duplicates must be eliminated before a unique index can be created on the column(s).
If CLUSTERED is not specified, a nonclustered index is 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 filegroup clause effectively moves a table from the file on which the table was created to the new filegroup. Before creating tables or indexes on specific filegroups, verify which filegroups are available and that they have enough empty space for the index. It is vital that the filegroup have at least 1.2 times the space required for the entire table.
Each table can have as many as 249 nonclustered indexes (regardless of how they are created: implicitly with PRIMARY KEY and UNIQUE constraints, or explicitly with CREATE INDEX). Each index can provide access to the data in a different sort order.
Note A computed column, as created in a CREATE TABLE statement, cannot be used as a key column or as part of any PRIMARY KEY, UNIQUE, FOREIGN KEY, or CHECK constraint definition.
Note Columns consisting of the ntext, text, image, or bit data types or computed columns cannot be specified as columns for an index, nor can functions be used.
Composite indexes are used when two or more columns are best searched as a unit or if many queries reference only the columns that are specified in the index. As many as 16 columns can be combined 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 900 bytes. (That is, the sum of the lengths of the columns that make up the composite index cannot exceed 900 bytes.)
Note The number of rows on an intermediate index page is never less than two, regardless of how low the value of FILLFACTOR.
When FILLFACTOR is specified, SQL Server rounds up the number of rows to be placed on each page. For example, issuing CREATE CLUSTERED INDEX ... FILLFACTOR = 33 creates a clustered index with a FILLFACTOR of 33 percent. Assume that SQL Server calculates that 5.2 rows is 33 percent of the space on a page. SQL Server rounds so that 6 rows are placed on each page.
Note 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 no value is specified, the default value is 0. When FILLFACTOR is set to 0, only the leaf pages are filled. Space is left in nonleaf pages for at least one row. You can change the default FILLFACTOR setting by executing sp_configure.
Use a FILLFACTOR of 100 only if no INSERT or UPDATE statements will occur, such as with a read-only table. If FILLFACTOR is 100, SQL Server creates indexes with leaf pages 100 percent full. An INSERT or UPDATE made after the creation of an index with a 100 percent FILLFACTOR causes page splits for each INSERT and possibly each UPDATE.
Setting FILLFACTOR to 100 fills the leaf pages of the index to full and leaves space to accommodate one row of maximum size on the intermediate pages.
Smaller FILLFACTOR values (except 0) cause SQL Server to create new indexes with leaf pages that are not completely full. For example, a FILLFACTOR of 10 can be a reasonable choice when creating an index on a table that is known to contain a small portion of the data that 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 row (two for nonunique clustered indexes), and space is left in leaf pages so that no leaf page is more full than the percentage specified by FILLFACTOR, as shown in the table.
| FILLFACTOR | Intermediate page | Leaf page |
|---|---|---|
| 0% | One free entry | 100% full |
| 1 - 99% | One free entry | <= FILLFACTOR % full |
| 100% | One free entry | 100% full |
One free entry is the space on the page that can accommodate another index entry.
Important Creating a clustered index with a FILLFACTOR affects the amount of storage space the data occupies because SQL Server redistributes the data when it creates the clustered index.
If IGNORE_DUP_KEY was not specified for the index, SQL Server issues an error message and rolls back the entire INSERT statement.
A unique index cannot be created on a column that already includes duplicate values, whether or not IGNORE_DUP_KEY is set. If attempted, SQL Server displays an error message and lists the duplicate values. Eliminate the duplicate values before creating a unique index on the column.
The table shows when IGNORE_DUP_KEY can be used.
| Index type | Options |
|---|---|
| Clustered | Not allowed |
| Unique clustered | IGNORE_DUP_KEY allowed |
| Nonclustered | Not allowed |
| Unique nonclustered | IGNORE_DUP_KEY allowed |
The DROP_EXISTING clause enhances performance when recreating a clustered index (with either the same or a different set of keys) on a table that also has nonclustered indexes. The DROP_EXISTING clause replaces the execution of a DROP INDEX statement on the old clustered index followed by the execution of a CREATE INDEX statement for the new clustered index. The nonclustered indexes are rebuilt once, and only if the keys are different.
If the keys do not change (the same index name and columns as the original index are provided), the DROP_EXISTING clause does not sort the data again. This can be useful if the index must be compacted.
Note When executing a CREATE INDEX statement with the DROP_EXISTING clause, SQL Server assumes that the index is consistent (there is no corruption in the index). The rows in the specified index should be sorted by the specified key referenced in the CREATE INDEX statement.
Important Disabling automatic recomputation of distribution statistics may prevent the SQL Server optimizer from picking optimal execution plans for queries involving the table.
Space is allocated to tables and indexes in increments of one extent (eight 8-KB pages) at a time. Each time an extent is filled, another is allocated. Indexes on very small or empty tables will use single page allocations until eight pages have been added to the index and then will switch to extent allocations. For a report on the amount of space allocated and used by an index, use sp_spaceused.
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 in order to create the clustered index, and the old, nonindexed data is deleted when the index is complete. When using the DROP_EXISTING clause, the space needed for the clustered index is the amount of space equal to the existing index’s space requirements.
Note Because the maximum allowable index size is 900 bytes, large char, varchar, binary, and varbinary columns (greater than 900 bytes) or nchar or nvarchar columns (greater than 450 characters) cannot be used in an index.
If CREATE INDEX is performed in the context of an explicit transaction, allocation locks are taken and held until the explicit transaction is committed. Therefore, large index creation operations may consume large amounts of memory for obtaining these locks, or the index creation may fail if lock space is exhausted. If the same CREATE INDEX statement is performed using an implicit transaction, it does not fail because allocation locks are not held for implicit transactions. For more information, see Transactions.
Indexes can be created on a temporary table. When the table is dropped or the session ends, all indexes and triggers are dropped.
To display a report on an object’s indexes, execute sp_helpindex.
Entire filegroups affected by a CREATE INDEX statement since the last filegroup backup must be backed up as a unit. For more information about file and filegroup backups, see BACKUP.
If CREATE INDEX is executed during a database or transaction log backup, SQL Server ends the BACKUP statement and the backup operation fails.
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.
SET NOCOUNT OFF
USE pubs
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'au_id_ind')
DROP INDEX authors.au_id_ind
GO
USE pubs
CREATE INDEX au_id_ind
ON authors (au_id)
GO
This example creates an index on the employeeID column of the emp_pay table that enforces uniqueness. This index physically orders the data on disk because the CLUSTERED clause is specified.
SET NOCOUNT ON
USE pubs
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'emp_pay')
DROP TABLE emp_pay
GO
USE pubs
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'employeeID_ind')
DROP INDEX emp_pay.employeeID_ind
GO
USE pubs
GO
CREATE TABLE emp_pay
(
employeeID int NOT NULL,
base_pay money NOT NULL,
commission decimal(2, 2) NOT NULL
)
INSERT emp_pay
VALUES (1, 500, .10)
INSERT emp_pay
VALUES (2, 1000, .05)
INSERT emp_pay
VALUES (3, 800, .07)
INSERT emp_pay
VALUES (5, 1500, .03)
INSERT emp_pay
VALUES (9, 750, .06)
GO
SET NOCOUNT OFF
CREATE UNIQUE CLUSTERED INDEX employeeID_ind
ON emp_pay (employeeID)
GO
This example creates an index on the orderID and employeeID columns of the order_emp table.
SET NOCOUNT ON
USE pubs
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'order_emp')
DROP TABLE order_emp
GO
USE pubs
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'emp_order_ind')
DROP INDEX order_emp.emp_order_ind
GO
USE pubs
GO
CREATE TABLE order_emp
(
orderID int IDENTITY(1000, 1),
employeeID int NOT NULL,
orderdate datetime NOT NULL DEFAULT GETDATE(),
orderamount money NOT NULL
)
INSERT order_emp (employeeID, orderdate, orderamount)
VALUES (5, '4/12/98', 315.19)
INSERT order_emp (employeeID, orderdate, orderamount)
VALUES (5, '5/30/98', 1929.04)
INSERT order_emp (employeeID, orderdate, orderamount)
VALUES (1, '1/03/98', 2039.82)
INSERT order_emp (employeeID, orderdate, orderamount)
VALUES (1, '1/22/98', 445.29)
INSERT order_emp (employeeID, orderdate, orderamount)
VALUES (4, '4/05/98', 689.39)
INSERT order_emp (employeeID, orderdate, orderamount)
VALUES (7, '3/21/98', 1598.23)
INSERT order_emp (employeeID, orderdate, orderamount)
VALUES (7, '3/21/98', 445.77)
INSERT order_emp (employeeID, orderdate, orderamount)
VALUES (7, '3/22/98', 2178.98)
GO
SET NOCOUNT OFF
CREATE INDEX emp_order_ind
ON order_emp (orderID, employeeID)
This example uses the FILLFACTOR clause set to 100. A FILLFACTOR of 100 fills every page completely and is useful only when you know that index values in the table will never change.
SET NOCOUNT OFF
USE pubs
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'zip_ind')
DROP INDEX authors.zip_ind
GO
USE pubs
GO
CREATE NONCLUSTERED INDEX zip_ind
ON authors (zip)
WITH FILLFACTOR = 100
This example creates a unique clustered index on the emp_pay table. If a duplicate key is entered, the INSERT or UPDATE statement is ignored.
SET NOCOUNT ON
USE pubs
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'emp_pay')
DROP TABLE emp_pay
GO
USE pubs
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'employeeID_ind')
DROP INDEX emp_pay.employeeID_ind
GO
USE pubs
GO
CREATE TABLE emp_pay
(
employeeID int NOT NULL,
base_pay money NOT NULL,
commission decimal(2, 2) NOT NULL
)
INSERT emp_pay
VALUES (1, 500, .10)
INSERT emp_pay
VALUES (2, 1000, .05)
INSERT emp_pay
VALUES (3, 800, .07)
INSERT emp_pay
VALUES (5, 1500, .03)
INSERT emp_pay
VALUES (9, 750, .06)
GO
SET NOCOUNT OFF
GO
CREATE UNIQUE CLUSTERED INDEX employeeID_ind
ON emp_pay(employeeID)
WITH IGNORE_DUP_KEY
This example creates an index on the author’s identification number in the authors table. Without the PAD_INDEX clause, SQL Server creates leaf pages that are 10 percent full, but the pages are filled almost completely. With PAD_INDEX, the intermediate pages are also 10 percent full.
Note At least two entries appear on the index pages of unique clustered indexes when PAD_INDEX is not specified.
SET NOCOUNT OFF
USE pubs
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'au_id_ind')
DROP INDEX authors.au_id_ind
GO
USE pubs
CREATE INDEX au_id_ind
ON authors (au_id)
WITH PAD_INDEX, FILLFACTOR = 10