Partager via

  • Article

Indicateurs de table (Transact-SQL)

Les indicateurs de table prennent le pas sur le comportement par défaut de l'optimiseur de requête pendant la durée de l'instruction de langage de manipulation de données (DML) en spécifiant une méthode de verrouillage, un ou plusieurs index, une opération de traitement de requête telle qu'une analyse de table ou une recherche d'index, ou d'autres options. Les indicateurs de table sont spécifiés dans la clause FROM de l'instruction DML et affectent uniquement la table ou la vue référencée dans cette clause.

AttentionAttention

Étant donné que l'optimiseur de requête SQL Server sélectionne généralement le meilleur plan d'exécution pour une requête, nous vous recommandons de ne recourir aux indicateurs qu'en dernier ressort et seulement si vous êtes un développeur ou un administrateur de base de données expérimenté.

S'applique à :

DELETE

INSERT

SELECT

UPDATE

MERGE

Icône Lien de rubriqueConventions de syntaxe Transact-SQL

Syntaxe

WITH  ( <table_hint> [ [, ]...n ] )

<table_hint> ::= 
[ NOEXPAND ] { 
    INDEX ( index_value [ ,...n ] ) | INDEX =  ( index_value )
  | FASTFIRSTROW 
  | FORCESEEK [( index_value ( index_column_name  [ ,... ] ) ) ]
  | FORCESCAN
  | HOLDLOCK 
  | NOLOCK 
  | NOWAIT
  | PAGLOCK 
  | READCOMMITTED 
  | READCOMMITTEDLOCK 
  | READPAST 
  | READUNCOMMITTED 
  | REPEATABLEREAD 
  | ROWLOCK 
  | SERIALIZABLE 
  | TABLOCK 
  | TABLOCKX 
  | UPDLOCK 
  | XLOCK 
} 

<table_hint_limited> ::=
{
    KEEPIDENTITY 
  | KEEPDEFAULTS 
  | FASTFIRSTROW 
  | HOLDLOCK 
  | IGNORE_CONSTRAINTS 
  | IGNORE_TRIGGERS 
  | NOLOCK 
  | NOWAIT
  | PAGLOCK 
  | READCOMMITTED 
  | READCOMMITTEDLOCK 
  | READPAST 
  | REPEATABLEREAD 
  | ROWLOCK 
  | SERIALIZABLE 
  | TABLOCK 
  | TABLOCKX 
  | UPDLOCK 
  | XLOCK 
}

Arguments

  • WITH ( <table_hint> ) [ [ , ]...n ]
    À quelques exceptions près, les indicateurs de table sont pris en charge uniquement lorsque les indicateurs sont spécifiés à l'aide du mot clé WITH. Les parenthèses sont obligatoires.

    Important

    L'omission du mot clé WITH est déconseillée ; cette fonctionnalité sera supprimée dans une future version de Microsoft SQL Server. Spécifiez toujours le mot clé WITH dans de nouveaux travaux de développement et modifiez les applications qui omettent actuellement ce mot clé.

    La séparation d'indicateurs par des espaces à la place de virgules est déconseillée ; cette fonctionnalité sera supprimée dans une future version de Microsoft SQL Server. Utilisez toujours des virgules dans de nouveaux travaux de développement et modifiez les applications qui omettent actuellement les virgules.

    Les indicateurs de table suivants sont autorisés avec et sans le mot clé WITH : NOLOCK, READUNCOMMITTED, UPDLOCK, REPEATABLEREAD, SERIALIZABLE, READCOMMITTED, FASTFIRSTROW, TABLOCK, TABLOCKX, PAGLOCK, ROWLOCK, NOWAIT, READPAST, XLOCK et NOEXPAND. Lorsque ces indicateurs de table sont spécifiés sans le mot clé WITH, ils doivent être définis seuls. Exemple : FROM t WITH (TABLOCK). Lorsque l'indicateur est spécifié avec une autre option, il doit être défini avec le mot clé WITH. Par exemple, FROM t WITH (TABLOCK, INDEX(myindex)).

    Les restrictions s'appliquent lorsque les indicateurs sont utilisés dans des requêtes sur des bases de données dont le niveau de compatibilité est d'au moins 90.

  • NOEXPAND
    Spécifie qu'aucune vue indexée n'est étendue pour permettre d'accéder aux tables sous-jacentes lorsque l'optimiseur de requête traite la requête. L'optimiseur de requête traite la vue comme une table avec un index cluster. NOEXPAND s'applique uniquement aux vues indexées. Pour plus d'informations, consultez la section « Remarques ».

  • INDEX (index_value [,... n ] ) | INDEX = (index_value)
    La syntaxe INDEX(index_value) spécifie le nom ou l'ID d'un ou de plusieurs index qui seront utilisés par l'optimiseur de requête lors du traitement de l'instruction. L'autre syntaxe INDEX = (index_value) spécifie une seule valeur d'index.

    S'il existe un index cluster, INDEX(0) force l'analyse de ce dernier, tandis que INDEX(1) en force l'analyse ou la recherche. S'il n'existe pas d'index cluster, INDEX(0) force l'analyse d'une table et INDEX(1) est interprété comme une erreur.

    Si plusieurs index sont utilisés dans une seule liste d'indicateurs, les éventuels doublons sont ignorés et les autres index répertoriés sont utilisés pour récupérer les lignes de la table. L'ordre des index dans l'indicateur d'index est très important. Un indicateur associé à plusieurs index met également en œuvre l'opérateur logique AND et l'optimiseur de requête applique autant de conditions que possible sur chaque index accessible. Si la collection d'index avec indicateur n'inclut pas toutes les colonnes référencées par la requête, une extraction est effectuée pour récupérer les colonnes restantes après que le Moteur de base de données SQL Server a récupéré toutes les colonnes indexées.

    Notes

    Lorsqu'une option d'index faisant référence à plusieurs index est utilisée sur la table de faits dans une jointure en étoile, l'optimiseur de requête ignore l'option d'index et retourne un message d'avertissement. De même, la réunion logique d'index avec l'opérateur OR n'est pas autorisée pour une table avec un indicateur d'index spécifié.

    Le nombre maximal d'index pouvant être spécifié dans l'indicateur de table est de 250 index non cluster.

  • KEEPIDENTITY
    Applicable uniquement dans une instruction INSERT lorsque l'option BULK est utilisée avec OPENROWSET.

    Indique que la ou les valeurs d'identité figurant dans le fichier de données importé doivent être utilisées dans la colonne identité. Si KEEPIDENTITY n'est pas spécifié, les valeurs d'identité de cette colonne sont vérifiées mais pas importées, et l'optimiseur de requête affecte automatiquement des valeurs uniques en fonction d'une valeur initiale et d'un incrément spécifié lors de la création de la table.

    Important

    Si le fichier de données ne contient pas de valeurs pour la colonne d'identité de la table ou de la vue, et que cette colonne n'est pas la dernière colonne de la table, vous devez ignorer cette colonne. Pour plus d'informations, consultez Utilisation d'un fichier de format pour ignorer un champ de données. Si une colonne d'identité est correctement ignorée, l'optimiseur de requête affecte automatiquement des valeurs uniques pour la colonne d'identité dans les lignes de table importées.

    Pour un exemple d'utilisation de cet indicateur dans une instruction INSERT ... SELECT * FROM OPENROWSET(BULK...), consultez Conservation des valeurs d'identité lors de l'importation de données en bloc.

    Pour plus d'informations sur la vérification de la valeur d'identité d'une table, consultez DBCC CHECKIDENT (Transact-SQL).

  • KEEPDEFAULTS
    Applicable uniquement dans une instruction INSERT lorsque l'option BULK est utilisée avec OPENROWSET.

    Spécifie l'insertion d'une valeur par défaut éventuelle de colonne de table, à la place de la valeur NULL, lorsqu'il manque une valeur pour la colonne dans l'enregistrement de données.

    Pour un exemple d'utilisation de cet indicateur dans une instruction INSERT ... SELECT <colonnes> FROM OPENROWSET(BULK...), consultez Conservation des valeurs NULL ou utilisation des valeurs par défaut lors de l'importation en bloc.

  • FASTFIRSTROW
    Équivalent à OPTION (FAST 1). Pour plus d'informations, consultez Indicateurs de requête (Transact-SQL).

    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 modifiez dès que possible les applications qui utilisent actuellement cette fonctionnalité.

  • FORCESEEK [ **(index_value(**index_column_name [ ,... n ] )) ]
    Indique que l'optimiseur de requête doit utiliser uniquement une opération de recherche d'index comme chemin d'accès aux données dans la table ou la vue. À compter de SQL Server 2008 R2 SP1, les paramètres d'index peuvent également être spécifiés. La spécification de FORCESEEK avec des paramètres d'index est similaire à l'utilisation de FORCESEEK avec un indicateur INDEX. Toutefois, vous pouvez bénéficier d'un plus grand contrôle sur le chemin d'accès utilisé par l'optimiseur de requête en spécifiant à la fois l'index sur lequel doit porter la recherche, et les colonnes d'index à prendre en compte dans l'opération de recherche.

    • index_value
      Valeur de l'identificateur ou nom de l'index. L'ID d'index 0 (segment de mémoire) ne peut pas être spécifié. Pour retourner l'ID ou le nom d'index, interrogez l'affichage catalogue sys.indexes.

    • index_column_name
      Nom de la colonne d'index à inclure dans l'opération de recherche. L'optimiseur de requête considère uniquement les opérations de recherche d'index dans l'index spécifié à l'aide des colonnes d'index spécifiées au minimum. L'optimiseur peut prendre en compte des colonnes supplémentaires, si nécessaire. Par exemple, si un index non cluster est spécifié, l'optimiseur peut choisir les colonnes clés d'index cluster, en plus des colonnes spécifiées.

    L'indicateur FORCESEEK peut être spécifié des manières suivantes.

    Syntaxe

    Exemple

    Description

    Sans index ou indicateur INDEX

    FROM dbo.MyTable WITH (FORCESEEK)

    L'optimiseur de requête prend uniquement en compte les opérations de recherche d'index spécifiées pour accéder à la table ou la vue via un index approprié.

    Combinaison avec un indicateur INDEX

    FROM dbo.MyTable WITH (FORCESEEK, INDEX (MyIndex))

    L'optimiseur de requête prend uniquement en compte les opération de recherche d'index pour accéder à la table ou la vue via l'index spécifié.

    Paramétré par spécification d'un index et de colonnes d'index

    FROM dbo.MyTable WITH (FORCESEEK (MyIndex (col1, col2, col3)))

    L'optimiseur de requête prend uniquement en compte les opérations de recherche d'index pour accéder à la table ou la vue via l'index spécifié, à l'aide des colonnes d'index spécifiées au minimum.

    Lors de l'utilisation de l'indicateur FORCESEEK (avec ou sans paramètres d'index), tenez compte des directives suivantes.

    • L'indicateur peut être spécifié en tant qu'indicateur de table ou en tant qu'indicateur de requête. Pour plus d'informations sur les indicateurs de requête, consultez Indicateurs de requête (Transact-SQL).

    • Pour appliquer FORCESEEK à une vue indexée, l'indicateur NOEXPAND doit également être spécifié.

    • Cet indicateur ne peut être appliqué qu'une fois au plus par table ou par vue.

    • L'indicateur ne peut pas être spécifié pour une source de données distante. L'erreur 7377 est retournée lorsque FORCESEEK est spécifié avec un indicateur d'index, tandis que l'erreur 8180 est retournée lorsque FORCESEEK est utilisé sans indicateur d'index.

    • Si FORCESEEK empêche de trouver un plan, l'erreur 8622 est retournée.

    Lorsque FORCESEEK est spécifié avec des paramètres d'index, les instructions et restrictions suivantes s'appliquent.

    • L'indicateur ne peut pas être spécifié avec un indicateur INDEX ou un autre indicateur FORCESEEK.

    • Au moins une colonne doit être spécifiée et il doit s'agir de la colonne clé principale.

    • Des colonnes d'index supplémentaires peuvent être spécifiées, mais il n'est pas possible d'ignorer des colonnes clés. Par exemple, si l'index spécifié contient les colonnes clés a, b et c, la syntaxe valide inclut FORCESEEK (MyIndex (a)) et FORCESEEK (MyIndex (a, b). Une syntaxe non valide inclurait FORCESEEK (MyIndex (c)) et FORCESEEK (MyIndex (a, c).

    • L'ordre des noms de colonne spécifiés dans l'indicateur doit correspondre à l'ordre des colonnes dans l'index référencé.

    • Les colonnes qui ne figurent pas dans la définition de clé d'index ne peuvent pas être spécifiées. Par exemple, dans un index non cluster, seules les colonnes clés d'index définies peuvent être spécifiées. Les colonnes clés cluster qui sont incluses automatiquement dans l'index ne peuvent pas être spécifiées, mais elles peuvent être utilisées par l'optimiseur.

    • La modification de la définition d'index (par exemple, par ajout ou suppression de colonnes) peut nécessiter des modifications des requêtes qui font référence à cet index.

    • L'indicateur empêche l'optimiseur de prendre en compte les index XML ou spatiaux sur la table.

    • Cet indicateur ne peut pas être spécifié avec l'indicateur FORCESCAN.

    • Pour les index partitionnés, la colonne de partitionnement ajoutée implicitement par SQL Server ne peut pas être spécifiée dans l'indicateur FORCESEEK. Pour plus d'informations, consultez Consignes spéciales pour les index partitionnés.

    AttentionAttention

    La spécification de FORCESEEK avec des paramètres d'index limite davantage le nombre de plans pouvant être pris en compte par l'optimiseur que la spécification de FORCESEEK sans paramètres. Cela peut entraîner une erreur de génération de plan dans un plus grand nombre de cas. Dans une prochaine version, les modifications internes apportées à l'optimiseur pourront peut-être autoriser la prise en compte de davantage de plans. Pour plus d'informations, consultez Utilisation de l'indicateur de table FORCESEEK.

  • FORCESCAN
    Apparu dans SQL Server 2008 R2 SP1, cet indicateur spécifie que l'optimiseur de requête utilise uniquement une opération d'analyse d'index comme chemin d'accès à la table ou la vue référencée. L'indicateur FORCESCAN peut être utile dans les requêtes dans lesquelles l'optimiseur sous-estime le nombre de lignes concernées et choisit une opération de recherche plutôt qu'une opération d'analyse. Lorsque cela se produit, la quantité de mémoire octroyée à l'opération est insuffisante, ce qui a des conséquences sur les performances de la requête.

    FORCESCAN peut être spécifié avec ou sans indicateur INDEX. Lorsqu'il est associé à un indicateur d'index, (INDEX = index_name, FORCESCAN), l'optimiseur de requête ne prend en compte que les chemins d'accès d'analyse via l'index spécifié lors de l'accès à la table référencée. FORCESCAN peut être spécifié avec l'indicateur d'index INDEX(0) afin de forcer une opération d'analyse de table sur la table de base.

    Pour les index et les tables partitionnées, FORCESCAN est appliqué une fois que les partitions ont été éliminées via une évaluation du prédicat de la requête. Cela signifie que l'analyse est appliquée uniquement aux partitions restantes, et non à la table entière.

    L'indicateur FORCESCAN est soumis aux restrictions suivantes.

    • L'indicateur ne peut pas être spécifié pour une table qui est la cible d'une instruction INSERT, UPDATE ou DELETE.

    • L'indicateur ne peut pas être utilisé avec plusieurs indicateurs d'index.

    • L'indicateur empêche l'optimiseur de prendre en compte les index XML ou spatiaux sur la table.

    • L'indicateur ne peut pas être spécifié pour une source de données distante.

    • Cet indicateur ne peut pas être spécifié avec l'indicateur FORCESEEK.

  • HOLDLOCK
    Équivalent à SERIALIZABLE. Pour plus d'informations, consultez SERIALIZABLE plus loin dans cette rubrique. L'option HOLDLOCK s'applique uniquement à la table ou à la vue pour laquelle elle est spécifiée et uniquement pour la durée de la transaction définie par l'instruction dans laquelle elle est utilisée. HOLDLOCK ne peut pas être utilisée dans une instruction SELECT qui comprend l'option FOR BROWSE.

  • IGNORE_CONSTRAINTS
    Applicable uniquement dans une instruction INSERT lorsque l'option BULK est utilisée avec OPENROWSET.

    Spécifie que toutes les contraintes de la table sont ignorées par l'opération d'importation en bloc. Par défaut, INSERT vérifie les contraintes CHECK et FOREIGN KEY. Lorsque IGNORE_CONSTRAINTS est spécifié pour une opération d'importation en bloc, INSERT doit ignorer ces contraintes sur une table cible. Notez que vous ne pouvez pas désactiver les contraintes UNIQUE, PRIMARY KEY ou NOT NULL.

    Vous avez la possibilité de désactiver les contraintes CHECK et FOREIGN KEY si les données d'entrée contiennent des lignes qui violent des contraintes. En désactivant ces contraintes, vous pouvez importer les données, puis utiliser des instructions Transact-SQL pour les nettoyer.

    Cependant, lorsque les contraintes CHECK et FOREIGN KEY sont ignorées, chaque contrainte ignorée sur la table est marquée sous la forme is_not_trusted dans l'affichage catalogue sys.check_constraints ou sys.foreign_keys, une fois l'opération terminée. À un certain stade, vous devez vérifier les contraintes sur la table entière. Si la table n'était pas vide avant l'opération d'importation en bloc, il revient plus cher de valider à nouveau la contrainte que d'appliquer des contraintes CHECK et FOREIGN KEY aux données incrémentielles.

  • IGNORE_TRIGGERS
    Applicable uniquement dans une instruction INSERT lorsque l'option BULK est utilisée avec OPENROWSET.

    Spécifie que tous les déclencheurs définis sur la table sont ignorés par l'opération d'importation en bloc. Par défaut, INSERT applique les déclencheurs.

    Utilisez IGNORE_TRIGGERS uniquement si l'application ne dépend d'aucun déclencheur et que l'optimisation des performances est importante.

  • NOLOCK
    Équivalent à READUNCOMMITTED. Pour plus d'informations, consultez READUNCOMMITTED plus loin dans cette rubrique.

    Notes

    Pour les instructions UPDATE ou DELETE : Cette fonctionnalité sera supprimée dans une 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é.

  • NOWAIT
    Indique au Moteur de base de données de retourner un message dès qu'un verrou est rencontré sur la table. L'utilisation de NOWAIT est équivalente à la spécification de SET LOCK_TIMEOUT 0 pour une table spécifique.

  • PAGLOCK
    Établit des verrous de page là où des verrous individuels sont généralement utilisés sur des lignes ou des clés ou là où un verrou de table unique est généralement utilisé. Par défaut, utilise le mode de verrou approprié pour l'opération. Si cet argument est spécifié dans des transactions fonctionnant au niveau d'isolement SNAPSHOT, les verrous de page ne sont établis que si PAGLOCK est combiné avec d'autres indicateurs de table qui requièrent des verrous, tels que UPDLOCK et HOLDLOCK.

  • READCOMMITTED
    Spécifie que les opérations de lecture doivent respecter les règles du niveau d'isolation READ COMMITTED en utilisant le verrouillage ou la gestion des versions de ligne. Si l'option de base de données READ_COMMITTED_SNAPSHOT a la valeur OFF, le Moteur de base de données acquiert des verrous partagés lorsque les données sont lues et libère ces verrous lorsque l'opération de lecture est achevée. Si l'option de base de données READ_COMMITTED_SNAPSHOT a pour valeur ON, le Moteur de base de données n'acquiert pas de verrous et utilise la gestion des versions de ligne. Pour plus d'informations sur les niveaux d'isolation, consultez SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

    Notes

    Pour les instructions UPDATE ou DELETE : Cette fonctionnalité sera supprimée dans une 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é.

  • READCOMMITTEDLOCK
    Spécifie que les opérations de lecture doivent respecter les règles du niveau d'isolation READ COMMITTED en utilisant le verrouillage. Le Moteur de base de données acquiert des verrous partagés lorsque les données sont lues et libère ces verrous lorsque l'opération de lecture est achevée, quelle que soit la valeur de l'option de base de données READ_COMMITTED_SNAPSHOT. Pour plus d'informations sur les niveaux d'isolation, consultez SET TRANSACTION ISOLATION LEVEL (Transact-SQL). Cet indicateur ne peut pas être spécifié sur la table cible d'une instruction INSERT ; l'erreur 4140 est retournée.

  • READPAST
    Spécifie que Moteur de base de données ne doit pas lire les lignes qui sont verrouillées par d'autres transactions. Lorsque READPAST est spécifié, les verrous de niveau ligne sont ignorés. Tant que les verrous ne sont pas libérés, le Moteur de base de données ignore les lignes au lieu de bloquer la transaction actuelle. Par exemple, supposons que la table T1 contienne une colonne entière unique avec les valeurs 1, 2, 3, 4, 5. Si la transaction A remplace la valeur 3 par 8 mais n'a pas encore validé, une instruction SELECT * FROM T1 (READPAST) obtient les valeurs 1, 2, 4, 5. READPAST est essentiellement utilisé pour réduire le conflit de verrouillage lors de l'implémentation d'une file d'attente de travail qui utilise une table SQL Server. Un lecteur de file d'attente qui utilise READPAST ignore les entrées de file d'attente verrouillées par d'autres transactions et prend en compte l'entrée de file d'attente suivante disponible, sans attendre que les autres transactions libèrent leurs verrous.

    READPAST peut être spécifié pour toute table référencée dans une instruction UPDATE ou DELETE et pour toute table référencée dans une clause FROM. Lorsqu'il est spécifié dans une instruction UPDATE et quel que soit l'emplacement auquel il est défini dans celle-ci, l'argument READPAST est uniquement appliqué lorsque l'opération lit des données pour identifier les enregistrements à mettre à jour. READPAST ne peut pas être spécifié pour des tables dans la clause INTO d'une instruction INSERT. Les opérations de lecture qui utilisent READPAST ne se bloquent pas. Les opérations de mise à jour ou de suppression qui utilisent READPAST peuvent se bloquer lorsqu'elles lisent des clés étrangères ou des vues indexées ou lorsqu'elles modifient des index secondaires.

    READPAST ne peut être spécifié que dans les transactions fonctionnant aux niveaux d'isolation READ COMMITTED ou REPEATABLE READ. Si cet argument est spécifié dans des transactions fonctionnant au niveau d'isolement SNAPSHOT, READPAST doit être combiné avec d'autres indicateurs de table qui requièrent des verrous, tels que UPDLOCK et HOLDLOCK.

    L'indicateur de table READPAST ne peut pas être spécifié quand l'option de base de données READ_COMMITTED_SNAPSHOT a la valeur ON et que l'une ou l'autre des conditions suivantes a la valeur True.

    • Le niveau d'isolation des transactions de la session est READ COMMITTED.

    • L'indicateur READCOMMITTED de la table est également spécifié dans la requête.

    Pour spécifier l'indicateur READPAST dans ces cas, supprimez l'indicateur de table READCOMMITTED s'il est présent, et incorporez l'indicateur de table READCOMMITTEDLOCK dans la requête.

  • READUNCOMMITTED
    Indique que les lectures erronées sont autorisées. Aucun verrou partagé n'est émis pour empêcher d'autres transactions de modifier les données lues par la transaction actuelle et les verrous exclusifs définis par les autres transactions n'empêchent pas la transaction actuelle de lire les données verrouillées. L'autorisation des lectures erronées peut accroître la concurrence, mais au prix de la lecture de modifications de données qui sont ensuite restaurées par d'autres transactions. Cela peut générer des erreurs pour votre transaction, confronter les utilisateurs à des données qui n'ont jamais été validées ou présenter aux utilisateurs deux fois les mêmes enregistrements (ou pas du tout). Pour plus d'informations sur les lectures erronées, les lectures non reproductibles et les lectures fantômes, consultez Effet des accès concurrentiels.

    Les indicateurs READUNCOMMITTED et NOLOCK s'appliquent uniquement aux verrous de données. Toutes les requêtes, y compris celles dotées d'indicateurs READUNCOMMITTED et NOLOCK, obtiennent des verrous Sch-S (Stabilité du schéma) durant la compilation et l'exécution. Par conséquent, les requêtes sont bloquées lorsqu'une transaction simultanée détient un verrou de modification du schéma (Sch-M) sur la table. Par exemple, une opération DDL (Data Definition Language) acquiert un verrou Sch-M avant de modifier les informations de schéma de la table. Toutes les requêtes simultanées (y compris celles exécutées avec des indicateurs READUNCOMMITTED ou NOLOCK) sont bloquées lors des tentatives d'obtention d'un verrou Sch-S. Inversement, une requête qui détient un verrou Sch-S bloque une transaction simultanée qui tente d'acquérir un verrou Sch-M. Pour plus d'informations sur le comportement des verrous, consultez Compatibilité de verrouillage (moteur de base de données).

    Il est impossible de spécifier READUNCOMMITTED et NOLOCK pour des tables modifiées par des opérations d'insertion, de mise à jour ou de suppression. L'optimiseur de requête SQL Server ignore les indicateurs READUNCOMMITTED et NOLOCK dans la clause FROM s'appliquant à la table cible d'une instruction UPDATE ou DELETE.

    Notes

    La prise en charge des indicateurs READUNCOMMITTED et NOLOCK dans la clause FROM s'appliquant à la table cible d'une instruction UPDATE ou DELETE sera supprimée dans une version future de SQL Server. Évitez d'utiliser ces indicateurs dans ce contexte lors de vos nouvelles tâches de développement, et pensez à modifier les applications qui les utilisent actuellement.

    Vous pouvez réduire au maximum les conflits de verrouillage tout en protégeant les transactions contre les lectures erronées de modifications de données non validées en utilisant l'un des niveaux d'isolation suivants :

    • le niveau d'isolation READ COMMITTED avec l'option de base de données READ_COMMITTED_SNAPSHOT activée (ON) ;

    • le niveau d'isolement SNAPSHOT.

    Pour plus d'informations sur les niveaux d'isolation, consultez SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

    Notes

    Si vous recevez le message d'erreur 601 lorsque READUNCOMMITTED est spécifié, résolvez-le comme une erreur de blocage (1205) et relancez votre instruction.

  • REPEATABLEREAD
    Indique qu'une recherche est effectuée avec la même sémantique de verrouillage qu'une transaction à un niveau d'isolation REPEATABLE READ. Pour plus d'informations sur les niveaux d'isolation, consultez SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

  • ROWLOCK
    Spécifie que les verrous de ligne sont établis lorsque les verrous de page ou de table sont généralement placés. Si cet argument est spécifié dans des transactions fonctionnant au niveau d'isolement SNAPSHOT, les verrous de ligne ne sont établis que si ROWLOCK est combiné avec d'autres indicateurs de table qui requièrent des verrous, tels que UPDLOCK et HOLDLOCK.

  • SERIALIZABLE
    Équivalent à HOLDLOCK. Étend les restrictions associées aux verrous partagés en les maintenant jusqu'à l'achèvement de la transaction, au lieu de les relâcher dès que la table ou la page de données n'est plus utilisée, que la transaction soit achevée ou non. Effectue une recherche avec la même sémantique de verrouillage qu'une transaction à un niveau d'isolation SERIALIZABLE. Pour plus d'informations sur les niveaux d'isolation, consultez SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

  • TABLOCK
    Indique que le verrou acquis est appliqué au niveau de la table. Le type de verrou qui est acquis dépend de l'instruction en cours d'exécution. Par exemple, une instruction SELECT peut acquérir un verrou partagé. En spécifiant TABLOCK, le verrou partagé est appliqué à l'intégralité de la table, et non au niveau de la ligne ou de la page. Si l'option HOLDLOCK est également spécifiée, le verrou de table est maintenu jusqu'à la fin de la transaction.

    Lors de l'importation de données dans un segment de mémoire à l'aide de l'instruction INSERT INTO <target_table> SELECT <columns> FROM <source_table>, vous pouvez activer l'optimisation de la journalisation et du verrouillage de l'instruction en spécifiant l'indicateur TABLOCK pour la table cible. En outre, le mode de récupération de la base de données doit correspondre au mode simple ou au mode de récupération utilisant les journaux de transactions. Pour plus d'informations, consultez INSERT (Transact-SQL).

    Lorsque TABLOCK est utilisé avec le fournisseur d'ensembles de lignes OPENROWSET pour importer des données dans une table, il permet à plusieurs clients de charger en même temps les données dans la table cible avec une optimisation de la journalisation et du verrouillage. Pour plus d'informations, consultez Conditions requises pour une journalisation minimale dans l'importation en bloc.

  • TABLOCKX
    Indique qu'un verrou exclusif est établi sur la table.

  • UPDLOCK
    Spécifie que les verrous de mise à jour doivent être établis et maintenus jusqu'à ce que la transaction s'achève. UPDLOCK établit les verrous de mise à jour pour les opérations de lecture uniquement au niveau de la ligne ou de la page. Si UPDLOCK est associé à TABLOCK, ou qu'un verrou de niveau de la table est établi pour toute autre raison, un verrou exclusif (X) est implémenté à la place.

    Lorsque UPDLOCK est spécifié, les indicateurs de niveau d'isolement READCOMMITTED et READCOMMITTEDLOCK sont ignorés. Par exemple, si le niveau d'isolement de la session est défini sur SERIALIZABLE et qu'une requête spécifie (UPDLOCK, READCOMMITTED), l'indicateur READCOMMITTED est ignoré et la transaction s'exécute à l'aide du niveau d'isolement SERIALIZABLE.

  • XLOCK
    Spécifie que les verrous exclusifs doivent être établis et maintenus jusqu'à ce que la transaction s'achève. Si l'option ROWLOCK, PAGLOCK ou TABLOCK est spécifiée, les verrous exclusifs s'appliquent au niveau de granularité approprié.

Notes

Les indicateurs de table sont ignorés si l'accès à la table ne s'effectue pas par un plan de requête. Ceci peut résulter du choix de l'optimiseur d'empêcher globalement l'accès à la table ou de l'accès à une vue indexée à la place. Dans ce dernier cas, l'accès à une vue indexée peut être proscrit à l'aide de l'indicateur de requête OPTION (EXPAND VIEWS).

Tous les indicateurs de verrou sont diffusés à toutes les vues et tables accessibles par le plan de requête ainsi que les vues et tables référencées dans une vue. En outre, SQL Server effectue les contrôles de cohérence de verrous correspondants.

Les indicateurs de verrou ROWLOCK, UPDLOCK et XLOCK qui acquièrent des verrous de niveau ligne peuvent placer des verrous sur des clés d'index plutôt que sur les lignes de données elles-mêmes. Par exemple, si une table possède un index non cluster et qu'une instruction SELECT utilisant un indicateur de verrou est gérée par un index explicatif, un verrou est acquis sur la clé d'index dans l'index explicatif plutôt que sur la ligne de données dans la table de base.

Si une table contient des colonnes calculées et que celles-ci sont traitées par des expressions ou des fonctions ayant accès à des colonnes dans d'autres tables, les indicateurs de table ne sont pas utilisés sur ces dernières. Cela signifie que les indicateurs de table ne sont pas propagés. Par exemple, l'indicateur de table NOLOCK est spécifié dans une table de la requête. Cette table possède des colonnes calculées par une combinaison d'expressions et de fonctions qui accèdent à des colonnes dans une autre table. Les tables référencées par les expressions et les fonctions n'utilisent pas l'indicateur de table NOLOCK lors de l'accès à ces dernières.

SQL Server n'autorise pas plus d'un indicateur de table dans chacun des groupes suivants pour chaque table de la clause FROM :

  • indicateurs de granularité : PAGLOCK, NOLOCK, READCOMMITTEDLOCK, ROWLOCK, TABLOCK ou TABLOCKX.

  • indicateurs de niveau d'isolation : HOLDLOCK, NOLOCK, READCOMMITTED, REPEATABLEREAD, SERIALIZABLE.

Indicateurs d'index filtré

Un index filtré peut être utilisé comme indicateur de table mais, en conséquence, l'optimiseur de requête génèrera l'erreur 8622 si l'index ne couvre pas toutes les lignes que la requête sélectionne. Vous trouverez ci-dessous un exemple d'indicateur d'index filtré non valide. Cet exemple illustre la création de l'index FIBillOfMaterialsWithComponentID filtré, puis l'utilisation de cet index comme indicateur d'index pour une instruction SELECT. Le prédicat d'index filtré inclut des lignes de données pour les ComponentID 533, 324 et 753. Le prédicat de la requête inclut également des lignes de données pour les ComponentID 533, 324 et 753 mais étend le jeu de résultats pour inclure les ComponentID 855 et 924, qui ne figurent pas dans l'index filtré. Par conséquent, l'optimiseur de requête ne peut pas utiliser l'indicateur d'index filtré et génère l'erreur 8622. Pour plus d'informations, consultez Règles de conception d'index filtrés.

USE AdventureWorks2008R2;
GO
IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'FIBillOfMaterialsWithComponentID' 
    AND object_id = OBJECT_ID(N'Production.BillOfMaterials'))
DROP INDEX FIBillOfMaterialsWithComponentID
    ON Production.BillOfMaterials;
GO
CREATE NONCLUSTERED INDEX "FIBillOfMaterialsWithComponentID"
    ON Production.BillOfMaterials (ComponentID, StartDate, EndDate)
    WHERE ComponentID IN (533, 324, 753);
GO
SELECT StartDate, ComponentID FROM Production.BillOfMaterials
    WITH( INDEX (FIBillOfMaterialsWithComponentID) )
    WHERE ComponentID in (533, 324, 753, 855, 924);
GO

L'optimiseur de requête ne prendra pas en compte un indicateur d'index si les options SET n'ont pas les valeurs requises pour les index filtrés. Pour plus d'informations, consultez CREATE INDEX (Transact-SQL).

Utilisation de NOEXPAND

NOEXPAND s'applique uniquement aux vues indexées. Une vue indexée comporte un index cluster unique, créé sur cette dernière. Si une requête contient des références à des colonnes présentes à la fois dans une vue indexée et dans des tables de base, et que l'optimiseur de requête préconise l'utilisation de la vue indexée comme méthode d'exécution de la requête, il utilise alors l'index sur la vue. Cette fonction est appelée correspondance de vue indexée et n'est prise en charge que dans SQL Server Entreprise Edition et Developer Edition.

Toutefois, pour que l'optimiseur prenne en considération les vues indexées pour la mise en correspondance ou utilise une vue indexée référencée avec l'indicateur NOEXPAND, les options SET suivantes doivent avoir pour valeur ON :

ANSI_NULLS

ANSI_WARNINGS

CONCAT_NULL_YIELDS_NULL

ANSI_PADDING

ARITHABORT1

QUOTED_IDENTIFIERS

1 ARITHABORT a implicitement la valeur ON lorsque ANSI_WARNINGS a pour valeur ON. Par conséquent, vous n'avez pas besoin d'ajuster ce paramètre manuellement.

En outre, l'option NUMERIC_ROUNDABORT doit être désactivée (OFF).

Pour contraindre l'optimiseur à utiliser un index pour une vue indexée, spécifiez l'option NOEXPAND. Cet indicateur peut être utilisé uniquement si la vue est également nommée dans la requête. SQL Server ne fournit pas d'indicateur pour imposer l'utilisation d'une vue indexée particulière dans une requête qui ne la nomme pas directement dans la clause FROM ; toutefois, l'optimiseur de requête admet l'utilisation de vues indexées même si elles ne sont pas référencées directement dans la requête.

Pour plus d'informations, consultez Résolution d'index sur les vues.

Utilisation d'un indicateur de table comme indicateur de requête

Les indicateurs de table peuvent également être spécifiés comme un indicateur de requête à l'aide de la clause OPTION (TABLE HINT). Nous recommandons d'utiliser un indicateur de table comme indicateur de requête uniquement dans le contexte d'un repère de plan. Pour les requêtes ad hoc, spécifiez ces indicateurs uniquement comme indicateurs de table. Pour plus d'informations, consultez Indicateurs de requête (Transact-SQL).

Autorisations

Les indicateurs KEEPIDENTITY, IGNORE_CONSTRAINTS et IGNORE_TRIGGERS requièrent des autorisations ALTER sur la table.

Exemples

A. Utilisation de l'indicateur TABLOCK pour spécifier une méthode de verrouillage

L'exemple suivant spécifie qu'un verrou partagé est établi sur la table Production.Product et maintenu jusqu'à la fin de l'instruction UPDATE.

USE AdventureWorks2008R2;
GO
UPDATE Production.Product
WITH (TABLOCK)
SET ListPrice = ListPrice * 1.10
WHERE ProductNumber LIKE 'BK-%';
GO

B. Utilisation de l'indicateur FORCESEEK pour spécifier une opération de recherche d'index

L'exemple ci-dessous utilise l'indicateur FORCESEEK sans spécifier d'index pour forcer l'optimiseur de requête à effectuer une opération de recherche d'index sur la table Sales.SalesOrderDetail.

USE AdventureWorks2008R2;
GO
SELECT *
FROM Sales.SalesOrderHeader AS h
INNER JOIN Sales.SalesOrderDetail AS d WITH (FORCESEEK)
    ON h.SalesOrderID = d.SalesOrderID 
WHERE h.TotalDue > 100
AND (d.OrderQty > 5 OR d.LineTotal < 1000.00);
GO

L'exemple ci-dessous utilise l'indicateur FORCESEEK avec un index pour forcer l'optimiseur de requête à effectuer une opération de recherche d'index sur l'index et la colonne d'index spécifiés.

USE AdventureWorks2008R2; 
GO
SELECT h.SalesOrderID, h.TotalDue, d.OrderQty
FROM Sales.SalesOrderHeader AS h
    INNER JOIN Sales.SalesOrderDetail AS d 
    WITH (FORCESEEK (PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID (SalesOrderID))) 
    ON h.SalesOrderID = d.SalesOrderID 
WHERE h.TotalDue > 100
AND (d.OrderQty > 5 OR d.LineTotal < 1000.00); 
GO

C. Utilisation de l'indicateur FORCECAN pour spécifier une opération d'analyse d'index

L'exemple ci-dessous utilise l'indicateur FORCESCAN pour forcer l'optimiseur de requête à effectuer une opération d'analyse sur la table Sales.SalesOrderDetail.

USE AdventureWorks2008R2; 
GO
SELECT h.SalesOrderID, h.TotalDue, d.OrderQty
FROM Sales.SalesOrderHeader AS h
    INNER JOIN Sales.SalesOrderDetail AS d 
    WITH (FORCESCAN) 
    ON h.SalesOrderID = d.SalesOrderID 
WHERE h.TotalDue > 100
AND (d.OrderQty > 5 OR d.LineTotal < 1000.00);