Easily find issues by searching: #<Issue ID>
Example: #1832
Easily find members by searching in: <username>, <first name> and <last name>.
Example: Search smith, will return results smith and adamsmith
Sybase ASE 15.0 - 46
Database Andy
ivantest
Table index_test
Created an Index with the following properties - new index storage - Fill factor is not scripted
CREATE INDEX inx_1
ON ivantest.index_test(a2)
WITH
fillfactor=5,
max_rows_per_page = 45,
reservepagegap = 45
GO
When I script index Full, the Fill Factor is missing
CREATE INDEX inx_1
ON ivantest.index_test(a2)
WITH max_rows_per_page = 45, reservepagegap = 45
GO
This is how it works:
For ASE the fill_factor has the following meaning when you create the table:
specifies how full Adaptive Server makes each page when it creates a new index on existing data. The fillfactor percentage is relevant only when the index is created. As the data changes, the pages are not maintained at any particular level of fullness.
The value you specify is not saved in sysindexes for display by sp_helpindex or for later use by the reorg command. Use sp_chgattribute to create stored fillfactor values.
The default for fillfactor is 0; this is used when you do not include with fillfactor in the create index statement (unless the value has been changed with sp_configure). When specifying a fillfactor, use a value between 1 and 100.
A fillfactor of 0 creates clustered indexes with completely full pages and nonclustered indexes with completely full leaf pages. It leaves a comfortable amount of space within the index B-tree in both the clustered and nonclustered indexes. There is seldom a reason to change the fillfactor.
If the fillfactor is set to 100, Adaptive Server creates both clustered and nonclustered indexes with each page 100 percent full. A fillfactor of 100 makes sense only for read-only tables—tables to which no data is ever added.
fillfactor values smaller than 100 (except 0, which is a special case) cause Adaptive Server to create new indexes with pages that are not completely full. A fillfactor of 10 might be a reasonable choice if you are creating an index on a table that will eventually hold a great deal more data, but small fillfactor values cause each index (or index and data) to occupy more storage space.
WARNING! Creating a clustered index with a fillfactor affects the amount of storage space your data occupies, since Adaptive Server redistributes the data as it creates the clustered index.
table sysindexes has a column named fill_factor which is what we are extracting and this column has the following meaning:
Value for the fillfactor of a table set with sp_chgattribute
The fillfactor value set by sp_chgattribute is stored in the fill_factor column in sysindexes. The fillfactor is applied when an index is re-created as a result of an alter table...lock command or a reorg rebuild command.
So the fill_factor that we pass when the index is created can not be extracted from anywhere, but the fill_factor that we do extract is what would be used to recreate the index if the fill_factor of the table was changed. I think what we are doing is correct, but if you want to discuss let me know...
works as designed. Need to use Alter indes for Fillfactor to store the values
works as designed. Need to use Alter indes for Fillfactor to store the values
Issue #3702 |
| Closed |
| Won't Fix |
| Resolved |
Completion |
| No due date |
| No fixed build |
| No time estimate |
This is how it works:
For ASE the fill_factor has the following meaning when you create the table:
specifies how full Adaptive Server makes each page when it creates a new index on existing data. The fillfactor percentage is relevant only when the index is created. As the data changes, the pages are not maintained at any particular level of fullness.
The value you specify is not saved in sysindexes for display by sp_helpindex or for later use by the reorg command. Use sp_chgattribute to create stored fillfactor values.
The default for fillfactor is 0; this is used when you do not include with fillfactor in the create index statement (unless the value has been changed with sp_configure). When specifying a fillfactor, use a value between 1 and 100.
A fillfactor of 0 creates clustered indexes with completely full pages and nonclustered indexes with completely full leaf pages. It leaves a comfortable amount of space within the index B-tree in both the clustered and nonclustered indexes. There is seldom a reason to change the fillfactor.
If the fillfactor is set to 100, Adaptive Server creates both clustered and nonclustered indexes with each page 100 percent full. A fillfactor of 100 makes sense only for read-only tables—tables to which no data is ever added.
fillfactor values smaller than 100 (except 0, which is a special case) cause Adaptive Server to create new indexes with pages that are not completely full. A fillfactor of 10 might be a reasonable choice if you are creating an index on a table that will eventually hold a great deal more data, but small fillfactor values cause each index (or index and data) to occupy more storage space.
WARNING! Creating a clustered index with a fillfactor affects the amount of storage space your data occupies, since Adaptive Server redistributes the data as it creates the clustered index.
table sysindexes has a column named fill_factor which is what we are extracting and this column has the following meaning:
Value for the fillfactor of a table set with sp_chgattribute
The fillfactor value set by sp_chgattribute is stored in the fill_factor column in sysindexes. The fillfactor is applied when an index is re-created as a result of an alter table...lock command or a reorg rebuild command.
So the fill_factor that we pass when the index is created can not be extracted from anywhere, but the fill_factor that we do extract is what would be used to recreate the index if the fill_factor of the table was changed. I think what we are doing is correct, but if you want to discuss let me know...