sp_indexoption (Transact-SQL)

S’applique à : SQL Server

Définit les valeurs d'option de verrouillage des index cluster et non cluster ou des tables dépourvues d'index cluster définis par l'utilisateur.

SQL Server Moteur de base de données effectue automatiquement des choix de verrouillage au niveau de la page, de la ligne ou de la table. Vous n’avez pas besoin de définir ces options manuellement. sp_indexoption est fourni aux utilisateurs experts qui savent avec certitude qu’un type particulier de verrou est toujours approprié.

Conventions de la syntaxe Transact-SQL

Syntaxe

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

Arguments

[ @IndexNamePattern = ] N'IndexNamePattern'

Nom qualifié ou non qualifié d’une table ou d’un index défini par l’utilisateur. @IndexNamePattern est nvarchar(1035), sans valeur par défaut. Les guillemets ne sont nécessaires que si l'on spécifie un nom qualifié de table ou d'index. Si un nom de table complet (incluant un nom de base de données) est fourni, le nom de base de données doit être celui de la base de données en cours. Si un nom de table est spécifié sans index, la valeur d'option spécifiée est définie pour tous les index de cette table et, si aucun index cluster n'existe, pour la table elle-même.

[ @OptionName = ] 'OptionName'

Nom de l’option d’index. @OptionName est varchar(35) et peut être l’une des valeurs suivantes.

[ @OptionValue = ] 'OptionValue'

Spécifie si le paramètre @OptionName est activé (TRUE, ON, ou yes1) ou désactivé (FALSE, , OFFou no0). @OptionValue est varchar(12), sans valeur par défaut.

Valeurs des codes de retour

0 (réussite) or > 0 (échec).

Notes

Les index XML ne sont pas pris en charge. Si un index XML est spécifié ou qu'un nom de table est spécifié sans nom d'index et que la table contient un index XML, l'instruction échoue. Pour définir ces options, utilisez ALTER INDEX à la place.

Pour afficher les propriétés de verrouillage de ligne et de page actuelles, utilisez indexPROPERTY ou l’affichage catalogue sys.indexes .

• Les verrous au niveau des lignes, des pages et des verrous au niveau de la table sont autorisés lors de l’accès à l’index lorsque AllowRowLocks = TRUE ou DisAllowRowLocks = FALSE, et AllowPageLocks = TRUE ou DisAllowPageLocks = FALSE. Le Moteur de base de données choisit le verrou approprié et peut promouvoir un verrou de ligne ou de page en verrou de table.

Seul un verrou au niveau de la table est autorisé lors de l’accès à l’index quand AllowRowLocks = FALSE ou DisAllowRowLocks = TRUE AllowPageLocks = FALSE ou .DisAllowPageLocks = TRUE

Si un nom de table est spécifié sans index, les paramétrages sont appliqués à tous les index de cette table. Lorsque la table sous-jacente n’a pas d’index cluster (autrement dit, il s’agit d’un tas), les paramètres sont appliqués comme suit :

• Quand AllowRowLocks ou DisAllowRowLocks sont définis TRUE sur ou FALSE, le paramètre est appliqué au tas et à tous les index non cluster associés.

• Lorsque AllowPageLocks l’option est définie TRUE ou DisAllowPageLocks FALSEdéfinie sur , le paramètre est appliqué au tas et à tous les index non cluster associés.

• Lorsque AllowPageLocks l’option est définie FALSE TRUEou DisAllowPageLocks définie sur , le paramètre est entièrement appliqué aux index non cluster. En d'autres termes, tous les verrous de page sont interdits sur les index non-cluster. Sur le segment de mémoire, seuls les verrous partagés (S), de mise à jour (U) et exclusifs (X) de la page sont interdits. Le Moteur de base de données peut toujours acquérir un verrou de page intentionnel (IS, IU ou IX) à des fins internes.

autorisations

Nécessite l'autorisation ALTER sur la table.

Exemples

R. Définir une option sur un index spécifique

L’exemple suivant interdit les verrous de page sur l’index IX_Customer_TerritoryID de la Customer table.

USE AdventureWorks2022;
GO

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

B. Définir une option sur tous les index d’une table

L'exemple suivant interdit les verrous de ligne sur tous les index associés à la table Product. L'interrogation de l'affichage catalogue sys.indexes avant et après l'exécution de la procédure sp_indexoption permet d'afficher les résultats de l'instruction.

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. Définir une option sur une table sans index cluster

L'exemple suivant interdit les verrous de page sur une table dépourvue d'index cluster (un segment de mémoire). L’affichage sys.indexes catalogue est interrogé avant et après l’exécution de la sp_indexoption procédure pour afficher les résultats de l’instruction.

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

Contenu connexe

• INDEXPROPERTY (Transact-SQL)
• Procédures stockées système (Transact-SQL)
• sys.indexes (Transact-SQL)