sp_indexoption (Transact-SQL)

 

ESTE TEMA SE APLICA A:síSQL Server (a partir de 2008)noBase de datos SQL de AzurenoAlmacenamiento de datos SQL de Azure noAlmacenamiento de datos paralelos

Define los valores de la opción de bloqueo para índices clúster y no clúster definidos por el usuario o tablas sin ningún índice clúster.

El Motor de base de datos de SQL Server selecciona automáticamente el bloqueo de nivel de página, fila o tabla. No es necesario establecer estas opciones manualmente. sp_indexoption se proporciona para los usuarios expertos que saben con certeza que un tipo de bloqueo determinado siempre es apropiado.

System_CAPS_ICON_important.jpg Importante


Esta característica se quitará en la versión siguiente de Microsoft SQL Server. Evite utilizar esta característica en nuevos trabajos de desarrollo y tenga previsto modificar las aplicaciones que actualmente la utilizan.En su lugar, use ALTER INDEX (Transact-SQL).

Se aplica a: SQL Server (desdeSQL Server 2008 hasta la versión actual).

Topic link icon Convenciones de sintaxis de Transact-SQL

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

[ @IndexNamePattern=] 'table_or_index_name'
Es el nombre calificado o no calificado de una tabla o índice definidos por un usuario. table_or_index_name es nvarchar(1035), no tiene ningún valor predeterminado. Las comillas solo son necesarias si se especifica un índice o nombre de tabla completo. Si se proporciona un nombre de tabla completo, incluido el nombre de la base de datos, el nombre de la base de datos debe ser el nombre de la base de datos actual. Si se especifica un nombre de tabla sin ningún índice, el valor de la opción especificada se define para todos los índices de dicha tabla y para la tabla misma si no existe ningún índice clúster.

[ @OptionName =] 'option_name'
Es un nombre de opción de índice. option_name es varchar (35), no tiene ningún valor predeterminado. option_name puede tener uno de los siguientes valores.

ValueDescription
AllowRowLocksCuando el valor es TRUE, se permiten bloqueos de fila al obtener acceso al índice. El Motor de base de datos determina cuándo se usan los bloqueos de fila. Cuando es FALSE, no se utilizan bloqueos de fila. El valor predeterminado es TRUE.
AllowPageLocksCuando el valor es TRUE, se permiten bloqueos de página al obtener acceso al índice. Motor de base de datos determina el momento en que se usan los bloqueos de página. Cuando es FALSE, no se utilizan bloqueos de página. El valor predeterminado es TRUE.
DisAllowRowLocksCuando es TRUE no se utilizan bloqueos de fila. Cuando el valor es FALSE, se permiten bloqueos de fila al obtener acceso al índice. El Motor de base de datos determina cuándo se usan los bloqueos de fila.
DisAllowPageLocksCuando es TRUE, no se utilizan bloqueos de página. Cuando el valor es FALSE, se permiten bloqueos de página al obtener acceso al índice. Motor de base de datos determina el momento en que se usan los bloqueos de página.

[ @OptionValue =] 'value'
Especifica si el option_name configuración está habilitado (TRUE, ON, yes o 1) o deshabilitado (FALSE, OFF, no o 0). valor es varchar (12), no tiene ningún valor predeterminado.

0 (correcto) o mayor que 0 (error)

Los índices XML no se admiten. Si se especifica un índice XML o un nombre de tabla sin ningún nombre de índice y la tabla contiene un índice XML, la instrucción produce un error. Para establecer estas opciones, utilice ALTER INDEX en su lugar.

Para mostrar la fila actual y la página de propiedades de bloqueo, utilice INDEXPROPERTY o sys.indexes vista de catálogo.

  • Fila, página y bloqueos de nivel de tabla se permiten al tener acceso al índice cuando AllowRowLocks = TRUE o DisAllowRowLocks = FALSE, y AllowPageLocks = TRUE o DisAllowPageLocks = FALSE. Motor de base de datos elige el bloqueo apropiado y puede cambiar de escala el bloqueo: de un bloqueo de fila o página a un bloqueo de tabla.

Se permite únicamente un bloqueo de nivel de tabla al obtener acceso al índice cuando AllowRowLocks = FALSE o DisAllowRowLocks = TRUE y AllowPageLocks = FALSE o DisAllowPageLocks = TRUE.

Si se especifica un nombre de tabla sin ningún índice, la configuración se aplica a todos los índices de esa tabla. Si la tabla subyacente no tiene ningún índice clúster (es decir, es un montón), la configuración se aplica de la siguiente manera:

  • Cuando AllowRowLocks o DisAllowRowLocks está establecida en TRUE o FALSE, la configuración se aplica al montón y cualquier índice no clúster asociado.

  • Cuando AllowPageLocks opción está establecida en TRUE o DisAllowPageLocks se establece en FALSE, la configuración se aplica al montón y cualquier índice no clúster asociado.

  • Cuando AllowPageLocks opción está establecida en FALSE o DisAllowPageLocks está establecida en TRUE, la configuración se aplica por completo a los índices no clúster. Es decir, no se permite ningún bloqueo de página en los índices no clúster. En el montón, no se permiten únicamente los bloqueos compartidos (S), de actualización (U) y exclusivos (X) de la página. El Motor de base de datos aún puede adquirir un bloqueo de página de intención (IS, IU o IX) por motivos internos.

Requiere el permiso ALTER en la tabla.

A. Definir una opción en un índice específico

El siguiente ejemplo deshabilita los bloqueos de página en el IX_Customer _ TerritoryID de índice en el Customer tabla.

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

B. Definir una opción en todos los índices de una tabla

El siguiente ejemplo no permite bloqueos de fila en los índices asociados con la tabla Product. La vista de catálogo sys.indexes se consulta antes y después de ejecutar el procedimiento sp_indexoption para mostrar los resultados de la instrucción.

USE AdventureWorks2012;  
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. Definir una opción en una tabla sin clúster

El siguiente ejemplo no permite bloqueos de página en una tabla sin clúster (un montón). La vista de catálogo sys.indexes se consulta antes y después de ejecutar el procedimiento sp_indexoption para mostrar los resultados de la instrucción.

USE AdventureWorks2012;  
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  

INDEXPROPERTY (Transact-SQL)
(Transact-SQL) de procedimientos almacenados del sistema
Sys.Indexes (Transact-SQL)

Adiciones de comunidad

AGREGAR
Mostrar: