ALTER INDEX (Transact-SQL)

  • Article

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

Topic link icon Transact-SQL Syntax Conventions

Syntax

ALTER INDEX { index_name | ALL }
    ON <object>
    { REBUILD 
        [ [PARTITION = ALL]
          [ WITH ( <rebuild_index_option> [ ,...n ] ) ] 
          | [ PARTITION = partition_number 
                [ WITH ( <single_partition_rebuild_index_option>
                        [ ,...n ] )
                ] 
            ]
        ]
    | DISABLE
    | REORGANIZE 
        [ PARTITION = partition_number ]
        [ WITH ( LOB_COMPACTION = { ON | OFF } ) ]
  | SET ( <set_index_option> [ ,...n ] ) 
    }
[ ; ]

<object> ::=
{
    [ database_name. [ schema_name ] . | schema_name. ]
    table_or_view_name
}

<rebuild_index_option > ::=
{
    PAD_INDEX = { ON | OFF }
  | FILLFACTOR = fillfactor 
  | SORT_IN_TEMPDB = { ON | OFF }
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | ONLINE = { ON | OFF } 
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
  | DATA_COMPRESSION = { NONE | ROW | PAGE } 
     [ ON PARTITIONS ( { <partition_number_expression> | <range> } 
     [ , ...n ] ) ]
}
<range> ::= 
<partition_number_expression> TO <partition_number_expression>
}

<single_partition_rebuild_index_option> ::=
{
    SORT_IN_TEMPDB = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
  | DATA_COMPRESSION = { NONE | ROW | PAGE } }
}

<set_index_option>::=
{
    ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
}

Arguments

  • index_name
    Is the name of the index. Index names must be unique within a table or view but do not have to be unique within a database. Index names must follow the rules of identifiers.

  • ALL
    Specifies all indexes associated with the table or view regardless of the index type. Specifying ALL causes the statement to fail if one or more indexes are in an offline or read-only filegroup or the specified operation is not allowed on one or more index types. The following table lists the index operations and disallowed index types.

    Specifying ALL with this operation

    Fails if the table has one or more

    REBUILD WITH ONLINE = ON

    XML index

    Spatial index

    REBUILD PARTITION = partition_number

    Nonpartitioned index, XML index, spatial index, or disabled index

    REORGANIZE

    Indexes with ALLOW_PAGE_LOCKS set to OFF

    REORGANIZE PARTITION = partition_number

    Nonpartitioned index, XML index, spatial index, or disabled index

    IGNORE_DUP_KEY = ON

    Spatial index

    XML index

    ONLINE = ON

    Spatial index

    XML index

    Warning

    For more detailed information about index operations that can be performed online, see Guidelines for Online Index Operations.

    If ALL is specified with PARTITION = partition_number, all indexes must be aligned. This means that they are partitioned based on equivalent partition functions. Using ALL with PARTITION causes all index partitions with the same partition_number to be rebuilt or reorganized. For more information about partitioned indexes, see Partitioned Tables and Indexes.

  • database_name
    Is the name of the database.

  • schema_name
    Is the name of the schema to which the table or view belongs.

  • table_or_view_name
    Is the name of the table or view associated with the index. To display a report of the indexes on an object, use the sys.indexes catalog view.

  • REBUILD [ WITH (<rebuild_index_option> [ ,... n]) ]
    Specifies the index will be rebuilt using the same columns, index type, uniqueness attribute, and sort order. This clause is equivalent to DBCC DBREINDEX. REBUILD enables a disabled index. Rebuilding a clustered index does not rebuild associated nonclustered indexes unless the keyword ALL is specified. If index options are not specified, the existing index option values stored in sys.indexes are applied. For any index option whose value is not stored in sys.indexes, the default indicated in the argument definition of the option applies.

    When you rebuild an XML index or a spatial index, the options ONLINE = ON and IGNORE_DUP_KEY = ON are not valid.

    If ALL is specified and the underlying table is a heap, the rebuild operation has no effect on the table. Any nonclustered indexes associated with the table are rebuilt.

    The rebuild operation can be minimally logged if the database recovery model is set to either bulk-logged or simple.

    Note

    When you rebuild a primary XML index, the underlying user table is unavailable for the duration of the index operation.

  • PARTITION
    Specifies that only one partition of an index will be rebuilt or reorganized. PARTITION cannot be specified if index_name is not a partitioned index.

    PARTITION = ALL rebuilds all partitions.

    Warning

    Creating and rebuilding nonaligned indexes on a table with more than 1,000 partitions is possible, but is not supported. Doing so may cause degraded performance or excessive memory consumption during these operations. We recommend using only aligned indexes when the number of partitions exceed 1,000.

  • partition_number
    Is the partition number of a partitioned index that is to be rebuilt or reorganized. partition_number is a constant expression that can reference variables. These include user-defined type variables or functions and user-defined functions, but cannot reference a Transact-SQL statement. partition_number must exist or the statement fails.

  • WITH (<single_partition_rebuild_index_option>)
    SORT_IN_TEMPDB, MAXDOP, and DATA_COMPRESSION are the options that can be specified when you rebuild a single partition (PARTITION = n). XML indexes cannot be specified in a single partition rebuild operation.

    Rebuilding a partitioned index cannot be performed online. The entire table is locked during this operation.

  • DISABLE
    Marks the index as disabled and unavailable for use by the Database Engine. Any index can be disabled. The index definition of a disabled index remains in the system catalog with no underlying index data. Disabling a clustered index prevents user access to the underlying table data. To enable an index, use ALTER INDEX REBUILD or CREATE INDEX WITH DROP_EXISTING. For more information, see Disable Indexes and Constraints and Enable Indexes and Constraints.

  • REORGANIZE
    Specifies the index leaf level will be reorganized. ALTER INDEX REORGANIZE statement is always performed online. This means long-term blocking table locks are not held and queries or updates to the underlying table can continue during the ALTER INDEX REORGANIZE transaction. REORGANIZE cannot be specified for a disabled index or an index with ALLOW_PAGE_LOCKS set to OFF.

  • WITH ( LOB_COMPACTION = { ON | OFF } )
    Specifies that all pages that contain large object (LOB) data are compacted. The LOB data types are image, text, ntext, varchar(max), nvarchar(max), varbinary(max), and xml. Compacting this data can improve disk space use. The default is ON.

    • ON
      All pages that contain large object data are compacted.

      Reorganizing a specified clustered index compacts all LOB columns that are contained in the clustered index. Reorganizing a nonclustered index compacts all LOB columns that are nonkey (included) columns in the index.

      When ALL is specified, all indexes that are associated with the specified table or view are reorganized, and all LOB columns that are associated with the clustered index, underlying table, or nonclustered index with included columns are compacted.

    • OFF
      Pages that contain large object data are not compacted.

      OFF has no effect on a heap.

    The LOB_COMPACTION clause is ignored if LOB columns are not present.

  • SET ( <set_index option> [ ,... n] )
    Specifies index options without rebuilding or reorganizing the index. SET cannot be specified for a disabled index.

  • PAD_INDEX = { ON | OFF }
    Specifies index padding. The default is OFF.

    • ON
      The percentage of free space that is specified by FILLFACTOR is applied to the intermediate-level pages of the index. If FILLFACTOR is not specified at the same time PAD_INDEX is set to ON, the fill factor value stored in sys.indexes is used.

    • OFF or fillfactor is not specified
      The intermediate-level pages are filled to near capacity. This leaves sufficient space for at least one row of the maximum size that the index can have, based on the set of keys on the intermediate pages.

    For more information, see CREATE INDEX (Transact-SQL).

  • FILLFACTOR = fillfactor
    Specifies a percentage that indicates how full the Database Engine should make the leaf level of each index page during index creation or alteration. fillfactor must be an integer value from 1 to 100. The default is 0.

    Note

    Fill factor values 0 and 100 are the same in all respects.

    An explicit FILLFACTOR setting applies only when the index is first created or rebuilt. The Database Engine does not dynamically keep the specified percentage of empty space in the pages. For more information, see CREATE INDEX (Transact-SQL).

    To view the fill factor setting, use sys.indexes.

    Important

    Creating or altering a clustered index with a FILLFACTOR value affects the amount of storage space the data occupies, because the Database Engine redistributes the data when it creates the clustered index.

  • SORT_IN_TEMPDB = { ON | OFF }
    Specifies whether to store the sort results in tempdb. The default is OFF.

    • ON
      The intermediate sort results that are used to build the index are stored in tempdb. If tempdb is on a different set of disks than the user database, this may reduce the time needed to create an index. However, this increases the amount of disk space that is used during the index build.

    • OFF
      The intermediate sort results are stored in the same database as the index.

    If a sort operation is not required, or if the sort can be performed in memory, the SORT_IN_TEMPDB option is ignored.

    For more information, see SORT_IN_TEMPDB Option For Indexes.

  • IGNORE_DUP_KEY = { ON | OFF }
    Specifies the error response when an insert operation attempts to insert duplicate key values into a unique index. The IGNORE_DUP_KEY option applies only to insert operations after the index is created or rebuilt. The default is OFF.

    • ON
      A warning message will occur when duplicate key values are inserted into a unique index. Only the rows violating the uniqueness constraint will fail.

    • OFF
      An error message will occur when duplicate key values are inserted into a unique index. The entire INSERT operation will be rolled back.

    IGNORE_DUP_KEY cannot be set to ON   for indexes created on a view, non-unique indexes, XML indexes, spatial indexes, and filtered indexes.

    To view IGNORE_DUP_KEY, use sys.indexes.

    In backward compatible syntax, WITH IGNORE_DUP_KEY is equivalent to WITH IGNORE_DUP_KEY = ON.

  • STATISTICS_NORECOMPUTE = { ON | OFF }
    Specifies whether distribution statistics are recomputed. The default is OFF.

    • ON
      Out-of-date statistics are not automatically recomputed.

    • OFF
      Automatic statistics updating are enabled.

    To restore automatic statistics updating, set the STATISTICS_NORECOMPUTE to OFF, or execute UPDATE STATISTICS without the NORECOMPUTE clause.

    Important

    Disabling automatic recomputation of distribution statistics may prevent the query optimizer from picking optimal execution plans for queries that involve the table.

  • ONLINE = { ON | OFF }
    Specifies whether underlying tables and associated indexes are available for queries and data modification during the index operation. The default is OFF.

    For an XML index or spatial index, only ONLINE = OFF is supported, and if ONLINE is set to ON an error is raised.

    Note

    Online index operations are not available in every edition of Microsoft SQL Server. For a list of features that are supported by the editions of SQL Server, see Features Supported by the Editions of SQL Server 2012.

    • ON
      Long-term table locks are not held for the duration of the index operation. During the main phase of the index operation, only an Intent Share (IS) lock is held on the source table. This allows queries or updates to the underlying table and indexes to continue. At the start of the operation, a Shared (S) lock is very briefly held on the source object. At the end of the operation, an S lock is very briefly held on the source if a nonclustered index is being created, or an SCH-M (Schema Modification) lock is acquired when a clustered index is created or dropped online, or when a clustered or nonclustered index is being rebuilt. ONLINE cannot be set to ON when an index is being created on a local temporary table.

    • OFF
      Table locks are applied for the duration of the index operation. An offline index operation that creates, rebuilds, or drops a clustered, spatial, or XML index, or rebuilds or drops a nonclustered index, acquires a Schema modification (Sch-M) lock on the table. This prevents all user access to the underlying table for the duration of the operation. An offline index operation that creates a nonclustered index acquires a Shared (S) lock on the table. This prevents updates to the underlying table but allows read operations, such as SELECT statements.

    For more information, see How Online Index Operations Work.

    Indexes, including indexes on global temp tables, can be rebuilt online with the following exceptions:

    • XML indexes

    • Indexes on local temp tables

    • A subset of a partitioned index (An entire partitioned index can be rebuilt online.)

  • ALLOW_ROW_LOCKS = { ON | OFF }
    Specifies whether row locks are allowed. The default is ON.

    • ON
      Row locks are allowed when accessing the index. The Database Engine determines when row locks are used.

    • OFF
      Row locks are not used.

  • ALLOW_PAGE_LOCKS = { ON | OFF }
    Specifies whether page locks are allowed. The default is ON.

    • ON
      Page locks are allowed when you access the index. The Database Engine determines when page locks are used.

    • OFF
      Page locks are not used.

    Note

    An index cannot be reorganized when ALLOW_PAGE_LOCKS is set to OFF.

  • MAXDOP **=**max_degree_of_parallelism
    Overrides the max degree of parallelism configuration option for the duration of the index operation. For more information, see Configure the max degree of parallelism Server Configuration Option. Use MAXDOP to limit the number of processors used in a parallel plan execution. The maximum is 64 processors.

    Important

    Although the MAXDOP option is syntactically supported for all XML indexes, for a spatial index or a primary XML index, ALTER INDEX currently uses only a single processor.

    max_degree_of_parallelism can be:

    • 1
      Suppresses parallel plan generation.

    • >1
      Restricts the maximum number of processors used in a parallel index operation to the specified number.

    • 0 (default)
      Uses the actual number of processors or fewer based on the current system workload.

    For more information, see Configure Parallel Index Operations.

    Note

    Parallel index operations are not available in every edition of Microsoft SQL Server. For a list of features that are supported by the editions of SQL Server, see Features Supported by the Editions of SQL Server 2012.

  • DATA_COMPRESSION
    Specifies the data compression option for the specified index, partition number, or range of partitions. The options are as follows:

    • NONE
      Index or specified partitions are not compressed.

    • ROW
      Index or specified partitions are compressed by using row compression.

    • PAGE
      Index or specified partitions are compressed by using page compression.

    For more information about compression, see Data Compression.

  • ON PARTITIONS ( { <partition_number_expression> | <range> } [,...n] )
    Specifies the partitions to which the DATA_COMPRESSION setting applies. If the index is not partitioned, the ON PARTITIONS argument will generate an error. If the ON PARTITIONS clause is not provided, the DATA_COMPRESSION option applies to all partitions of a partitioned index.

    <partition_number_expression> can be specified in the following ways:

    • Provide the number for a partition, for example: ON PARTITIONS (2).

    • Provide the partition numbers for several individual partitions separated by commas, for example: ON PARTITIONS (1, 5).

    • Provide both ranges and individual partitions: ON PARTITIONS (2, 4, 6 TO 8).

    <range> can be specified as partition numbers separated by the word TO, for example: ON PARTITIONS (6 TO 8).

    To set different types of data compression for different partitions, specify the DATA_COMPRESSION option more than once, for example:

    REBUILD WITH 
(
DATA_COMPRESSION = NONE ON PARTITIONS (1), 
DATA_COMPRESSION = ROW ON PARTITIONS (2, 4, 6 TO 8), 
DATA_COMPRESSION = PAGE ON PARTITIONS (3, 5)
)

Remarks

ALTER INDEX cannot be used to repartition an index or move it to a different filegroup. This statement cannot be used to modify the index definition, such as adding or deleting columns or changing the column order. Use CREATE INDEX with the DROP_EXISTING clause to perform these operations.

When an option is not explicitly specified, the current setting is applied. For example, if a FILLFACTOR setting is not specified in the REBUILD clause, the fill factor value stored in the system catalog will be used during the rebuild process. To view the current index option settings, use sys.indexes.

Note

The values for ONLINE, MAXDOP, and SORT_IN_TEMPDB are not stored in the system catalog. Unless specified in the index statement, the default value for the option is used.

On multiprocessor computers, just like other queries do, ALTER INDEX REBUILD automatically uses more processors to perform the scan and sort operations that are associated with modifying the index. When you run ALTER INDEX REORGANIZE, with or without LOB_COMPACTION, the max degree of parallelism value is a single threaded operation. For more information, see Configure Parallel Index Operations.

An index cannot be reorganized or rebuilt if the filegroup in which it is located is offline or set to read-only. When the keyword ALL is specified and one or more indexes are in an offline or read-only filegroup, the statement fails.

Rebuilding Indexes

Rebuilding an index drops and re-creates the index. This removes fragmentation, reclaims disk space by compacting the pages based on the specified or existing fill factor setting, and reorders the index rows in contiguous pages. When ALL is specified, all indexes on the table are dropped and rebuilt in a single transaction. FOREIGN KEY constraints do not have to be dropped in advance. When indexes with 128 extents or more are rebuilt, the Database Engine defers the actual page deallocations, and their associated locks, until after the transaction commits.

Rebuilding or reorganizing small indexes often does not reduce fragmentation. The pages of small indexes are stored on mixed extents. Mixed extents are shared by up to eight objects, so the fragmentation in a small index might not be reduced after reorganizing or rebuilding it.

In SQL Server 2012, statistics are not created by scanning all the rows in the table when a partitioned index is created or rebuilt. Instead, the query optimizer uses the default sampling algorithm to generate statistics. To obtain statistics on partitioned indexes by scanning all the rows in the table, use CREATE STATISTICS or UPDATE STATISTICS with the FULLSCAN clause.

In earlier versions of SQL Server, you could sometimes rebuild a nonclustered index to correct inconsistencies caused by hardware failures. In SQL Server 2008 and later, you may still be able to repair such inconsistencies between the index and the clustered index by rebuilding a nonclustered index offline. However, you cannot repair nonclustered index inconsistencies by rebuilding the index online, because the online rebuild mechanism will use the existing nonclustered index as the basis for the rebuild and thus persist the inconsistency. Rebuilding the index offline, by contrast, will force a scan of the clustered index (or heap) and so remove the inconsistency. As with earlier versions, we recommend recovering from inconsistencies by restoring the affected data from a backup; however, you may be able to repair the index inconsistencies by rebuilding the nonclustered index offline. For more information, see DBCC CHECKDB (Transact-SQL).

Reorganizing Indexes

Reorganizing an index uses minimal system resources. It defragments the leaf level of clustered and nonclustered indexes on tables and views by physically reordering the leaf-level pages to match the logical, left to right, order of the leaf nodes. Reorganizing also compacts the index pages. Compaction is based on the existing fill factor value. To view the fill factor setting, use sys.indexes.

When ALL is specified, relational indexes, both clustered and nonclustered, and XML indexes on the table are reorganized. Some restrictions apply when specifying ALL, see the definition for ALL in the Arguments section.

For more information, see Reorganize and Rebuild Indexes.

Disabling Indexes

Disabling an index prevents user access to the index, and for clustered indexes, to the underlying table data. The index definition remains in the system catalog. Disabling a nonclustered index or clustered index on a view physically deletes the index data. Disabling a clustered index prevents access to the data, but the data remains unmaintained in the B-tree until the index is dropped or rebuilt. To view the status of an enabled or disabled index, query the is_disabled column in the sys.indexes catalog view.

If a table is in a transactional replication publication, you cannot disable any indexes that are associated with primary key columns. These indexes are required by replication. To disable an index, you must first drop the table from the publication. For more information, see Publish Data and Database Objects.

Use the ALTER INDEX REBUILD statement or the CREATE INDEX WITH DROP_EXISTING statement to enable the index. Rebuilding a disabled clustered index cannot be performed with the ONLINE option set to ON. For more information, see Disable Indexes and Constraints.

Setting Options

You can set the options ALLOW_ROW_LOCKS, ALLOW_PAGE_LOCKS, IGNORE_DUP_KEY and STATISTICS_NORECOMPUTE for a specified index without rebuilding or reorganizing that index. The modified values are immediately applied to the index. To view these settings, use sys.indexes. For more information, see Set Index Options.

Row and Page Locks Options

When ALLOW_ROW_LOCKS = ON and ALLOW_PAGE_LOCK = ON, row-level, page-level, and table-level locks are allowed when you access the index. The Database Engine chooses the appropriate lock and can escalate the lock from a row or page lock to a table lock.

When ALLOW_ROW_LOCKS = OFF and ALLOW_PAGE_LOCK = OFF, only a table-level lock is allowed when you access the index.

If ALL is specified when the row or page lock options are set, the settings are applied to all indexes. When the underlying table is a heap, the settings are applied in the following ways:

ALLOW_ROW_LOCKS = ON or OFF

To the heap and any associated nonclustered indexes.

ALLOW_PAGE_LOCKS = ON

To the heap and any associated nonclustered indexes.

ALLOW_PAGE_LOCKS = OFF

Fully to the nonclustered indexes. This means that all page locks are not allowed on the nonclustered indexes. On the heap, only the shared (S), update (U) and exclusive (X) locks for the page are not allowed. The Database Engine can still acquire an intent page lock (IS, IU or IX) for internal purposes.

Online Index Operations

When rebuilding an index and the ONLINE option is set to ON, the underlying objects, the tables and associated indexes, are available for queries and data modification. Exclusive table locks are held only for a very short amount of time during the alteration process.

Reorganizing an index is always performed online. The process does not hold locks long term and, therefore, does not block queries or updates that are running.

You can perform concurrent online index operations on the same table only when doing the following:

  • Creating multiple nonclustered indexes.

  • Reorganizing different indexes on the same table.

  • Reorganizing different indexes while rebuilding nonoverlapping indexes on the same table.

All other online index operations performed at the same time fail. For example, you cannot rebuild two or more indexes on the same table concurrently, or create a new index while rebuilding an existing index on the same table.

For more information, see Perform Index Operations Online.

Spatial Index Restrictions

When you rebuild a spatial index, the underlying user table is unavailable for the duration of the index operation because the spatial index holds a schema lock.

The PRIMARY KEY constraint in the user table cannot be modified while a spatial index is defined on a column of that table. To change the PRIMARY KEY constraint, first drop every spatial index of the table. After modifying the PRIMARY KEy constraint, you can re-create each of the spatial indexes.

In a single partition rebuild operation, you cannot specify any spatial indexes. However, you can specify spatial indexes in a complete partition rebuild.

To change options that are specific to a spatial index, such as BOUNDING_BOX or GRID, you can either use a CREATE SPATIAL INDEX statement that specifies DROP_EXISTING = ON, or drop the spatial index and create a new one. For an example, see CREATE SPATIAL INDEX (Transact-SQL).

Columnstore Index Restrictions

Except for the REBUILD option, an xVelocity memory optimized columnstore index cannot be altered. Drop and recreate the columnstore index instead.

Data Compression

For a more information about data compression, see Data Compression.

To evaluate how changing the compression state will affect a table, an index, or a partition, use the sp_estimate_data_compression_savings stored procedure.

The following restrictions apply to partitioned indexes:

  • When you use ALTER INDEX ALL ..., you cannot change the compression setting of a single partition if the table has nonaligned indexes.

  • The ALTER INDEX <index> ... REBUILD PARTITION ... syntax rebuilds the specified partition of the index.

  • The ALTER INDEX <index> ... REBUILD WITH ... syntax rebuilds all partitions of the index.

Statistics

When you execute ALTER INDEX ALL … on a table, only the statistics associates with indexes are updated. Automatic or manual statistics created on the table (instead of an index) are not updated.

Permissions

To execute ALTER INDEX, at a minimum, ALTER permission on the table or view is required.

Examples

A. Rebuilding an index

The following example rebuilds a single index on the Employee table.

USE AdventureWorks2012;
GO
ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD;
GO

B. Rebuilding all indexes on a table and specifying options

The following example specifies the keyword ALL. This rebuilds all indexes associated with the table. Three options are specified.

USE AdventureWorks2012;
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
              STATISTICS_NORECOMPUTE = ON);
GO

C. Reorganizing an index with LOB compaction

The following example reorganizes a single clustered index. Because the index contains a LOB data type in the leaf level, the statement also compacts all pages that contain the large object data. Note that specifying the WITH (LOB_COMPACTION) option is not required because the default value is ON.

USE AdventureWorks2012;
GO
ALTER INDEX PK_ProductPhoto_ProductPhotoID ON Production.ProductPhoto
REORGANIZE ;
GO

D. Setting options on an index

The following example sets several options on the index AK_SalesOrderHeader_SalesOrderNumber.

USE AdventureWorks2012;
GO
ALTER INDEX AK_SalesOrderHeader_SalesOrderNumber ON
    Sales.SalesOrderHeader
SET (
    STATISTICS_NORECOMPUTE = ON,
    IGNORE_DUP_KEY = ON,
    ALLOW_PAGE_LOCKS = ON
    ) ;
GO

E. Disabling an index

The following example disables a nonclustered index on the Employee table.

USE AdventureWorks2012;
GO
ALTER INDEX IX_Employee_OrganizationNode ON HumanResources.Employee
DISABLE ;
GO

F. Disabling constraints

The following example disables a PRIMARY KEY constraint by disabling the PRIMARY KEY index. The FOREIGN KEY constraint on the underlying table is automatically disabled and warning message is displayed.

USE AdventureWorks2012;
GO
ALTER INDEX PK_Department_DepartmentID ON HumanResources.Department
DISABLE ;
GO

The result set returns this warning message.

Warning: Foreign key 'FK_EmployeeDepartmentHistory_Department_DepartmentID'

on table 'EmployeeDepartmentHistory' referencing table 'Department'

was disabled as a result of disabling the index 'PK_Department_DepartmentID'.

G. Enabling constraints

The following example enables the PRIMARY KEY and FOREIGN KEY constraints that were disabled in Example F.

The PRIMARY KEY constraint is enabled by rebuilding the PRIMARY KEY index.

USE AdventureWorks2012;
GO
ALTER INDEX PK_Department_DepartmentID ON HumanResources.Department
REBUILD ;
GO

The FOREIGN KEY constraint is then enabled.

ALTER TABLE HumanResources.EmployeeDepartmentHistory
CHECK CONSTRAINT FK_EmployeeDepartmentHistory_Department_DepartmentID;
GO

H. Rebuilding a partitioned index

The following example rebuilds a single partition, partition number 5, of the partitioned index IX_TransactionHistory_TransactionDate.

USE AdventureWorks;
GO
-- Verify the partitioned indexes.
SELECT *
FROM sys.dm_db_index_physical_stats (DB_ID(),OBJECT_ID(N'Production.TransactionHistory'), NULL , NULL, NULL);
GO
--Rebuild only partition 5.
ALTER INDEX IX_TransactionHistory_TransactionDate
ON Production.TransactionHistory
REBUILD Partition = 5;
GO

I. Changing the compression setting of an index

The following example rebuilds an index on a nonpartitioned table.

ALTER INDEX IX_INDEX1 
ON T1
REBUILD 
WITH ( DATA_COMPRESSION = PAGE )
GO

For additional data compression examples, see Data Compression.

See Also

Reference

CREATE INDEX (Transact-SQL)

CREATE SPATIAL INDEX (Transact-SQL)

CREATE XML INDEX (Transact-SQL)

DROP INDEX (Transact-SQL)

sys.dm_db_index_physical_stats (Transact-SQL)

EVENTDATA (Transact-SQL)

Concepts

Disable Indexes and Constraints

XML Indexes (SQL Server)

Perform Index Operations Online

Reorganize and Rebuild Indexes