Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 9 down vote accepted

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"

share|improve this answer
    
my recent search showed that its a "MYTH-Fill factor of 0 and 100 are never the same " ... –  Subha Jul 10 '13 at 11:31
    
@Subha can you share a link? –  Aaron Bertrand Jul 10 '13 at 12:27
    
@Subha: Do you mean this? sqlskills.com/blogs/paul/… –  gbn Jul 10 '13 at 13:00
    
@AaronBertrand : sqlskills.com/blogs/paul/… –  Subha Jul 11 '13 at 6:09
    
@gbn : yup it is... –  Subha Jul 11 '13 at 6:09

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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