Partager via

  • Article

sp_indexoption (Transact-SQL)

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.

Le Moteur de base de données SQL Server choisit automatiquement le niveau de verrouillage, à savoir table, ligne ou page. Vous n'avez pas besoin de définir ces options manuellement. sp_indexoption est destinée aux utilisateurs expérimentés qui savent avec certitude quel type de verrou employer.

Important

Cette fonctionnalité sera supprimée dans la prochaine version de Microsoft SQL Server. Évitez d'utiliser cette fonctionnalité dans de nouveaux travaux de développement et prévoyez de modifier les applications qui utilisent actuellement cette fonctionnalité. Utilisez plutôt ALTER INDEX (Transact-SQL).

Icône Lien de rubriqueConventions de la syntaxe de Transact-SQL

Syntaxe

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

Arguments

  • [ @IndexNamePattern=] 'table_or_index_name'
    Spécifie le nom qualifié ou non qualifié d'une table ou d'un index défini par l'utilisateur. table_or_index_name est de type nvarchar(1035) et n'a pas de 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 =] 'option_name'
    Nom d'option d'index. option_name est de type varchar(35), sans valeur par défaut. option_name peut prendre l'une des valeurs suivantes.

    Valeur

    Description

    AllowRowLocks

    Si la valeur est TRUE, les verrous de ligne sont autorisés lors de l'accès à l'index. Le Moteur de base de données détermine à quel moment les verrous de ligne sont utilisés. Si la valeur est FALSE, les verrous de ligne ne sont pas utilisés. La valeur par défaut est TRUE.

    AllowPageLocks

    Si la valeur est TRUE, les verrous de page sont autorisés lors de l'accès à l'index. Le Moteur de base de données détermine à quel moment les verrous de page sont utilisés. Si la valeur est FALSE, les verrous de page ne sont pas utilisés. La valeur par défaut est TRUE.

    DisAllowRowLocks

    Si la valeur est TRUE, les verrous de ligne ne sont pas utilisés. Si la valeur est FALSE, les verrous de ligne sont autorisés lors de l'accès à l'index. Le Moteur de base de données détermine à quel moment les verrous de ligne sont utilisés.

    DisAllowPageLocks

    Si la valeur est TRUE, les verrous de page ne sont pas utilisés. Si la valeur est FALSE, les verrous de page sont autorisés lors de l'accès à l'index. Le Moteur de base de données détermine à quel moment les verrous de page sont utilisés.

  • [ @OptionValue =] 'value'
    Indique si le paramètre option_name est activé (TRUE, ON, yes ou 1) ou désactivé (FALSE, OFF, no ou 0). L'argument value est de type varchar(12) et n'a pas de valeur par défaut.

Valeurs des codes de retour

0 (réussite) ou supérieur à 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.

Pour afficher les propriétés de verrouillage de ligne et de page actives, utilisez INDEXPROPERTY ou l'affichage catalogue sys.indexes.

  • Les verrous de niveau ligne, page et table sont autorisés lors de l'accès à l'index si AllowRowLocks a pour valeur TRUE ou DisAllowRowLocks FALSE et que AllowPageLocks a pour valeur 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. Pour plus d'informations, consultez Escalade de verrous (moteur de base de données).

Seul un verrou de niveau table est autorisé lors de l'accès à l'index si AllowRowLocks a pour valeur FALSE ou DisAllowRowLocks TRUE et que AllowPageLocks a pour valeur 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 ne possède aucun index cluster (en d'autres termes, il s'agit d'un segment de mémoire), les paramétrages sont appliqués comme suit :

  • Lorsque les options AllowRowLocks ou DisAllowRowLocks ont pour valeur TRUE ou FALSE, le paramétrage s'applique au segment de mémoire et à tout index non-cluster associé.

  • Lorsque l'option AllowPageLocks a pour valeur TRUE ou que l'option DisAllowPageLocks a pour valeur FALSE, le paramétrage s'applique au segment de mémoire et à tout index non-cluster associé.

  • Lorsque l'option AllowPageLocks a pour valeur FALSE ou que l'option DisAllowPageLocks a pour valeur TRUE, le paramétrage s'applique entièrement 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 néanmoins acquérir un verrou de page intentionnel (IS, IU ou IX) à des fins internes.

Pour plus d'informations sur la configuration de la granularité du verrouillage sur un index, consultez Personnalisation du verrouillage pour un index.

Autorisations

Requiert une autorisation ALTER sur la table.

Exemples

A. Définition d'une option sur un index spécifique

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

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

B. Définition d'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 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. Définition d'une option sur une table dépourvue d'index cluster

L'exemple suivant interdit les verrous de page sur une table dépourvue d'index cluster (un segment de mémoire). 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 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