Share via

Index Transact-SQL Enhancements

  • Article

A new Transact-SQL DDL statement is available for modifying relational and XML indexes. The CREATE INDEX statement has been enhanced to support XML index syntax, partitioning, and included (nonkey) columns. Several index options have been added. These include the ONLINE option that allows concurrent user access to the underlying data during some index operations.

New and Enhanced DDL Statements

New statements Description

ALTER INDEX

Modifies an existing table or view index (relational or XML) by disabling, rebuilding, or reorganizing the index; or by setting options on the index.
Enhanced statements Description

CREATE INDEX

Includes the following new syntax elements:

  • XML INDEX
  • INCLUDED (column)
  • PARTITION SCHEME

Adds the following index options:

  • ONLINE
  • ALLOW_ROW_LOCKS
  • ALLOW_PAGE_LOCKS
  • MAXDOP

DROP INDEX

Adds new format for specifying index and table names.

Adds the MOVE TO clause. This enables moving data from a clustered index to another filegroup or partition scheme.

Adds the following index options:

  • ONLINE
  • MAXDOP

See Also

Reference

Data Definition Language (DDL) Statements (Transact-SQL)

Other Resources

What's New and Enhanced in Transact-SQL (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance