Export (0) Print
Expand All

Index Transact-SQL Enhancements

SQL Server 2005

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 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

Community Additions

ADD
Show:
© 2014 Microsoft