SQL Interview Questions-What is Fill Factor and its value when the

What is Fill Factor and its value when the
index is created.




Ans.An option used when creating an index to
reserve free space on each page of the index.
FILLFACTOR accommodates future expansion of
table data and reduces the potential for page
splits. FILLFACTOR is a value from 1 through
100 that specifies the percentage of the index
page to be left empty.Default is 0.



A fill factor value of 0 does not mean that
pages are 0 percent full. It is treated s
imilarly to a fill factor value of 100 in that
SQL Server creates clustered indexes
with full data pages and nonclustered indexes
with full leaf pages.If you set fill factor to
100, SQL Server creates both clustered and
nonclustered indexes with each page 100 percent
full. Setting fill factor to 100 is suitable
only for read-only tables, to which additional
data is never added.