Creating an Index
After the design has been determined, indexes can be created on the tables in a database.
Microsoft® SQL Server™ 2000 automatically creates unique indexes to enforce the uniqueness requirements of PRIMARY KEY and UNIQUE constraints. Unless a clustered index already exists on the table or a nonclustered index is explicitly specified, a unique, clustered index is created to enforce the PRIMARY KEY constraint. Unless a clustered index is explicitly specified, a unique, nonclustered index is created by default to enforce the UNIQUE constraint.
If you need to create an index that is independent of a constraint, you can use the CREATE INDEX statement. By default, a nonclustered index is created if the clustering option is not specified.
Additional considerations for creating an index include:
- Only the owner of the table can create indexes on the same table.
- Only one clustered index can be created per table.
- The maximum number of nonclustered indexes that can be created per table is 249 (including any indexes created by PRIMARY KEY or UNIQUE constraints).
- The maximum size of all nonvariable-length columns that comprise the index is 900 bytes. For example, a single index could not be created on three columns defined as char(300), char(300), and char (301) because the total width exceeds 900 bytes.
- The maximum number of columns that can comprise the same index is 16.
When you create indexes with the CREATE INDEX statement, you must specify the name of the index, table, and columns to which the index applies. New indexes created as part of a PRIMARY KEY or UNIQUE constraint or using SQL Server Enterprise Manager are automatically given system-defined names based on the database table name. If you create multiple indexes on a table, the index names are appended with _1, _2, and so on. The index can be renamed if necessary.
Note You cannot create an index in the current database while the current database is being backed up.
If a clustered index is created on a table with several secondary indexes, all of the secondary indexes must be rebuilt so that they contain the clustering key value instead of the row identifier (RID). Likewise, if a clustered index is deleted on a table that has several nonclustered indexes, the nonclustered indexes are all rebuilt as part of the DROP operation. This may take significant time on large tables.
The preferred way to build indexes on large tables is to start with the clustered index and then build the nonclustered indexes. When dropping all indexes, drop the nonclustered indexes first and the clustered index last. That way, no indexes need to be rebuilt.
Clustered Indexes
When you create a clustered index, the table is copied, the data in the table is sorted, and then the original table is deleted. Therefore, enough empty space must exist in the database to hold a copy of the data.
By default, the data in the table is sorted when the index is created. However, if the data is already sorted because the clustered index already exists and is being re-created using the same name and columns, the sort operation can be automatically skipped by rebuilding the index, rather than creating the index again. The rebuild operation checks that the rows are sorted while building the index. If any rows are not correctly sorted, the operations cancels and the index is not created.
Unique Indexes
Creating a unique index ensures that any attempt to duplicate key values fails. If a single query is created that causes duplicate and nonduplicate key values to be added, SQL Server rejects all rows, including the nonduplicate key values. For example, if a single insert statement retrieves 20 rows from table A and inserts them into table B, and 10 of those rows contain duplicate key values, by default all 20 rows are rejected. However, the IGNORE_DUP_KEY clause can be specified when creating the index that causes only the duplicate key values to be rejected; the nonduplicate key values are added. In the previous example, only the 10 duplicate key values would be rejected; the other 10 nonduplicate key values would be inserted into table B.
A unique index cannot be created if there are any duplicate key values. For example, if you want to create a unique,
Note You cannot create a unique index on a single column if that column contains NULL in more than one row. Similarly, you cannot create a unique index on multiple columns if the combination of columns contains NULL in more than one row. These are treated as duplicate values for indexing purposes.
To create an index when creating a table
Transact-SQLTo create an index on an existing table
You can also create an index using the Create Index Wizard in SQL Server Enterprise Manager.
To create an index using the Create Index Wizard