Sybase数据库技术，数据库恢复专家

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.

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
GO

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.

阅读全文…