Changes the max_rows_per_page, fillfactor, reservepagegap, or exp_row_size value for future space allocations of a table or an index; sets the concurrency_opt_threshold for a table. Provides the user interface for optimistic index locking, which acquires an exclusive table lock on a specified table.
sp_chgattribute objname, {"max_rows_per_page" | "fillfactor" |
"reservepagegap" | "exp_row_size"
concurrency_opt_threshold | “optimistic_index_lock”}, value, optvalue
sp_chgattribute "table_name", {"identity_gap", set_number |
“dealloc_first_txtpg”,1}
is the name of the table or index for which you want to change attributes.
specifies the row size. Use this option for tables with variable-length columns.
specifies how full Adaptive Server will make each page when it is re-creating an index or copying table pages as a result of a reorg rebuild command or an alter table command to change the locking scheme. The fillfactor percentage is relevant only at the time the index is rebuilt. Valid values are 0–100.
specifies the ratio of filled pages to empty pages that are to be left during extent I/O allocation operations. For each specified num_pages, an empty page is left for future expansion of the table. Valid values are 0–255. The default value is 0.
reserves a specified amount of space for the rows in data-only locked tables. Use this option to reduce the number of rows being forwarded, which can be expensive during updates. Valid values are 0, 1, and any value between the minimum and maximum row length for the table. 0 means a server-wide setting is applied, and 1 means to fully pack the rows on the data pages.
specifies the table size, in pages, at which access to a data-only-locked table should begin optimizing for reducing I/O, rather than for concurrency. If the table is smaller than the number of pages specified by concurrency_opt_threshold, the query is optimized for concurrency by always using available indexes; if the table is larger than the number of pages specified by concurrency_opt_threshold, the query is optimized for I/O instead. Valid values are -1 to 32767. Setting the value to 0 disables concurrency optimization. Use -1 to enforce concurrency optimization for tables larger than 32767 pages. The default is 15 pages.
is the name of the option that sets an exclusive table lock on the table you specify.
set 1 to turn optimistic index locking on, or 0 to turn it off.
is the new value. Valid values and default values depend on which parameter is specified.
is the name of the table for which you want to change the identity gap. Used also to deallocate the text and image pages on a table to free up space.
indicates that you want to change the identity gap.
is the new size of the identity gap.
Sets the max_rows_per_page to 1 for the authors table for all future space allocations:
sp_chgattribute authors, "max_rows_per_page", 1
Sets the max_rows_per_page to 4 for the titleidind index for all future space allocations:
sp_chgattribute "titles.titleidind", "max_rows_per_page", 4
Specifies a fillfactor of 90 percent for pages in title_ix:
sp_chgattribute "titles.title_ix", "fillfactor", 90
Sets the exp_row_size to 120 for the authors table for all future space allocations:
sp_chgattribute "authors", "exp_row_size", 120
Sets the reservepagegap to 16 for the titleidind index for all future space allocations:
sp_chgattribute "titles.titleidind", "reservepagegap", 16
Turns off concurrency optimization for the titles table:
sp_chgattribute "titles", "concurrency_opt_threshold", 0
Sets the identity gap for mytable to 20:
sp_chgattribute "mytable", "identity_gap", 20
Changes mytable to use the identity burning set factor setting instead of the identity_gap setting:
sp_chgattribute "mytable", "identity_gap", 0
Sets the value of sp_chgattribute to 1, turning the optimistic index locking feature on.
sp_chgattribute "mytable", "optimistic_index_lock", 1
Sets the value of sp_chgattribute to 0, turning the optimistic index locking feature off.
sp_chgattribute "mytable", "optimistic_index_lock", 0
Switches the deallocation for text and image space on.
sp_chgattribute "mytable", "deallocate_first_txtpg", 1
To switch the feature off:
sp_chgattribute "mytable", "deallocate_first_txtpg", 0
sp_chgattribute changes the max_rows_per_page, fillfactor, reservepagegap, or exp_row_size value for future space allocations or data modifications of the table or index. It does not affect the space allocations of existing data pages. You can change these values for an object only in the current database.
Use sp_help to see the stored spance management values for a table. Use sp_helpindex to see the stored space management values for an index.
Setting max_rows_per_page to 0 tells Adaptive Server to fill the data or index pages and not to limit the number of rows (this is the default behavior of Adaptive Server if max_rows_per_page is not set).
Low values for optvalue may cause page splits. Page splits occur when new data or index rows need to be added to a page, and there is not enough room for the new row. Usually, the data on the existing page is split fairly evenly between the newly allocated page and the existing page.
To approximate the maximum value for a nonclustered index, subtract 32 from the page size and divide the resulting number by the index key size. The following statement calculates the maximum value of max_rows_per_page for the nonclustered index titleind:
select (select @@pagesize - 32) / minlen from sysindexes where name = "titleind"
----------- 288
If you specify too high a value for optvalue, Adaptive Server returns an error message specifying the highest value allowed.
If you specify an incorrect value for max_rows_per_page, fillfactor, reservepagegap, or exp_row_size, sp_chgattribute returns an error message specifying the valid values.
For more information on max_rows_per_page, fillfactor, reservepagegap, exp_row_size, and concurrency_opt_threshold, see the Performance and Tuning Guide.
For more information about identity gaps, see the section “Managing Identity Gaps in Tables” in Chapter 7, “Creating Databases and Tables” in the Transact-SQL User’s Guide.
You cannot run this stored procedure from within a transaction.
Only a user with sa_role privileges can execute this stored procedure.
You cannot set the optimistic index locking option for tables with datapages or datarow locking schemes.
You cannot set the optimistic index locking option for tables in system databases, such as master or tempdb. You can set it only on user-defined tables.
If you do not acquire a lock on the specified table, sp_chgattribute fails.
The default property of the optimistic index locking option is off.
Only the object owner can execute sp_chgattribute.
Commands alter table, create index, create table
System procedures sp_helpindex
