Sunday, December 19, 2010

Fill Factor option for when Create Index or Rebuild Index

When creating a index or rebuilt, The FILL-FACTOR value determines the percentage of space on each leaf-level page to be filled with data. It is important to set the fill-factor value for each index. In practice a fill-factor value is set to 80 means that 20 percent of each level-level page will be left empty.

USE AdventureWorks2008;

GO
CREATE NONCLUSTERED INDEX IDX_WorkOrder_ProductID
ON Production.WorkOrder(ProductID)
WITH (FILLFACTOR = 80,
PAD_INDEX = ON);
GO