ALTER INDEX

Modify an existing index on a table or view.

Syntax
ALTER INDEX {index | ALL} ON object DISABLE [;]
ALTER INDEX {index | ALL} ON object REORGANIZE
[PARTITION = partition_number ]
[WITH ( LOB_COMPACTION = {ON | OFF} ) ] [;] ALTER INDEX {index | ALL} ON object REBUILD
[ [WITH ( rebuild_index_option [ ,...n ] ) ]
| [PARTITION = partition_number
[ WITH ( single_ptn_rebuild_index_option
[ ,...n ] )
]
]
] [;]
ALTER INDEX {index | ALL} ON object SET ( set_index_option [ ,...n ] )
Object: database.[schema].table_or_view schema.table_or_view 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 single_partition_rebuild_index_option: SORT_IN_TEMPDB = {ON | OFF } MAXDOP = max_degree_of_parallelism set_index_option: ALLOW_ROW_LOCKS = {ON | OFF} ALLOW_PAGE_LOCKS = {ON | OFF} IGNORE_DUP_KEY = {ON | OFF} STATISTICS_NORECOMPUTE = {ON | OFF} Key: ALL All indexes associated with the table or view ASC/DESC The sort direction for the index column. PARTITION Only rebuild/reorganize one partition of the index. partition_number The number of an existing index partition to rebuild/reorganize. LOB_COMPACTION Compacting large object (LOB) data can reduce the disk space used. fillfactor Percentage of each index page to fill during index creation/rebuild. 1-100, default=0 (values 0 and 100 are identical=Full) SET Change index options without a rebuild/reorg, the index must be enabled. ONLINE In enterprise edition, index operations may be performed online.

In a default (nonclustered) index, the physical order of the data is independent of the index order.

Reindexing should be scheduled during system down-time as it is likely to very severely affect performance.

The options ONLINE and IGNORE_DUP_KEY are not valid when you rebuild an XML index.

A unique clustered index must be created on a view before any nonclustered index is created.

Examples

ALTER INDEX MyIndex01 ON MySchema.MyTable
REBUILD;
GO

ALTER INDEX MyIndex02 ON MySchema.MyTable
REBUILD Partition = 5;
GO

ALTER INDEX ALL ON MySchema.MyTable
REBUILD WITH (SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);
GO -- Script to rebuild all the indexes for a database: USE SS64_db --Change this to the name of your database DECLARE @TableName varchar(255) DECLARE TableCursor CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_type = 'base table' OPEN TableCursor FETCH NEXT FROM TableCursor INTO @TableName WHILE @@FETCH_STATUS = 0 BEGIN ALTER INDEX ON schema.table REBUILD/REORGANIZE FETCH NEXT FROM TableCursor INTO @TableName END CLOSE TableCursor DEALLOCATE TableCursor

"Anything you build on a large scale or with intense passion invites chaos" ~ Francis Ford Coppola

Related commands

CREATE INDEX
CREATE PARTITION SCHEME
Data Types
DBCC SHOW_STATISTICS
DROP INDEX
DBCC DBREINDEX - use to re-index with older versions (SQL Server 2000 and earlier)
sys.indexes
sys.index_columns
sys.dm_db_index_physical_stats
sys.xml_indexes
EVENTDATA
Equivalent Oracle command: ALTER INDEX


 
Copyright © 1999-2024 SS64.com
Some rights reserved