sp_indexoption (Transact-SQL)

Syntax

sp_indexoption [ @IndexNamePattern = ] 'table_or_index_name' 
    , [ @OptionName= ] 'option_name' 
    , [ @OptionValue= ] 'value'

Arguments

• [ @IndexNamePattern=] 'table_or_index_name'
  Is the qualified or nonqualified name of a user-defined table or index. table_or_index_name is nvarchar(1035), with no default. Quotation marks are required only if a qualified index or table name is specified. 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 specified with no index, the specified option value is set for all indexes on that table and the table itself if no clustered index exists.

• [ @OptionName =] 'option_name'
  Is an index option name. option_name is varchar(35), with no default. option_name can have one of the following values.

  Value	Description
  AllowRowLocks	When TRUE, row locks are allowed when accessing the index. The Database Engine determines when row locks are used. When FALSE, row locks are not used. The default is TRUE.
  AllowPageLocks	When TRUE, page locks are allowed when accessing the index. The Database Engine determines when page locks are used. When FALSE, page locks are not used. The default is TRUE.
  DisAllowRowLocks	When TRUE, row locks are not used. When FALSE, row locks are allowed when accessing the index. The Database Engine determines when row locks are used.
  DisAllowPageLocks	When TRUE, page locks are not used. When FALSE, page locks are allowed when accessing the index. The Database Engine determines when page locks are used.

• [ @OptionValue =] 'value'
  Specifies whether the option_name setting is enabled (TRUE, ON, yes, or 1) or disabled (FALSE, OFF, no, or 0). value is varchar(12), with no default.

Return Code Values

0 (success) or greater than 0 (failure)

Remarks

XML indexes are not supported. If an XML index is specified, or a table name is specified with no index name and the table contains an XML index, the statement fails. To set these options, use ALTER INDEX instead.

To display the current row and page locking properties, use INDEXPROPERTY or the sys.indexes catalog view.

• Row-, page-, and table-level locks are allowed when accessing the index when AllowRowLocks = TRUE or DisAllowRowLocks = FALSE, and AllowPageLocks = TRUE or DisAllowPageLocks = FALSE. The Database Engine chooses the appropriate lock and can escalate the lock from a row or page lock to a table lock. For more information, see Lock Escalation (Database Engine).

Only a table-level lock is allowed when accessing the index when AllowRowLocks = FALSE or DisAllowRowLocks = TRUE and AllowPageLocks = FALSE or DisAllowPageLocks = TRUE.

If a table name is specified with no index, the settings are applied to all indexes on that table. When the underlying table has no clustered index (that is, it is a heap) the settings are applied as follows:

• When AllowRowLocks or DisAllowRowLocks are set to TRUE or FALSE, the setting is applied to the heap and any associated nonclustered indexes.

• When AllowPageLocks option is set to TRUE or DisAllowPageLocks is set to FALSE, the setting is applied to the heap and any associated nonclustered indexes.

• When AllowPageLocks option is set FALSE or DisAllowPageLocks is set to TRUE, the setting is fully applied to the nonclustered indexes. That is, all page locks are disallowed on the nonclustered indexes. On the heap, only the shared (S), update (U), and exclusive (X) locks for the page are disallowed. The Database Engine can still acquire an intent page lock (IS, IU or IX) for internal purposes.

For more information about configuring the locking granularity on an index, see Customizing Locking for an Index.

Permissions

Examples

A. Setting an option on a specific index

USE AdventureWorks;
GO
EXEC sp_indexoption N'Sales.Customer.IX_Customer_TerritoryID',
    N'disallowpagelocks', TRUE;

B. Setting an option on all indexes on a table

USE AdventureWorks;
GO
--Display the current row and page lock options for all indexes on the table.
SELECT name, type_desc, allow_row_locks, allow_page_locks 
FROM sys.indexes
WHERE object_id = OBJECT_ID(N'Production.Product');
GO
-- Set the disallowrowlocks option on the Product table. 
EXEC sp_indexoption N'Production.Product',
    N'disallowrowlocks', TRUE;
GO
--Verify the row and page lock options for all indexes on the table.
SELECT name, type_desc, allow_row_locks, allow_page_locks 
FROM sys.indexes
WHERE object_id = OBJECT_ID(N'Production.Product');
GO

C. Setting an option on a table with no clustered index

USE AdventureWorks;
GO
--Display the current row and page lock options of the table. 
SELECT OBJECT_NAME (object_id) AS [Table], type_desc, allow_row_locks, allow_page_locks 
FROM sys.indexes
WHERE OBJECT_NAME (object_id) = N'DatabaseLog';
GO
-- Set the disallowpagelocks option on the table. 
EXEC sp_indexoption DatabaseLog,
    N'disallowpagelocks', TRUE;
GO
--Verify the row and page lock settings of the table.
SELECT OBJECT_NAME (object_id) AS [Table], allow_row_locks, allow_page_locks 
FROM sys.indexes
WHERE OBJECT_NAME (object_id) = N'DatabaseLog';
GO

See Also

Reference