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

Examples

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.

See also:
  • alter table, create index, create table in Reference Manual: Commands

  • 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 the Transact-SQL User’s Guide.

Permissions

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

SettingDescription
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:

InformationValues
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

Related reference
sp_helpindex