Sqlserver Rebuild or Orgnize index

這兩者差別

Index Rebuild : This process drops the existing Index and Recreates the index.
語法:

USE AdventureWorks;
GO
ALTER INDEX ALL ON Production.Product REBUILD
GO

Index Reorganize : This process physically reorganizes the leaf nodes of the index.

語法:

USE AdventureWorks;
GO
ALTER INDEX ALL ON Production.Product REORGANIZE
GO

破碎查詢

USE AdventureWorks2012;

GO

-- Find the average fragmentation percentage of all indexes

-- in the HumanResources.Employee table.

SELECT a.index_id, name, avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats (DB_ID(N'AdventureWorks2012'), OBJECT_ID(N'HumanResources.Employee'), NULL, NULL, NULL) AS a JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;

GO

微軟建議

avg_fragmentation_in_percent 值avg_fragmentation_in_percent value	修正的陳述式Corrective statement
> 5% 且 < = 30%	ALTER INDEX REORGANIZEALTER INDEX REORGANIZE
> 30%>	ALTER INDEX REBUILD WITH (ONLINE = ON)

參考:

https://docs.microsoft.com/zh-tw/sql/relational-databases/indexes/reorganize-and-rebuild-indexes

https://blog.sqlauthority.com/2007/12/22/sql-server-difference-between-index-rebuild-and-index-reorganize-explained-with-t-sql-script/