9

As of my understanding, Fill factor of 80 implies that 20 percentage of each leaf-level page will be empty to enable future growth. I can't correlate how fill factor of 0 and 100 can be the same! Am I missing something?

16

It is legacy from SQL Server 2000

0 and 100 were different back then

  • 100 meant "fill all pages including all b-tree index levels"
  • 0 meant "leave some space at higher levels in the b-tree index"

Since SQL Server 2005, both mean "fill all pages including all b-tree index levels"

Quotes from BOL (My bold)

SQL Server 2000:

A fill factor value of 0 does not mean that pages are 0 percent full. It is treated similarly 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. It is different from 100 in that SQL Server leaves some space within the upper level of the index tree.

SQL Server 2005

A fill factor of 0 or 100 creates clustered indexes with full data pages and nonclustered indexes with full leaf pages, but it leaves some space within the upper level of the index tree. Fill factor values 0 and 100 are identical in all respects.

What I can't find is something to show this explicitly in "behaviour changes"

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.