What is the difference between REBUILD WITH ONLINE and REORGANIZE index in SQL Server?

What is the difference between REBUILD ONLINE and REORGANIZE index in SQL 开发者_Python百科Server?

The leaf level data in an index can easily get fragmented depending on the nature of the inserts and where SQL Server is able to place the data on disk during inserts, updates (or remove it during deletes). It will not always be able to place a specific value in the exact physical slot where it is supposed to be, and this fragmentation can have a serious impact on seek/scan operations.

Reorganizing tries to put the leaf level of the index back in logical order within the pages that are already allocated to the index.

Rebuilding basically creates an entirely new copy of the index, and is much more effective at reducing fragmentation - but this comes at a cost, both in terms of time and disk space. You'll likely need free space in the database, anywhere from 1.2x to 1.5x the existing index size, in order to perform a rebuild. This is similar to saying CREATE INDEX ... WITH DROP_EXISTING.

Rebuilding online means the old index is still available for querying by other users while the new index is being creates. This feature is not available in all editions (Enterprise+ only).

The choice between which method to use can depend on the size of the table, the level of fragmentation, the potential benefit to reducing fragmentation, and the available space on disk (with the additional decision to use online if you are on a certain edition). Ola Hallengren and Michelle Ufford have pretty robust solutions that help make these decisions for you:

http://ola.hallengren.com/

http://sqlfool.com/2011/06/index-defrag-script-v4-1/

The one nice thing about reorganizing is that if it's taking too long you can cancel it and you won't lose any of the work it's already done. If you cancel a rebuild it will roll back everything it's done.