Table-level or clustered index fillfactor value stored

This command stores a fillfactor value of 50 for the table:

sp_chgattribute titles, "fillfactor", 50

With 50 as the stored table-level value for fillfactor, the following create clustered index command applies the fillfactor values shown in Table 14-3.

create clustered index title_id_ix
on titles (title_id) 
with fillfactor = 80

**Table 14-3: Using stored fillfactor values for clustered indexes**
Command	Allpages-Locked Table	Data-Only-Locked Table
create clustered index	Data pages: 80	Data pages: 50 Leaf pages: 80
Nonclustered index rebuilds	Leaf pages: 80	Leaf pages: 80

When a create clustered index command is run, any table-level fillfactor value stored in sysindexes is reset to 0.

To affect the filling of data-only-locked data pages during a create clustered index or reorg command, you must first issue sp_chgattribute.

Effects of alter table...lock when values are stored

Stored values for fillfactor are used when an alter table...lock command copies tables and rebuilds indexes.

Tables with clustered indexes

In an allpages-locked table, the table and the clustered index share the sysindexes row, so only one value for fillfactor can be stored and used for the table and clustered index. You can set the fillfactor value for the data pages by providing either the table name or the clustered index name. This command saves the value 50:

sp_chgattribute titles, "fillfactor", 50

This command saves the value 80, overwriting the value of 50 set by the previous command:

sp_chgattribute "titles.clust_ix", "fillfactor", 80

If you alter the titles table to use data-only locking after issuing the sp_chgattribute commands above, the stored value fillfactor of 80 is used for both the data pages and the leaf pages of the clustered index.

In a data-only-locked table, information about the clustered index is stored in a separate row in sysindexes. The fillfactor value you specify for the table applies to the data pages and the fillfactor value you specify for the clustered index applies to the leaf level of the clustered index.

When a data-only-locked table is altered to use allpages locking, the fillfactor stored for the table is used for the data pages. The fillfactor stored for the clustered index is ignored.

Table 14-4 shows the fillfactors used on data and index pages by an alter table...lock command, executed after the sp_chgattribute commands above have been run.

**Table 14-4: Effects of stored fillfactor values during alter table**
alter table...lock	No clustered index	Clustered index
From allpages locking to data-only locking	Data pages: 80	Data pages: 80 Leaf pages: 80
From data-only locking to allpages locking	Data pages: 80	Data pages: 80

alter table...lock sets all stored fillfactor values for a table to 0.

fillfactor values stored for nonclustered indexes

Each nonclustered index is represented by a separate sysindexes row. These commands store different values for two nonclustered indexes:

sp_chgattribute "titles.ncl_ix", "fillfactor", 90

sp_chgattribute "titles.pubid_ix", "fillfactor", 75

Table 14-5 shows the effects of a reorg rebuild command on a data-only-locked table when the sp_chgattribute commands above are used to store fillfactor values.

**Table 14-5: Effect of stored fillfactor values during reorg rebuild**
reorg rebuild	No clustered index	Clustered index	Nonclustered indexes
Data-only-locked table	Data pages: 80	Data pages: 50 Leaf pages: 80	ncl_ix leaf pages: 90 pubid_ix leaf pages: 75