Compartir vía

sp_indexoption (Transact-SQL)

  • Artículo

Se aplica a: SQL Server

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 toma automáticamente las opciones de 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 determinado de bloqueo siempre es adecuado.

Importante

Esta característica se quitará en una versión futura de 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.

Convenciones de sintaxis de Transact-SQL

Sintaxis

sp_indexoption
    [ @IndexNamePattern = ] N'IndexNamePattern'
    , [ @OptionName = ] 'OptionName'
    , [ @OptionValue = ] 'OptionValue'
[ ; ]

Argumentos

[ @IndexNamePattern = ] N'IndexNamePattern'

Nombre completo o no calificado de una tabla o índice definidos por el usuario. @IndexNamePattern es nvarchar(1035), sin 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 = ] 'OptionName'

Nombre de la opción de índice. @OptionName es varchar(35) y puede ser uno de los valores siguientes.

Valor Descripción
AllowRowLocks Cuando TRUE, se permiten bloqueos de fila al acceder al índice. El Motor de base de datos determina cuándo se usan los bloqueos de fila. Cuando FALSE, no se usan bloqueos de fila. El valor predeterminado es TRUE.
AllowPageLocks Cuando TRUE, se permiten bloqueos de página al acceder al índice. Motor de base de datos determina el momento en que se usan los bloqueos de página. Cuando FALSE, no se usan bloqueos de página. El valor predeterminado es TRUE.
DisAllowRowLocks Cuando TRUE, no se usan bloqueos de fila. Cuando FALSE, se permiten bloqueos de fila al acceder al índice. El Motor de base de datos determina cuándo se usan los bloqueos de fila.
DisAllowPageLocks Cuando TRUE, no se usan bloqueos de página. Cuando FALSE, se permiten bloqueos de página al acceder al índice. Motor de base de datos determina el momento en que se usan los bloqueos de página.

[ @OptionValue = ] 'OptionValue'

Especifica si la configuración de @OptionName está habilitada (TRUE, ON, yeso 1) o deshabilitada (FALSE, OFF, noo 0). @OptionValue es varchar(12), sin ningún valor predeterminado.

Valores de código de retorno

0 (correcto) o > 0 (erróneo).

Comentarios

No se admiten índices XML. 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, use ALTER INDEX en su lugar.

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

  • Se permiten bloqueos de nivel de fila, de nivel de página y de tabla al acceder 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.

Solo se permite un bloqueo de nivel de tabla al acceder al índice cuando AllowRowLocks = FALSE o DisAllowRowLocks = TRUE o AllowPageLocks = FALSE 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. Cuando la tabla subyacente no tiene ningún índice agrupado (es decir, es un montón), la configuración se aplica de la siguiente manera:

  • Cuando AllowRowLocks o se establecen TRUE en o FALSEDisAllowRowLocks , la configuración se aplica al montón y a los índices no clúster asociados.

  • Cuando AllowPageLocks la opción se establece TRUE en o DisAllowPageLocks se establece FALSEen , la configuración se aplica al montón y a los índices no clúster asociados.

  • Cuando AllowPageLocks se establece FALSE la opción o DisAllowPageLocks se establece en TRUE, la configuración se aplica completamente 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.

Permisos

Debe tener un permiso de ALTER sobre la tabla.

Ejemplos

A Establecer una opción en un índice específico

En el ejemplo siguiente se deshabilitan los bloqueos de página en el IX_Customer_TerritoryID índice de la Customer tabla.

USE AdventureWorks2022;
GO

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

B. Establecer 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 AdventureWorks2022;
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. Establecimiento de una opción en una tabla sin índice agrupado

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

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

Contenido relacionado