Indexing

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.