sp_indexoption (T-SQL)

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 a default of NULL, which lists all available table options and the corresponding default values assigned to new tables (0 = FALSE or off, 1 = TRUE or on). 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.

[@OptionValue =] 'value'

Specifies whether the option_name setting is TRUE (on) or FALSE (off). value is varchar(12), with a default of NULL, which lists the current settings for all matching tables.

Return Code Values

0 (success) or greater than 0 (failure)

Result Sets

Column name	Data type	Description
Available Index Options	nvarchar(35)	The options that are available.
Default Value	int	The default values for the options.

Permissions

Members of the sysadmin fixed server role or the db_owner fixed database role can modify the AllowRowLocks and AllowPageLocks options for any user-defined indexes. Other users can modify options only for tables they own.

Any user can run sp_indexoption to generate a report listing index option values for all user-defined indexes.

Examples

This example reports the available index options for the PK_Customers index on the Customers table in the Northwind database.

USE Northwind

EXEC sp_indexoption @IndexNamePattern = 'Customers.PK_Customers'