SQL Server中的OFFLINE和ONLINE索引重建有什么区别？ [英] What is the difference between OFFLINE and ONLINE index rebuild in SQL Server?

问题描述

重建索引时，有一个ONLINE = OFF和ONLINE = ON的选项。我知道当ONLINE模式打开时，它会复制索引，切换新查询以使用它，然后重建原始索引，使用版本控制跟踪更改（如果我错了，请纠正我）。

When rebuilding an index, there is an option for ONLINE=OFF and ONLINE=ON. I know that when ONLINE mode is on, it makes a copy of the index, switches new queries to utilizing it, and then rebuilds the original index, tracking changes using versioning to both (correct me if I am wrong).

但是在离线模式下SQL做了什么？

But what does SQL do in OFFLINE mode?

推荐答案

在线模式下新的建立索引，同时可以读取和写入旧索引。旧索引的任何更新也将应用于新索引。反物质列用于跟踪更新和重建之间可能存在的冲突（即删除尚未复制的行）。请参见在线索引操作。当进程完成时，表会被锁定一段时间，新索引将替换旧索引。如果索引包含LOB列，则SQL Server 2005/2008 / R2不支持ONLINE操作。

In ONLINE mode the new index is built while the old index is accessible to reads and writes. any update on the old index will also get applied to the new index. An antimatter column is used to track possible conflicts between the updates and the rebuild (ie. delete of a row which was not yet copied). See Online Index Operations. When the process is completed the table is locked for a brief period and the new index replaces the old index. If the index contains LOB columns, ONLINE operations are not supported in SQL Server 2005/2008/R2.

在OFFLINE模式下，表会被预先锁定以进行任何读取或写入，然后新索引从旧索引构建，同时保持对表的锁定。在重建索引时，不允许对表执行读取或写入操作。只有在操作完成后才会释放对表的锁定并再次允许读取和写入。

In OFFLINE mode the table is locked upfront for any read or write, and then the new index gets built from the old index, while holding a lock on the table. No read or write operation is permitted on the table while the index is being rebuilt. Only when the operation is done is the lock on the table released and reads and writes are allowed again.

请注意，在SQL Server 2012中，对LOB的限制已被取消，请参阅包含LOB列的索引的在线索引操作。

Note that in SQL Server 2012 the restriction on LOBs was lifted, see Online Index Operations for indexes containing LOB columns.

这篇关于SQL Server中的OFFLINE和ONLINE索引重建有什么区别？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！