sql server 2000中建立索引時的填滿因數fillfactor

PAD_INDEX

Specifies the space to leave open on
each page (node) in the intermediate levels of the index. The PAD_INDEX
option is useful only when FILLFACTOR is specified, because PAD_INDEX
uses the percentage specified by FILLFACTOR. By default, SQL Server
ensures that each index page has enough empty space to accommodate at
least one row of the maximum size the index can have, given the set of
keys on the intermediate pages. If the percentage specified for
FILLFACTOR is not large enough to accommodate one row, SQL Server
internally overrides the percentage to allow the minimum.

Note
  The number of rows on an intermediate index page is never less than two, regardless of how low the value of

FILLFACTOR.

FILLFACTOR =
fillfactor

Specifies
a percentage that indicates how full SQL Server should make the leaf
level of each index page during index creation. When an index page fills
up, SQL Server must take time to split the index page to make room for
new rows, which is quite expensive. For update-intensive tables, a
properly chosen FILLFACTOR value yields better update performance than
an improper FILLFACTOR value. The value of the original FILLFACTOR is
stored with the index in sysindexes
.

When
FILLFACTOR is specified, SQL Server rounds up the number of rows to be
placed on each page. For example, issuing CREATE CLUSTERED INDEX ...
FILLFACTOR = 33 creates a clustered index with a FILLFACTOR of 33
percent. Assume that SQL Server calculates that 5.2 rows is 33 percent
of the space on a page. SQL Server rounds so that six rows are placed on
each page.

Note
  An
explicit FILLFACTOR setting applies only when the index is first
created. SQL Server does not dynamically keep the specified percentage
of empty space in the pages.

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. You can change the default FILLFACTOR setting by executing sp_configure
.

Use
a FILLFACTOR of 100 only if no INSERT or UPDATE statements will occur,
such as with a read-only table. If FILLFACTOR is 100, SQL Server creates
indexes with leaf pages 100 percent full. An INSERT or UPDATE made
after the creation of an index with a 100 percent FILLFACTOR causes page
splits for each INSERT and possibly each UPDATE.

Smaller
FILLFACTOR values, except 0, cause SQL Server to create new indexes
with leaf pages that are not completely full. For example, a FILLFACTOR
of 10 can be a reasonable choice when creating an index on a table known
to contain a small portion of the data that it will eventually hold.
Smaller FILLFACTOR values also cause each index to take more storage
space.

The following table illustrates how the pages of an index are filled up if FILLFACTOR is specified.

FILLFACTOR	Intermediate page	Leaf page
0 percent	One free entry	100 percent full
1 - 99 percent	One free entry	<= FILLFACTOR percent full
100 percent	One free entry	100 percent full

 

One free entry is the space on the page that can accommodate another index entry.

Important
  Creating
a clustered index with a FILLFACTOR affects the amount of storage space
the data occupies because SQL Server redistributes the data when it
creates the clustered index.

 

 

樣本：

This example uses the FILLFACTOR clause set to 100. A FILLFACTOR of
100 fills every page completely and is useful only when you know that
index values in the table will never change.

SET NOCOUNT OFF

USE pubs

IF EXISTS (SELECT name FROM sysindexes 

      WHERE name = 'zip_ind')

   DROP INDEX authors.zip_ind

GO

USE pubs

GO

CREATE NONCLUSTERED INDEX zip_ind

   ON authors (zip)

   WITH FILLFACTOR = 100


======================================================

create table 時：

[WITH FILLFACTOR =
fillfactor
]

Specifies how full SQL Server should make each index page used to store the index data. User-specified fillfactor
values can be from 1 through 100, with a default of 0. A lower fill
factor creates the index with more space available for new index entries
without having to allocate new space.