sp_chgattribute

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.

Syntax

sp_chgattribute objname,
	{"max_rows_per_page" | "fillfactor" | "reservepagegap" | 
	"exp_row_size" | "concurrency_opt_threshold" | 
	"optimistic_index_lock" | "identity_burn_max" | "plldegree"
	"ptn_locking"}, value,  optvalue
    {"identity_gap", set_number |
	"dealloc_first_txtpg", 0 | 1 | 2}

Parameters

objname – is the name of the table or index for which you want to change attributes.
max_rows_per_page – specifies the row size. Use this for tables with variable-length columns.
fillfactor – specifies how full the SAP ASE server makes 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.
reservepagegap – 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.
exp_row_size – 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.
concurrency_opt_threshold – 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.
optimistic_index_lock – enables a performance optimization that eliminates contention on the root page of an index. If the root page must change because of index splits, an exclusive table is acquired. For this reason, optimistic_index_lock is appropriate for tables where the number of modifications is relatively small. Valid values are 1 to turn on optimistic index locking or 0 to turn off optimistic index locking which is the default.
identity_burn_max – allows you to reset the internal counter for the identity column. The value set represents the highest value already generated; the next automatically generated value is one larger than the value you specify. The value is passed as a varchar datatype in the fourth parameter.
identity_gap – indicates that you want to change the identity gap.
value – is the numeric input value for the various options you specify in the sp_chgattribute.
optvalue – is the new value. Valid values and default values depend on which parameter is specified. This parameter is only used by the identity_burn_max parameter. For other parameters, this value is NULL.
set_number – is the new size of the identity gap.
dealloc_first_txtpg – updates a text or image column to null. Sets the corresponding text pointer to null after deallocating the previously referenced text or image pages. This result in reduced space allocation for null text/images columns. Valid values are:
- 0 – default, existing value, if either the table option setting is 1, or the database option deallocate first text page is TRUE, then deallocate the first text page after NULL update; otherwise, do not deallocate the first text page.
- 1 – deallocate the first text page after NULL update (overriding the setting of the database option deallocate first text page).
- 2 – do not deallocate the first text page after NULL update (overriding the setting of the database option deallocate first text page).
Whether the first text page will be deallocated after NULL update depends on the combination of this table parameter and the database option deallocate first text page.
```
 
DB setting (deallocate first text page)   |   0   1   2
--------------------------------------------------------- 
dealloc_first_txtp - true                 |   Y   Y   N 
dealloc_first_txtp - false                |   N   N   N 
```
- Y: deallocate first text page after null update
- N: not deallocate first text page after null update
The output from sp_help indicates whether first text page will be deallocated.
plldegree – specifies the maximum number of threads the query optimizer can use.
ptn_locking – specifies whether to enable (1) or disable (0) locking at the partition level. By default, partition locking is disabled.

Examples

Example 1 – 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
```
Example 2 – 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
```
Example 3 – Specifies a fillfactor of 90 percent for pages in title_ix:
```
sp_chgattribute "titles.title_ix", "fillfactor", 90
```
Example 4 – Sets the exp_row_size to 120 for the authors table for all future space allocations:
```
sp_chgattribute "authors", "exp_row_size", 120
```
Example 5 – Sets the reservepagegap to 16 for the titleidind index for all future space allocations:
```
sp_chgattribute "titles.titleidind", "reservepagegap", 16
```
Example 6 – Turns off concurrency optimization for the titles table:
```
sp_chgattribute "titles", "concurrency_opt_threshold", 0
```
Example 7 – Sets the identity gap for mytable to 20:
```
sp_chgattribute "mytable", "identity_gap", 20
```
Example 8 – Changes mytable to use the identity burning set factor setting instead of the identity_gap setting:
```
sp_chgattribute "mytable", "identity_gap", 0
```
Example 9 – Sets the value of sp_chgattribute to 1, turning the optimistic index locking feature on.
```
sp_chgattribute "mytable", "optimistic_index_lock", 1
```
Example 10 – Sets the value of sp_chgattribute to 0, turning the optimistic index locking feature off.
```
sp_chgattribute "mytable", "optimistic_index_lock", 0
```
Example 11 – Switches the deallocation for text and image space on using dealloc_first_txtpg:
```
sp_chgattribute "mytable", "dealloc_first_txtpg", 1
```
To switch the feature off:
```
sp_chgattribute "mytable", "dealloc_first_txtpg", 0
```

Example 12 – The output from sp_help indicates whether the first text page will be deallocated:

> sp_chgattribute  mytab, "dealloc_first_txtpg", 1   
'dealloc_first_txtpg' attribute of object 'mytab' changed to 1.   
(return status = 0)   
1>   
2> sp_help mytab  
 Name Owner Object_type     Object_status            Create_date
----- ----- ----------- -------------------------- -------------------
mytab  dbo  user table  deallocate first text page  Jan 22 2013 9:45PM 
  
> sp_chgattribute  mytab, "dealloc_first_txtpg", 2   
'dealloc_first_txtpg' attribute of object 'mytab' changed to 2.   
(return status = 0)   
1>   
2> sp_help mytab  
  Name Owner Object_type     Object_status         Create_date
----- ----- ------------- -------------------- --------------------
mytab  dbo   user table   keep first text page  Jan 22 2013  9:45PM

Example 13 – Changes the identity_burn_max value for the authors table to 5:
```
sp_chgattribute "authors", "identity_burn_max", 0, "5"
```
Example 14 – Tells the query optimizer to use a maximum of four threads:
```
sp_chgattribute my_table, "plldegree", 4
```
The query optimizer may choose less than four threads if it does not find enough resources. The same mechanism can be applied to an index. For example, the following example uses an index called auth_ind exists on authors to use two threads to access it:
```
sp_chgattribute "authors.auth_ind", "plldegree", 4
```
You must run sp_chgatttribute from the current database.
Example 15 – Enables partition-level locking for the authors table:
```
sp_chgattribute authors, "ptn_locking", 1
```
To disable partition-level locking:
```
sp_chgattribute authors, "ptn_locking", 0
```

Usage

There are additional considerations when using sp_chgattribute:

You cannot change attributes for virtually hashed tables. For example, if you attempt to change the attributes for table order_line (a virtually-hashed table) like this:
```
sp_chgattribute 'order_line', 'exp_row_size', 1
```
The SAP ASE server issues an error message similar to:
```
sp_chgattribute is not allowed for order_line, as it is a virtually hashed table.
```
(Cluster Edition only) You cannot use sp_chgattribute to change the value of identity_gap at runtime.
sp_chgattribute changes the max_rows_per_page, fillfactor, reservepagegap, exp_row_size, or dealloc_first_txtpg 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 space 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 the SAP ASE server to fill the data or index pages and not to limit the number of rows—this is the default behavior of the SAP ASE server if you do not set max_rows_per_page.
Both the identity_burn_max value stored in sysobjects and the current identity value are set to the new value.
If the table is:
- Not empty – the new value of identity_burn_max must be greater than or equal to the current maximum value of the identity column.
- Empty – you can set the value to any positive value in the valid range.
Low values of max_rows_per page 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 an incorrect value for max_rows_per_page, fillfactor, reservepagegap, or exp_row_size, sp_chgattribute returns an error message specifying the valid values.
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.
text and image pages are allocated space even when you perform a NULL update. You can use dealloc_first_txtpg to remove these empty text pages from the table.

A new update to the column results in reallocation of a text or image page.

Permissions

The permission checks for sp_chgattribute differ based on your granular permissions settings.

Setting	Description
Enabled	With granular permissions enabled, you must be the object owner.
Disabled	With granular permissions disabled, you must be the object owner.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

Information	Values
Event	38
Audit option	exec_procedure
Command or access audited	Execution of a procedure
Information in `extrainfo`	Roles – Current active roles Keywords or options – NULL Previous value – NULL Current value – NULL Other information – All input parameters Proxy information – Original login name, if set proxy in effect