Knowledge Sharing: What is the Difference Between Rebuild and Reorganize indexes ; Interview Question

Here I have tried to include all those answers available at web in different posts.

A big thanks to all those who did lots of test run on huge data to just understand difference between re index and reorganize. Before writing I have refer blogs of Paul , Tim , Sunny and many other sql server geeks. I also want to include all those whom I have interviewed and they come with variety of answers.

Syntax :
Rebuild : ALTER INDEX ALL ON table_name REBUILD
Reorganize : ALTER INDEX ALL ON table_name REORGANIZE

S.no	REBUILD	REORGANIZE
1	This process drops the existing Index and Recreates the index	This process doesn’t drop indexes; It physically reorganizes the leaf nodes of the index.
2	Syntax: ALTER INDEX ALL ON table_name REBUILD	Syntax: ALTER INDEX ALL ON table_name REORGANIZE
3	Index should be rebuild when index fragmentation is great than 30%	Index should be reorganized when index fragmentation is between 10% to 30%
4	Index rebuilding process uses more CPU and it locks the database resources	Index reorganize process uses less CPU and it doesn’t the database resources
5	Rebuilding an index can be executed online or offline	Reorganizing an index is always executed online
6	A nice thing about rebuilding an index is that you can change the setting for index properties like the fill factor and index padding	Index options cannot be specified when reorganizing an index
7	Requires ALTER permission on the table or view or User must be a member of the sysadmin fixed server role or the db_ddladmin and db_owner fixed database roles	Requires ALTER permission on the table or view or User must be a member of the sysadmin fixed server role or the db_ddladmin and db_owner fixed database roles
8	REBUILD locks the table for the whole operation period except in Enterprise Edition with ONLINE = ON	REORGANIZE doesn't lock the table.
9	It is changing the whole B-tree, recreating the index	It is a process of cleaning, organizing, and defragmenting of only "leaf level" of the B-tree
10	REBUILD will automatically rebuild all index-column statistics	Statistics are NOT updated with a reorganization
11	This reclaims disk space by compacting the pages in new index	It works in two phases – compaction and defrag
12	Rebuild does require extra space as same the size of index	Reorganize essentially just swaps one page with another and thus does not require free space
13	Rebuild can fix extent fragmentation as well	Reorganize can remove almost all of the logical fragmentation but it cannot necessarily fix extent fragmentation
14	Index (clustered or non-clustered) cannot be built online if it contains LOB data (text, ntext, varchar(max), nvarchar(max), varbinary(max), image and xml data type columns).	Its always online operation.
15	If an index has multiple partitions, then you cannot rebuild a single partition online	You can reorganize a single index partition online
16	After rebuild statistics on non-indexed columns doesn’t get re-computed	Statistics are NOT updated with a reorganization
17	Rebuild is an atomic operation	Reorganize can be stopped while it is occurring and you won't lose the work it has already completed
18	Rebuild indexes always generates similar amount of t-log for same size index	For large, heavily fragmented indexes the reorganization operation will generate more t-log as compared to less fragmented index
19	REBUILD requires enough free space in the database to accommodate the new index	REORGANIZE only requires 8KB of additional space in the database
20	REBUILD can use multiple CPUs so the operation runs faster	REORGANIZE is always single-threaded
21	REBUILD can use minimal-logging to reduce transaction log growth	REORGANIZE is always fully logged, but doesn’t prevent transaction log clearing.
22	An index rebuild will always rebuild the entire index, regardless of the extent of fragmentation	An index reorganize will only take care of the existing fragmentation
23	SQL 2000 use DBCC DBREINDEX	SQL 2000 use DBCC INDEXDEFRAG
24	Even If the index spans multiple data files, it will be taken care	If the index spans multiple data files, reorganizing will only reorder pages within the same file, it won’t move pages between files