Friday, November 20, 2009

Alter Index

REORGANIZE

Specifies the index leaf level will be reorganized. This clause is equivalent to DBCC INDEXDEFRAG. 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. For more information, see Creating Indexes with Included Columns.

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 affect on a heap.

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

No comments:

Post a Comment