sp_indexoption
Sets option values for user-defined indexes.
Note Microsoft® SQL Server™ automatically makes choices of page-, row-, or table-level locking. It is not necessary to set these options manually. sp_indexoption is provided for expert users who know with certainty that a particular type of lock is always appropriate.
Syntax
sp_indexoption [ @IndexNamePattern = ] 'index_name'
, [ @OptionName = ] 'option_name'
, [ @OptionValue = ] 'value'
Arguments
[@IndexNamePattern =] 'index_name'
Is the qualified or nonqualified name of a user-defined database table or index. Quotation marks are not necessary if a single index or table name is specified. Even if a fully qualified table name, including a database name, is provided, the database name must be the name of the current database. If a table name is given with no index, the specified option value is set for all indexes on that table. index_pattern is nvarchar(1035), with no default.
[@OptionName =] 'option_name'
Is an index option name. option_name is varchar(35), with no default. option_name can have these values.
| Value | Description |
|---|---|
| AllowRowLocks | When FALSE, row locks are not used. Access to the specified indexes is obtained using page- and table-level locks. |
| AllowPageLocks | When FALSE, page locks are not used. Access to the specified indexes is obtained using row- and table-level locks. |
| DisAllowRowLocks | When TRUE, row locks are not used. Access to the specified indexes is obtained using page- and table-level locks. |
| DisAllowPageLocks | When TRUE, page locks are not used. Access to the specified indexes is obtained using row- and table-level locks. |
[@OptionValue =] 'value'
Specifies whether the option_name setting is enabled (TRUE, on, or 1) or disabled (FALSE, off, or 0). value is varchar(12), with no default.
Return Code Values
0 (success) or greater than 0 (failure)
Remarks
sp_indexoption can be used only to set option values for user-defined indexes. To display index properties, use INDEXPROPERTY.
Permissions
Members of the sysadmin fixed server role, the db_owner and db_ddladmin fixed database roles, and the table owner can modify the AllowRowLocks/DisAllowRowLocks and AllowPageLocks/DisAllowPageLocks options for any user-defined indexes.
Other users can modify options only for tables they own.
Examples
This example disallows page locks on the City index on the Customers table.
USE Northwind
GO
EXEC sp_indexoption 'Customers.City',
'disallowpagelocks',
TRUE