Partager via

Indicateur de table (T-SQL)

  • Article

Mis à jour : 12 décembre 2006

Spécifie que l'optimiseur de requête doit utiliser une analyse de table, un ou plusieurs index ou une méthode de verrouillage avec cette table ou vue et pour cette instruction SELECT, INSERT, UPDATE ou DELETE. Bien que cette option soit autorisée, vous devez généralement laisser l'optimiseur de requête choisir la meilleure méthode d'optimisation sans spécifier d'indicateur.

ms187373.note(fr-fr,SQL.90).gifImportant :
Étant donné que l'optimiseur de requête SQL Server 2005 sélectionne généralement le meilleur plan d'exécution pour une requête, il est recommandé que les indicateurs, y compris <table_hint>, soient uniquement utilisés en dernier recours par les administrateurs de base de données et les développeurs expérimentés.

S'applique à :

DELETE

INSERT

SELECT

UPDATE

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

Syntaxe

<table_hint> ::= 
[ NOEXPAND ] { 
    INDEX ( index_val [ ,...n ] )
  | FASTFIRSTROW 
  | 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 
  | NOWAIT
    | PAGLOCK 
  | READCOMMITTED 
  | READCOMMITTEDLOCK 
  | READPAST 
  | REPEATABLEREAD 
  | ROWLOCK 
  | SERIALIZABLE 
  | TABLOCK 
  | TABLOCKX 
  | UPDLOCK 
  | XLOCK 
}

Arguments

  • 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 Notes.

  • INDEX ( index_val [ ,... n ] )
    Spécifie le nom ou l'ID des index qui sera utilisé par l'optimiseur de requête lors du traitement de l'instruction. Un seul indicateur d'index par table peut être spécifié.

    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.

    La syntaxe alternative INDEX = spécifie un seul indicateur d'index. Elle n'est prise en charge que pour la compatibilité descendante.

    Si plusieurs index sont utilisés dans une seule liste d'indicateurs, les doublons sont ignorés et les autres 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 l'ensemble des index affectés par l'indicateur n'est pas couvert, une extraction est effectuée une fois que le moteur de base de données SQL Server 2005 a récupéré toutes les colonnes indexées.

    ms187373.note(fr-fr,SQL.90).gifRemarque :
    Lorsqu'une option d'index faisant référence à plusieurs index est utilisée sur la table des 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 n'est pas autorisée pour une table avec une option d'index spécifiée.

    Le nombre maximal d'index 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 de départ et d'un incrément spécifié lors de la création de la table.

    ms187373.note(fr-fr,SQL.90).gifImportant :
    Si le fichier de données ne contient pas de valeurs pour la colonne identité de la table ou de la vue, vous devez ignorer cette colonne, sauf si elle est la dernière colonne de la table. Pour plus d'informations, consultez Utilisation d'un fichier de format pour ignorer un champ de données. Si une colonne identité est correctement ignorée, l'optimiseur de requête affecte automatiquement des valeurs uniques pour la colonne 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).

  • FASTFIRSTROW
    Équivalent à OPTION (FAST 1). Pour plus d'informations, consultez FAST dans la clause OPTION de SELECT.
  • 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.

    Il peut notamment convenir 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, notez que si les contraintes CHECK et FOREIGN KEY sont ignorées, une fois l'opération terminée, chaque contrainte ignorée sur la table est marquée comme is_not_trusted dans l'affichage catalogue sys.check_constraints ou sys.foreign_keys. À un point donné, vous devez vérifier les contraintes sur toute la table. 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.
  • NOWAIT
    Indique au moteur de base de données SQL Server 2005 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'isolation 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'isolement READ COMMITTED en utilisant le verrouillage ou la gestion des versions de ligne. Si l'option de base de données READ_COMITTED_SNAPSHOT a pour 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 SQL Server 2005 n'acquiert pas de verrous et utilise la gestion des versions de ligne. Pour plus d'informations sur les niveaux d'isolement, consultez SET TRANSACTION ISOLATION LEVEL (Transact-SQL).
  • READCOMMITTEDLOCK
    Spécifie que les opérations de lecture doivent respecter les règles du niveau d'isolement 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'isolement, consultez SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

  • READPAST
    Spécifie que le moteur de base de données ne doit pas lire les lignes qui sont verrouillées par d'autres transactions. Dans la plupart des cas, cette interdiction s'applique également aux pages. Tant que les verrous ne sont pas libérés, le moteur de base de données ignore les lignes ou les pages au lieu de bloquer la transaction actuelle. READPAST ne peut être spécifié que dans les transactions fonctionnant aux niveaux d'isolement READ COMMITTED ou REPEATABLE READ. Si cet argument est spécifié dans des transactions fonctionnant au niveau d'isolation SNAPSHOT, PAGLOCK doit être combiné avec d'autres indicateurs de table qui requièrent des verrous, tels que UPDLOCK et HOLDLOCK. Lorsque READPAST est spécifié, les verrous de niveau ligne et de niveau page sont ignorés. 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.

    Par exemple, supposons que la table T1 contient une colonne d'entiers uniques avec les valeurs 1, 2, 3, 4, 5. Si la transaction A modifie la valeur de 3 en 8 mais qu'elle ne l'a pas encore validée, l'instruction SELECT * FROM T1 (READPAST) obtient les valeurs 1, 2, 4, 5. READPAST sert essentiellement à réduire les conflits 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.

  • READUNCOMMITTED
    Indique que les lectures incorrectes 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 incorrectes peut accroître l'accès concurrentiel, mais au prix de la lecture de modifications de données qui sont ensuite restaurées par d'autres transactions. Ceci peut générer des erreurs pour votre transaction, ou les utilisateurs peuvent se trouver confrontés à des données qui n'ont jamais été validées.

    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 de stabilité de schéma (Sch-S) durant la compilation et l'exécution. De ce fait, les requêtes sont bloquées lorsqu'une transaction simultanée détient un verrou de modification de 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.

    ms187373.note(fr-fr,SQL.90).gifRemarque :
    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 de la prochaine version de Microsoft 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.

    Dans SQL Server 2005, vous pouvez limiter les conflits de verrouillage tout en protégeant les transactions de lectures incorrectes de modifications de données non validées en utilisant l'un des niveaux d'isolement suivants :

    • le niveau d'isolement 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'isolement, consultez SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

    ms187373.note(fr-fr,SQL.90).gifRemarque :
    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
    Effectue une recherche avec la même sémantique de verrouillage qu'une transaction à un niveau d'isolement REPEATABLE READ. Pour plus d'informations sur les niveaux d'isolement, 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'isolation SNAPSHOT, les verrous de ligne ne sont établis que si PAGLOCK 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'isolement SERIALIZABLE. Pour plus d'informations sur les niveaux d'isolement, consultez SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

  • TABLOCK
    Établit un verrou sur la table et le maintient jusqu'à la fin de l'instruction. Si des données sont en cours de lecture, un verrou partagé est établi. Si des données sont en cours de modification, un verrou exclusif est établi. Si l'option HOLDLOCK est également spécifiée, un verrou de table partagé est maintenu jusqu'à la fin de la transaction.

    Lorsqu'il est utilisé avec le fournisseur d'ensembles de lignes en bloc OPENROWSET pour importer des données dans une table dépourvue d'index, l'argument TABLOCK permet à plusieurs clients de charger simultanément les données dans la table cible avec optimisation de la consignation et du verrouillage.

  • TABLOCKX
    Spécifie qu'un verrou exclusif est établi sur la table jusqu'à ce que la transaction s'achève.
  • UPDLOCK
    Spécifie que les verrous de mise à jour doivent être établis et maintenus jusqu'à ce que la transaction s'achève.
  • 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).

L'utilisation de virgules entre les indicateurs de table est facultative mais encouragée. La séparation des indicateurs par des espaces plutôt que par des virgules est prise en charge pour la compatibilité descendante.

Dans SQL Server 2005, à l'exception de quelques cas, les indicateurs de table ne sont pris en charge dans la clause FROM que lorsqu'ils sont spécifiés avec le mot clé WITH. En outre, les indicateurs de table doivent être spécifiés avec des parenthèses.

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 (FASTFIRSTROW)

Lorsque l'indicateur est spécifié avec une autre option, il doit être défini avec le mot clé WITH :

FROM t WITH (FASTFIRSTROW, INDEX(myindex))

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

Dans SQL Server 2005, tous les indicateurs de verrou sont diffusés à toutes 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, ROWLOCK, TABLOCK, ou TABLOCKX ;
  • indicateurs de niveau d'isolement : HOLDLOCK, NOLOCK, READCOMMITTED, REPEATABLEREAD, SERIALIZABLE.

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 2005 éditions Entreprise et Developer.

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 2005 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.

Autorisations

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

Exemple

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.

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

Voir aussi

Référence

OPENROWSET (Transact-SQL)
Indicateurs (Transact-SQL)

Autres ressources

Indicateurs de verrouillage
Résolution de vues

Aide et Informations

Assistance sur SQL Server 2005

Historique des modifications

Version Historique

12 décembre 2006
Contenu modifié :
  • Clarification du type de verrou établi par l'option TABLOCK.
  • Révision de la description de l'option IGNORE_CONSTRAINTS pour indiquer que celle-ci entraîne l'ignorance des contraintes CHECK et FOREIGN KEY.

14 avril 2006
Nouveau contenu :
  • Ajout d'informations sur l'utilisation de PAGLOCK, READPAST et ROWLOCK dans les transactions fonctionnant au niveau d'isolation SNAPSHOT.

5 décembre 2005
Contenu modifié :
  • Les informations concernant l'indicateur de verrou READUNCOMMITTED ont été mises à jour.