Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
 Posted - 2002-09-20 : 14:49:14
|
| What is the diff between FILLFACTOR = 0 (default) and FILLFACTOR = 100?Both fill the leaf pages 100% and thus cause a page split on every insert, right?Jay White{0} |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-09-20 : 15:04:31
|
The only thing I see that might differentiate the two:quote:
User-specified FILLFACTOR values can be from 1 through 100. If no value is specified, the default is 0. When FILLFACTOR is set to 0, only the leaf pages are filled.
It's a stretch, but it could imply that FILLFACTOR=100 will fill non-leaf pages as well as leaf pages. Any differences are more probably due to PAD_INDEX setting, and without a specified PAD_INDEX then they probably are identical. (based on a quick scan of Ron Soukup's Inside SQL Server 6.5; who knows if it's true with later versions)B-Tree structures and their attendant maintenance have always made my head ache so I certainly can't say I have any idea what I'm talking about  |
 |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2002-09-20 : 15:04:50
|
| Right,The only difference, if you do a DBCC DBREINDEX with a fillfactor of 0, it will keep the original fillfactor, but if you do it with 100, it will fill the leaf pages completely.-Chad |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-09-20 : 15:13:55
|
| So for something like a tally table, (create table numbers (n int not null primary key) with 1 thru 10000, where there will never be any inserts or updates, a fillfactor of 100 would use the least amount of pages .... right?Jay White{0} |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2002-09-20 : 15:20:56
|
| yep.-Chad |
|
|
|
|
|
|
|
|
FILLFACTOR