No stored fillfactor values

With no fillfactor values stored in sysindexes, the fillfactor specified in commands “create index”are applied as shown in Table 14-1.

create clustered index title_id_ix
on titles (title_id) 
with fillfactor = 80
Table 14-1: fillfactor values applied with no table-level saved value

Command

Allpages-locked table

Data-only-locked table

create clustered index

Data pages: 80

Data pages: fully packed Leaf pages: 80

Nonclustered index rebuilds

Leaf pages: 80

Leaf pages: 80

The nonclustered indexes use the fillfactor specified in the create clustered index command.

If no fillfactor is specified in create clustered index, the nonclustered indexes always use the server-wide default; they never use a value from sysindexes.


Values used for alter table...lock and reorg rebuild

When no fillfactor values are stored, both alter table...lock and reorg rebuild apply the server-wide default value, set by the default fill factor percentage configuration parameter. The default fillfactor is applied as shown in Table 14-2.

Table 14-2: fillfactor values applied with during rebuilds

Command

Allpages-locked table

Data-only-locked table

Clustered index rebuild

Data pages: default value

Data pages: fully packed Leaf pages: default value

Nonclustered index rebuilds

Leaf pages: default

Leaf pages: default