This command stores a fillfactor value of 50 for the table:
sp_chgattribute titles, "fillfactor", 50
If you set the stored table-level value for fillfactor to 50, this create clustered index command applies the fillfactor values shown in Table 3-3.
create clustered index title_id_ix on titles (title_id) with fillfactor = 80
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 you run create clustered index, any
table-level fillfactor value stored in sysindexes is
reset to 0.
You must first issue sp_chgattribute to specify that data-only-locked data pages are filled during a create clustered index or reorg command.
Stored values for fillfactor are used when an alter table...lock command copies tables and rebuilds 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 you change a DOL table to use allpages locking, the fillfactor stored for the table is used for the data pages. Adaptive Server ignores the fillfactor stored for the clustered index.
Table 3-4 shows the fillfactor values that are set on data and index pages using an alter table...lock command, executed after the sp_chgattribute commands above have been run.
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.
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 3-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.
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 |
