Fill factor for ever increasing clustered index

Microsoft SQL Server

If I defined the clustered index on identity column, what will be the
differences between fill factor 100% and 70%?
Alan
Fillfactoe specifies how full each page should be. 70 means 70%
full, 100 means 100% full. The only special case is 0, which means the leaf
level is full, but there is room for one or two rows per page in the upper
levels of the index tree.
In addition, I recommend 0 (same as 100) or 100 for an index on an identity column. The value is
ever increasing so there will be no page splits in such an index.
Except where rows are updated with wider values so they no longer fit on
their pages.

A very common example is "notes" type columns which are very often widened,
causing page splits even on ID based CIXs.

For these scenarios, you might still want to leave some fillfactor space
otherwise your updates might be slowed down badly

Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
Benchmark your query performance
http://www.SQLBenchmarkPro.com
Most people forget about this scenario when choosing to go with 100% fill
factors.
Yes, so this is most of the answer to the OP. I believe he was wondering on
an increasing value if there was any reason to go with 70 vs 100. The answer
has to do with what else are you going to do with the table besides INSERTs.
Yes, if for the case of INSERT only, when the last page is full, any further
INSERT will be put into the new page. So there is no difference between 10%,
20% or 70% or even 100%?

If there is DELETE, so there will be some holes in the page? Because the
column is ever increasing. The holes will never be filled unless
re-indexing? If re-indexing, then this is my original question, does the
fill factor affect it?

In my understanding is when the page is splitted, the new page will be
filled by determined by fill factor.


on
answer
INSERTs.
fill
on
space
the
1) it is my understanding that new pages will take the fill factor.

2) Deletes will leave holes in their respective pages. Those holes will be
'filled' when you reindex/defrag.

3) That is my understanding as well.
No a fill factor is ONLY adhered to during a create or rebuilding of an
index. It would be pointless to always keep a page 70% full for example as
you are inserting new rows. If a page splits approximately half of the rows
stay on the existing page and half on the new page.
... unless the page splits at the "end" (the last row according to the index). Then now pages are
moves, instead just a new page is linked into the linked list, at the end.
Well that isn't really a split then:). That's business as usual.
Tues, but it is sometimes referred to as a " page split" (even though it really isn't such). :-)
If the page is full and a new record is insert, no matter at the beginning,
middle or end, then page split is the result. Then if the fill factor is for
example, 70%, then are the two pages still 50/50 ? Or the 705 fill factor
has effect?
Fillfactor only applies when building an index. It has no affect on page
splits.
So if I rebuild the index, do 'all' pages will be filled up determined by
the fill factor?
eg. 70%.
All pages will be filled up 70 %?

factor
As far as possible, all leaf level pages will be filled 70%. The upper
levels of indexes will be filled almost full, unless you ALSO specify the
PAD_INDEX option.
I believe it is recorded as a split event. There are also other special
cases where the split will not always be 50/50. I wrote a series of articles
for SQL Magazine about this.
DUH!! You know, having a 2 1/2 year old really does occassionally affect
the adult brain. :-))
Good point, Greg. One should consider what other columns are in the index (for a cl index, of course
all column) and whether any of those column risk being "widened" by updates.