Fill-factor 100 for a CLUSTERED UNIQUE IDENTITY field

hi,
i have a table with IDENTITY field.
i set a CLUSTERED UNIQUE index on this field.
i wonder whether is it right to set this index's Fill-
factor to 100?
i though that if the values of this field are always auto-
incremented so there would never be a need to split places
between records, so there is no need to save space for new
records(or updated records) that might need to be placed
between any other prior inserted records.
is it true?
is it the optimal choise for this case?
thanks,
edo.Not quite true.
You're on the right track as far as inserts are concerned, but updates are
another matter.
Because clustered indexes contain ALL columns, any updates that increase the
width of the row might cause a page split.
Therefore, whether 100% is optimal or not depends on whether there are any
updates to the table.
HTH
Regards,
Greg Linwood
SQL Server MVP
"edo" <anonymous@.discussions.microsoft.com> wrote in message
news:1c2db01c4528a$671b1890$a601280a@.phx.gbl...
> hi,
> i have a table with IDENTITY field.
> i set a CLUSTERED UNIQUE index on this field.
> i wonder whether is it right to set this index's Fill-
> factor to 100?
> i though that if the values of this field are always auto-
> incremented so there would never be a need to split places
> between records, so there is no need to save space for new
> records(or updated records) that might need to be placed
> between any other prior inserted records.
> is it true?
> is it the optimal choise for this case?
> thanks,
> edo.|||hi,
thanks for replying me.
increasing (decreasing too?) the WIDTH of a row because of
an update is a new information for me, and i would also
say it is quite shocking for me.
i allways thought table structure is a fixed-width, so the
space for all of the fields is allocated in the same time
and PLACE in advance for each inserted record.
or maybe do you mean that the index may be compuond by
varchar fields so changing the values may change the
lenght of the compound index-value, for example an index-
value coumpound by 2 fields may be changed from "X"+"A"
to "X"+"ABCDEF"
(so if, for example, the table contains integer fields
only, is Fill-factor 100 still be the optimal option even
when updates are concerned)
i guess it's a long story to explain why a clustered index
is actually needs to contain ALL fileds ...?
i probablly missing something (or a lot of things)...
thanks again.
edo.
>--Original Message--
>Not quite true.
>You're on the right track as far as inserts are
concerned, but updates are
>another matter.
>Because clustered indexes contain ALL columns, any
updates that increase the
>width of the row might cause a page split.
>Therefore, whether 100% is optimal or not depends on
whether there are any
>updates to the table.
>HTH
>Regards,
>Greg Linwood
>SQL Server MVP
>"edo" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1c2db01c4528a$671b1890$a601280a@.phx.gbl...
>> hi,
>> i have a table with IDENTITY field.
>> i set a CLUSTERED UNIQUE index on this field.
>> i wonder whether is it right to set this index's Fill-
>> factor to 100?
>> i though that if the values of this field are always
auto-
>> incremented so there would never be a need to split
places
>> between records, so there is no need to save space for
new
>> records(or updated records) that might need to be placed
>> between any other prior inserted records.
>> is it true?
>> is it the optimal choise for this case?
>> thanks,
>> edo.
>
>.
>|||Hi Edo,
On Mon, 14 Jun 2004 21:40:34 -0700, edo wrote:
>hi,
>thanks for replying me.
>increasing (decreasing too?) the WIDTH of a row because of
>an update is a new information for me, and i would also
>say it is quite shocking for me.
>i allways thought table structure is a fixed-width, so the
>space for all of the fields is allocated in the same time
>and PLACE in advance for each inserted record.
This is only true if the row contains no varying length columns. Each
table that holds at least one varchar, nvarchar or varbinary column has
rows with varying length.
(snip)
>i guess it's a long story to explain why a clustered index
>is actually needs to contain ALL fileds ...?
Not at all. The clustered index determines the order in which rows are
stored in the data file. Suppose you have a clustered index on an integer
column, there are rows with values 1 and 3 for that column and you then
insert a row with value 2. In that case, the database will store the
entire row between the rows vor key value 1 and 3, not only the key value.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo's answered most of your questions already here but I'll just answer
that qn you put about whether 100% fillfactor is optimal if all columns are
fixed width such as integer. I'd say that that answer to that is yes - if
the columns are fixed width, then the underlying storage requirements will
never grow for a given row, so there should not be any requirement to split
storage pages. Even if there is some obscure cause of page splits in this
scenario, I'd suggest that would be rare & therefore the 100% fillfactor
would still be optimal.
Regards,
Greg Linwood
SQL Server MVP
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:aeatc0poos36e86c2tcthsforql0rbs5lk@.4ax.com...
> Hi Edo,
> On Mon, 14 Jun 2004 21:40:34 -0700, edo wrote:
> >hi,
> >
> >thanks for replying me.
> >
> >increasing (decreasing too?) the WIDTH of a row because of
> >an update is a new information for me, and i would also
> >say it is quite shocking for me.
> >
> >i allways thought table structure is a fixed-width, so the
> >space for all of the fields is allocated in the same time
> >and PLACE in advance for each inserted record.
> This is only true if the row contains no varying length columns. Each
> table that holds at least one varchar, nvarchar or varbinary column has
> rows with varying length.
>
> (snip)
> >i guess it's a long story to explain why a clustered index
> >is actually needs to contain ALL fileds ...?
> Not at all. The clustered index determines the order in which rows are
> stored in the data file. Suppose you have a clustered index on an integer
> column, there are rows with values 1 and 3 for that column and you then
> insert a row with value 2. In that case, the database will store the
> entire row between the rows vor key value 1 and 3, not only the key value.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||When you create a clustered index on a table, the actual data rows move into
the leaf level of the clustered index ( and are therefore stored in the
order specified by the index key.)
When there are variable length columns in the table, updating may increase
the length of the row. When a row length increases on a page which is
completely full, it will bump one or more of the rows following it off of
the current page, and onto the next page. This causes extra work...
Completely full pages are wonderful for readers, because the number of IOs
which has to be done decreases, and the amount of memory required to keep
the data is lower as well. But If you ever insert new rows into the middle
of the table, or update rows so that they grow in lenght, page splitting can
occur... If you wish to reduce the speed of some reads, while improving the
insert/update situation, you simply reduce the fillfactor somewhat...
So by setting the fill factor you are stating performance preferences (
readers vs updaters) on the table.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"edo" <anonymous@.discussions.microsoft.com> wrote in message
news:1c2db01c4528a$671b1890$a601280a@.phx.gbl...
> hi,
> i have a table with IDENTITY field.
> i set a CLUSTERED UNIQUE index on this field.
> i wonder whether is it right to set this index's Fill-
> factor to 100?
> i though that if the values of this field are always auto-
> incremented so there would never be a need to split places
> between records, so there is no need to save space for new
> records(or updated records) that might need to be placed
> between any other prior inserted records.
> is it true?
> is it the optimal choise for this case?
> thanks,
> edo.