[2k8] 인덱스 생성/재생성/재구성 비교

/********************************************************************************************
-- Title : [2k8] 인덱스 생성/재생성/재구성 비교
-- Reference : dbrnag.tistory.com
-- Key word : create alter index drop_existing rebuild reorganize
********************************************************************************************/

-- 파일 : sql 2000 인덱스 조각 모음 성능 관련

인덱스_조각_모음(showcontig_dbreindex_indexdefrag).txt

-- 인덱스 생성
2k> CREATE INDEX~ WITH DROP_EXISTING~
2k8> CREATE INDEX~ WITH DROP_EXISTING = ON
CREATE NONCLUSTERED INDEX IX_WorkOrder_ProductID
    ON Production.WorkOrder(ProductID)
    WITH (FILLFACTOR = 80,
        PAD_INDEX = ON,
        DROP_EXISTING = ON);
GO



-- 인덱스 재생성
2k> DBCC DBREINDEX
DBCC DBREINDEX ("HumanResources.Employee", PK_Employee_EmployeeID,80);
GO

2k8> ALTER INDEX~ REBUILD~
ALTER INDEX PK_Employee_EmployeeID ON HumanResources.Employee
REBUILD;
GO

ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
              STATISTICS_NORECOMPUTE = ON);
GO



-- 인덱스 재구성
2k> DBCC INDEXDEFRAG
DBCC INDEXDEFRAG (AdventureWorks, "Production.Product", PK_Product_ProductID)
GO

2k8> ALTER INDEX~
ALTER INDEX PK_ProductPhoto_ProductPhotoID ON Production.ProductPhoto
REORGANIZE ;
GO