The difference between Index Reorganize and Rebuild

There is a difference between the Reorganize and Rebuild of index.

1, semantic differences

Rebuild is re-created, releasing the space previously occupied by index, and re-applying for space to create index. Rebuilding an index means that a whole new set of pages are allocated for it.

Reorganize is re-organized for use with index leaf level pages. Reorganizing an index compacts the Leaf-level pages back to their original specified fillfactor ant then rearrages the Pag Es at the leaf level pages to correct the logical fragmentation, using the same pages, the index originally OCCUPIED.N o new pages are allocated.

2, Different Syntax

AlterINDEXIndex_nameOn<object>{REBUILD[[PARTITION = All][With (<rebuild_index_option> [,... N]) ]|[PARTITION = Partition_number [With (<single_partition_rebuild_index_option> [,... N])] ] ]|REORGANIZE[PARTITION = Partition_number][With (Lob_compaction = {on | OFF})]}[;]<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 | Span style= "color: #0000ff;" >off | MAXDOP = max_degree_of_parallelism | Data_compression = {NONE | ROW | PAGE}}}                

REBUILD [With (<rebuild_index_option> [ ,..... N] ) ]               

Specifies the index would be rebuilt using the same columns, index type, uniqueness attribute, and sort order. This clause was equivalent to DBCC dbreindex. REBUILD enables a disabled index. Rebuilding a clustered index does not rebuild associated nonclustered indexes unless the keyword are specified.

If index Options is not specified, the existing index option values stored in sys.indexes is applied. For any index option whose value was not stored in sys.indexes, the default indicated in the argument definition O F the option applies.

In the Sys.indexes view, a total of 5 index option are stored, ignore_dup_key,fill_factor,is_padded,allow_row_locks,allow_page_locks, respectively, The default value for the other 5 index option is "negative", respectively

SORT_IN_TEMPDB:            defaultoffstatistics_norecompute:    defaultoff  drop_existing:              defaultOFFONLINE:                      default  OFFdata_compression:          Default  nonemaxdop:                     0

For more information, see Create INDEX (Transact-SQL)

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

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

REORGANIZE

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

3, sys.indexes storage of index option

SelectI.object_id, I.name asIndexname,i.index_id,i.type,i.type_desc, i.data_space_id,i.is_disabled,--Unique PropertyI.is_unique,--ConstraintI.is_primary_key, I.is_unique_constraint,--Filter IndexI.has_filter, I.filter_definition,--Index OptionsI.ignore_dup_key, I.fill_factor, i.is_padded, I.allow_row_locks, I.allow_page_locks fromSys.indexes I

4,alter Index Remarks

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

When an option isn't explicitly specified, the current setting is applied. For example, if a FILLFACTOR setting isn't specified in the REBUILD clause, the fill factor value stored in the system CA Talog'll 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 is not stored in the system catalog. Unless specified in the "index statement", the default value for the option is used.

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

An index cannot being reorganized or rebuilt if the filegroup in which it is located are offline or set to Read-only. When the keyword-specified and one or more indexes be 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 is compacting the pages based on the specified or existing fill factor set Ting, and reorders the index rows in contiguous pages. When all was specified, all indexes on the table was dropped and rebuilt in a single transaction. FOREIGN KEY constraints does not have a to is dropped in advance. When indexes with + extents or more is rebuilt, the Database Engine defers the actual page deallocations, and their Ociated locks, until after the transaction commits.

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

In SQL Server (statistics) created by scanning all the rows in the table if a partitioned index is create D 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 STATIST ICS with the FULLSCAN clause.

In earlier versions of SQL Server, could sometimes rebuild a nonclustered index to correct inconsistencies caused by H Ardware failures. In SQL Server (later), you may still is able to repair such inconsistencies between the index and the clustered IND Ex by rebuilding a nonclustered index offline. However, cannot repair nonclustered index inconsistencies by rebuilding the index online, because the online rebuild m Echanism would use the existing nonclustered index as the basis for the rebuild and thus persist the inconsistency. Rebuilding the index offline, by contrast, would force a scan of the clustered index (or heap) and so remove the Inconsiste Ncy. As with earlier versions, we recommend recovering from inconsistencies to restoring the affected data from a backup; However, 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 was specified, relational indexes, both clustered and nonclustered, and XML indexes on the table was reorganized. Some restrictions apply when specifying all, see the definition for all under the Arguments section.

Reference Documentation:

https://msdn.microsoft.com/en-us/library/ms188783 (v=sql.110). aspx

https://msdn.microsoft.com/en-us/library/ms189858 (v=sql.110). aspx

https://msdn.microsoft.com/en-us/library/ms188388 (v=sql.110). aspx

The difference between Index Reorganize and Rebuild