Indicateurs de table (Transact-SQL)

Article
12/16/2024

S’applique à :SQL Server Azure SQL Database Azure SQL Managed Instance Base de données SQL dans Microsoft Fabric

Les indicateurs de table sont utilisés pour remplacer le comportement par défaut de l’optimiseur de requête pendant l’instruction DML (Data Manipulation Language). Vous pouvez spécifier une méthode de verrouillage, un ou plusieurs index, une opération de traitement des requêtes, comme 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.

Attention

É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 à ces conseils qu'en dernier ressort et seulement si vous êtes un développeur ou un administrateur de base de données expérimenté.

S’applique à :

Conventions de la syntaxe Transact-SQL

Syntaxe

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

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

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

Arguments

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

Avec certaines exceptions, les indicateurs de table sont pris en charge dans la clause FROM uniquement lorsque les indicateurs sont spécifiés avec le mot clé WITH. En outre, les indicateurs de table doivent être spécifiés avec des parenthèses.

Important

L’omission du mot clé WITH est une fonctionnalité déconseillée : cette fonctionnalité sera supprimée dans une prochaine version de 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é.

Les indicateurs de table suivants sont autorisés avec et sans le mot clé WITH : NOLOCK, READUNCOMMITTED, UPDLOCK, REPEATABLEREAD, SERIALIZABLE, READCOMMITTED, TABLOCK, TABLOCKX, PAGLOCK, ROWLOCK, NOWAIT, READPAST, XLOCK, SNAPSHOT et NOEXPAND. Quand ces indicateurs de table sont spécifiés sans le mot clé WITH, ils doivent être spécifiés seuls. Par exemple :

FROM t (TABLOCK)

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

FROM t WITH (TABLOCK, INDEX(myindex))

Nous vous recommandons d'utiliser des virgules entre les indicateurs de table.

Important

La séparation des indicateurs par des espaces plutôt que des virgules est une fonctionnalité déconseillée : cette fonctionnalité sera supprimée dans une prochaine version de 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é.

NOEXPAND

Spécifie que les vues indexées ne sont pas développées pour permettre l’accès aux tables sous-jacentes quand 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 seulement aux vues indexées. Pour plus d’informations, consultez Utiliser noEXPAND.

INDEX ( <index_value> [ , ...n ] ) | INDEX = ( <index_value> )

La syntaxe de INDEX() spécifie les noms ou les ID d’un ou plusieurs index qui seront utilisés par l’optimiseur de requête lors du traitement de l’instruction. L'autre syntaxe INDEX = spécifie une seule valeur d'index. 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.

Si plusieurs index sont utilisés dans une même liste d’indicateurs, les doublons sont ignorés et les autres index listé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. En outre, l’index ORing n’est pas autorisé pour une table avec un indicateur d’index spécifié.

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 d'identité. Si KEEPIDENTITY n’est pas spécifié, les valeurs d’identité de cette colonne sont vérifiées, mais non importées, et l’optimiseur de requête affecte automatiquement des valeurs uniques en fonction des valeurs initiales et incrémentées spécifiées 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é dans la table ou la vue, et si la colonne d’identité n’est pas la dernière colonne de la table, vous devez ignorer la colonne d’identité. Pour plus d’informations, consultez Utiliser un fichier de format pour ignorer un champ de données (SQL Server). Si une colonne d’identité est ignorée, l’optimiseur de requête assigne automatiquement des valeurs uniques pour la colonne d’identité dans les lignes de table importées.

Pour obtenir un exemple qui utilise cet indicateur dans une instruction INSERT ... SELECT * FROM OPENROWSET(BULK...), consultez Conserver les valeurs d’identité lors de l’importation en bloc de données (SQL Server).

Pour plus d’informations sur la vérification de la valeur d’identité d’une table, consultez DBCC CHECKIDENT.

KEEPDEFAULTS

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

Spécifie l’insertion de la valeur par défaut d’une colonne de table, le cas échéant, au lieu de NULL lorsque l’enregistrement de données n’a pas de valeur pour la colonne.

Pour obtenir un exemple qui utilise cet indicateur dans une instruction INSERT ... SELECT * FROM OPENROWSET(BULK...), consultez Conserver les valeurs null ou par défaut pendant l’importation en bloc (SQL Server).

FORCESEEK [ ( <index_value> ( <index_column_name> [ , ...n ] ) ) ]

Spécifie que l’optimiseur de requête doit utiliser seulement une opération de recherche d’index comme chemin d’accès aux données dans la table ou la vue.

Notes

À compter de SQL Server 2008 R2 (10.50.x) Service Pack 1, des paramètres d’index peuvent également être spécifiés. Dans ce cas, l'optimiseur de requête considère uniquement les opérations de recherche d'index par le biais de l'index spécifié utilisant au moins les colonnes d'index spécifiées.

index_value

Nom de l’index ou valeur d’ID de l’index. L’ID d’index 0 (segment) ne peut pas être spécifié. Pour retourner le nom ou l’ID de l’index, interrogez la vue de catalogue sys.indexes.
index_column_name

Nom de la colonne d’index à inclure dans l’opération de recherche. La spécification de FORCESEEK avec des paramètres d’index est similaire à l’utilisation de FORCESEEK avec un indicateur INDEX. Toutefois, vous pouvez obtenir un meilleur contrôle du chemin d'accès utilisé par l'optimiseur de requête en spécifiant l'index sur lequel effectuer la recherche et les colonnes d'index à prendre en compte dans l'opération de recherche. L’optimiseur peut prendre en compte davantage de colonnes si nécessaire. Par exemple, si un index non cluster est spécifié, l’optimiseur peut choisir d’utiliser des colonnes clés d’index en cluster en plus des colonnes spécifiées.

L’indicateur FORCESEEK peut être spécifié d’une des façons suivantes.

Syntaxe	Exemple	Description
Sans index ou indicateur `INDEX`	`FROM dbo.MyTable WITH (FORCESEEK)`	L'optimiseur de requête considère uniquement les opérations de recherche d'index pour accéder à la table ou la vue par le biais de tout index approprié.
Combiné avec un indicateur `INDEX`	`FROM dbo.MyTable WITH (FORCESEEK, INDEX (MyIndex))`	L'optimiseur de requête considère uniquement les opérations de recherche d'index pour accéder à la table ou la vue par le biais de l'index spécifié.
Paramétrable en spécifiant un index et des colonnes d'index	`FROM dbo.MyTable WITH (FORCESEEK (MyIndex (col1, col2, col3)))`	L'optimiseur de requête considère uniquement les opérations de recherche pour accéder à la table ou la vue par le biais de l'index spécifié utilisant au moins les colonnes d'index spécifiées.

Quand vous utilisez l’indicateur FORCESEEK (avec ou sans paramètres d’index), prenez en compte les recommandations suivantes :

L'indicateur peut être spécifié comme indicateur de table ou 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é.
L'indicateur peut être appliqué au plus une fois par table ou vue.
L’indicateur ne peut pas être spécifié pour une source de données distante. L’erreur 7377 est retournée quand FORCESEEK est spécifié avec un indicateur d’index, et l’erreur 8180 est retournée quand FORCESEEK est utilisé sans indicateur d’index.
Si FORCESEEK entraîne la détection d’aucun plan, l’erreur 8622 est retournée.

Quand 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é pour une table qui est la cible d’une instruction INSERT, UPDATEou DELETE.
L’indicateur ne peut pas être spécifié en association 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 première colonne principale.
Des colonnes d’index supplémentaires peuvent être spécifiées, mais les colonnes clés ne peuvent pas être ignorées. Par exemple, si l'index spécifié contient les colonnes clés a, b, et c, la syntaxe valide inclurait FORCESEEK (MyIndex (a)) et FORCESEEK (MyIndex (a, b). La syntaxe incorrecte inclurait FORCESEEK (MyIndex (c)) et FORCESEEK (MyIndex (a, c).
L'ordre des noms de colonnes spécifiés dans l'indicateur doit correspondre à l'ordre des colonnes dans l'index référencé.
Les colonnes qui ne sont pas dans la définition de la 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 en cluster qui sont automatiquement incluses dans l’index ne peuvent pas être spécifiées, mais peuvent être utilisées par l’optimiseur.
Un index columnstore à mémoire optimisée xVelocity ne peut pas être spécifié comme paramètre d’index. L’erreur 366 est retournée.
La modification de la définition d’index (par exemple, en ajoutant ou en supprimant des colonnes) peut nécessiter des modifications apportées aux requêtes qui référencent cet index.
L'indicateur empêche l'optimiseur de considérer tout index spatial ou XML sur la table.
L’indicateur ne peut pas être spécifié en combinaison avec l’indicateur FORCESCAN.
Pour les index partitionnés, la colonne de partitionnement implicitement ajoutée par SQL Server ne peut pas être spécifiée dans l’indicateur FORCESEEK.

Attention

Le fait de spécifier FORCESEEK avec des paramètres limite davantage le nombre de plans qui peuvent être pris en compte par l’optimiseur que le fait de spécifier FORCESEEK sans paramètres. Cela peut entraîner une erreur de Plan cannot be generated dans d’autres cas.

FORCESCAN

S’applique à : SQL Server 2008 R2 (10.50.x) Service Pack 1 et ultérieur.

Indique que l’optimiseur de requête doit utiliser seulement 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 pour les requêtes dans lesquelles l’optimiseur sous-estime le nombre de lignes affectées et choisit une opération de recherche au lieu d’une opération d’analyse. Dans ce cas, la quantité de mémoire allouée pour l’opération est trop faible, ce qui affecte les performances de la requête.

FORCESCAN peut être spécifié avec ou sans indicateur INDEX. Lorsqu’il est combiné à un indicateur d’index (INDEX = index_name, FORCESCAN), l’optimiseur de requête considère uniquement 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) pour forcer une opération d’analyse de table sur la table de base.

Pour les tables et index partitionnés, FORCESCAN est appliqué après l’élimination des partitions par le biais de l’évaluation du prédicat de requête. Cela signifie que l'analyse est appliquée uniquement aux partitions restantes, et non à la table entière.

L’option FORCESCAN est soumise aux restrictions suivantes :

L’indicateur ne peut pas être spécifié pour une table qui est la cible d’une instruction INSERT, UPDATEou DELETE.
L’indicateur ne peut pas être utilisé avec plusieurs indicateurs d’index.
L’indicateur empêche l’optimiseur de requête de prendre en compte les index spatiaux ou XML sur la table.
L’indicateur ne peut pas être spécifié pour une source de données distante.
L’indicateur ne peut pas être spécifié en combinaison avec l’indicateur FORCESEEK.

HOLDLOCK

Équivaut à SERIALIZABLE. Pour plus d’informations, consultez SERIALIZABLE plus loin dans cet article. 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 inclut l’option FOR BROWSE.

IGNORE_CONSTRAINTS

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

Spécifie que l’opération d’importation en bloc ignore toutes les contraintes sur la table. Par défaut, INSERT vérifie contraintes uniques et les contraintes de vérification et contraintes de clé primaire et étrangère. Lorsque IGNORE_CONSTRAINTS est spécifié pour une opération d’importation en bloc, INSERT devez ignorer ces contraintes sur une table cible. Vous ne pouvez pas désactiver les contraintes UNIQUE, PRIMARY KEYou NOT NULL.

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

Toutefois, lorsque des contraintes CHECK et FOREIGN KEY sont ignorées, chaque contrainte ignorée sur la table est marquée comme is_not_trusted dans l’affichage sys.check_constraints ou sys.foreign_keys catalogue après l’opération. À 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, le coût de revalidation de la contrainte peut dépasser le coût d’application de CHECK et de contraintes 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 des déclencheurs.

Utilisez IGNORE_TRIGGERS uniquement si votre application ne dépend pas des déclencheurs et si l’optimisation des performances est importante.

NOLOCK

Équivaut à READUNCOMMITTED. Pour plus d’informations, consultez READUNCOMMITTED plus loin dans cet article.

Notes

Pour UPDATE ou DELETE instructions : cette fonctionnalité sera supprimée dans une prochaine version de 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. L’indicateur NOWAIT ne fonctionne pas quand l’indicateur TABLOCK est également inclus. Pour terminer une requête sans délai en cas d’utilisation de l’indicateur TABLOCK, faites à la place précéder la requête de SET LOCK_TIMEOUT 0;.

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 indicateur est spécifié dans des transactions fonctionnant au niveau d’isolement SNAPSHOT, les verrous de page sont pris seulement si PAGLOCK est combiné avec d’autres indicateurs de table qui nécessitent des verrous, comme UPDLOCK et HOLDLOCK.

READCOMMITTED

Spécifie que les opérations de lecture respectent les règles du niveau d’isolation READ COMMITTED à l’aide du verrouillage ou du contrôle de version de ligne. Si l’option de base de données READ_COMMITTED_SNAPSHOT est 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 terminée. Si l’option de base de données READ_COMMITTED_SNAPSHOT est ON, le moteur de base de données n’acquiert pas de verrous et utilise le contrôle de version des lignes. Pour plus d’informations sur les niveaux d’isolation, consultez SET TRANSACTION ISOLATION LEVEL.

Notes

READCOMMITTEDLOCK

Spécifie que les opérations de lecture respectent les règles du niveau d’isolation READ COMMITTED à l’aide du verrouillage. Le moteur de base de données acquiert des verrous partagés quand les données sont lues et libère ces verrous quand 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. 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 le Moteur de base de données ne doit pas lire les lignes qui sont verrouillées par d'autres transactions. Quand READPAST est spécifié, les verrous de niveau ligne sont ignorés, mais les verrous de niveau page ne le sont pas. C’est-à-dire, 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) produit les valeurs 1, 2, 4, 5. READPAST est utilise principalement pour réduire la contention du verrouillage lors de l’implémentation d’une file d’attente de travaux 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 pouvez être spécifié pour n’importe quelle table référencée dans une instruction UPDATE ou DELETE, et toute table référencée dans une clause FROM. Lorsqu’elle est spécifiée dans une instruction UPDATE, READPAST est appliquée uniquement lors de la lecture de données pour identifier les enregistrements à mettre à jour, quel que soit l’emplacement dans l’instruction spécifié. READPAST ne peut pas être spécifié pour les tables dans la clause INTO d’une instruction INSERT. Les opérations de mise à jour ou de suppression qui utilisent READPAST peuvent bloquer lors de la lecture de clés étrangères ou d’affichages indexés, ou lors de la modification d’index secondaires.

READPAST ne peut être spécifié que dans les transactions qui fonctionnent aux niveaux d’isolation READ COMMITTED ou REPEATABLE READ. Si cet indicateur est spécifié dans des transactions fonctionnant au niveau d’isolement SNAPSHOT, READPAST doit être combiné avec d’autres indicateurs de table qui nécessitent des verrous, comme UPDLOCK et HOLDLOCK.

L’indicateur de table READPAST ne peut pas être spécifié lorsque l’option de base de données READ_COMMITTED_SNAPSHOT est définie sur ON et que l’une des conditions suivantes est remplie :

Le niveau d’isolation des transactions de la session est READ COMMITTED.
L’indicateur de table READCOMMITTED 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 incluez 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, présenter des utilisateurs avec des données qui n’ont jamais été validées ou provoquer la présence d’enregistrements deux fois (ou pas du tout).

Les indicateurs READUNCOMMITTED et NOLOCK s’appliquent seulement aux verrous de données. Toutes les requêtes, y compris les requêtes avec des indicateurs de READUNCOMMITTED et de NOLOCK, acquièrent des verrous Sch-S (stabilité du schéma) pendant 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 les requêtes exécutées avec des indicateurs de READUNCOMMITTED ou de NOLOCK, sont bloquées lors de la tentative d’acquisition 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.

Vous ne pouvez pas 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 de READUNCOMMITTED et de NOLOCK dans la clause FROM qui s’appliquent à la table cible d’une instruction UPDATE ou DELETE.

Notes

Prise en charge de l’utilisation des indicateurs de READUNCOMMITTED et de NOLOCK dans la clause FROM qui s’appliquent à la table cible d’une instruction UPDATE ou DELETE sera supprimée dans une version ultérieure 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 la contention de verrouillage tout en protégeant les transactions contre les lectures incorrectes des modifications de données non validées à l’aide de l’une des options suivantes :

Niveau d’isolation READ COMMITTED avec l’option de base de données READ_COMMITTED_SNAPSHOT définie ON.
Le niveau d’isolement SNAPSHOT.

Pour plus d’informations sur les niveaux d’isolation, consultez SET TRANSACTION ISOLATION LEVEL.

Notes

Si vous recevez message d’erreur 601 lorsque READUNCOMMITTED est spécifié, résolvez-le comme vous le feriez pour une erreur d’interblocage (message d’erreur 1205), puis réessayez votre instruction.

REPEATABLEREAD

Spécifie qu’une analyse est effectuée avec la même sémantique de verrouillage qu’une transaction s’exécutant au niveau d’isolation REPEATABLE READ. Pour plus d’informations sur les niveaux d’isolation, consultez SET TRANSACTION ISOLATION LEVEL.

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 indicateur est spécifié dans des transactions fonctionnant au niveau d’isolement SNAPSHOT, les verrous de ligne sont pris seulement si ROWLOCK est combiné avec d’autres indicateurs de table qui nécessitent des verrous, comme UPDLOCK et HOLDLOCK. ROWLOCK ne peut pas être utilisé avec une table qui a un index columnstore cluster. L’exemple suivant retourne une erreur 651 à l’application.

UPDATE [dbo].[FactResellerSalesXL_CCI] WITH (ROWLOCK)
    SET UnitPrice = 50
WHERE ProductKey = 150;

SERIALIZABLE

Équivaut à 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. L’analyse est effectuée avec la même sémantique qu’une transaction s’exécutant au niveau d’isolement SERIALIZABLE. Pour plus d’informations sur les niveaux d’isolation, consultez SET TRANSACTION ISOLATION LEVEL.

SNAPSHOT

S’applique à : SQL Server 2014 (12.x) et ultérieur

La table à mémoire optimisée est accédée selon l’isolement SNAPSHOT. SNAPSHOT peut être utilisé seulement avec des table à mémoire optimisée (et non pas avec des tables sur disque), comme le montre l’exemple suivant. Pour plus d’informations, consultez Introduction aux tables à mémoire optimisée.

SELECT *
FROM dbo.Customers AS c WITH (SNAPSHOT)
     LEFT OUTER JOIN dbo.[Order History] AS oh
         ON c.customer_id = oh.customer_id;

SPATIAL_WINDOW_MAX_CELLS = <integer_value>

S’applique à : SQL Server 2012 (11.x) et ultérieur

Spécifie le nombre maximal de cellules à utiliser pour le pavage d'un objet géométrique ou géographique. <integer_value> est un nombre entier compris entre 1 et 8192.

Cette option permet de paramétrer précisément l'heure d'exécution de la requête en ajustant le compromis entre la durée d'exécution du filtre primaire et du filtre secondaire. Un nombre élevé réduit la durée d'exécution du filtre secondaire, mais augmente celle du filtre de l'exécution primaire, tandis qu'un nombre plus petit décroît la durée d'exécution du filtre primaire, mais augmente celle de l'exécution du filtre secondaire. Avec des données spatiales plus denses, un nombre élevé doit aboutir à une durée d'exécution plus rapide en donnant une meilleure approximation avec le filtre primaire et en réduisant la durée d'exécution du filtre secondaire. Avec des données plus éparses, un nombre inférieur diminue la durée d’exécution du filtre principal.

Cette option fonctionne à la fois pour les pavages de grille manuels et automatiques.

TABLOCK

Spécifie que le verrou acquis est appliqué au niveau de la table. Le type de verrou 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é à la table entière au lieu de l’être 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.

Quand vous importez des données dans un segment de mémoire en utilisant l’instruction INSERT INTO <target_table> SELECT <columns> FROM <source_table>, vous pouvez activer la journalisation minimale et le verrouillage optimisé pour l’instruction en spécifiant le conseil 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. L’indicateur TABLOCK permet également les insertions parallèles dans les segments de mémoire ou les index columnstore cluster. Pour plus d’informations, consultez insert.

Quand il est utilisé avec le fournisseur d’ensembles de lignes OPENROWSET pour importer des données dans une table, TABLOCK permet à plusieurs clients de charger en même temps des données dans la table cible avec une optimisation de la journalisation et du verrouillage. Pour plus d’informations, consultez Conditions préalables pour une journalisation minimale dans lesd’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 prend des verrous de mise à jour pour les opérations de lecture seulement au niveau de la ligne ou de la page. Si UPDLOCK est combiné à TABLOCK, ou si un verrou de niveau table est pris pour une raison quelconque, un verrou exclusif (X) est pris à la place.

Quand 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 SERIALIZABLE et qu’une requête spécifie (UPDLOCK, READCOMMITTED), l’indicateur READCOMMITTED est ignoré et la transaction est exécutée avec le 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, PAGLOCKou TABLOCK est spécifiée, les verrous exclusifs s’appliquent au niveau de granularité approprié.

Notes

Les indicateurs de table sont ignorés si le plan de requête n’accède pas à la table. Cela peut être dû au fait que l’optimiseur choisit de ne pas accéder à la table du tout, ou parce qu’une vue indexée est accessible à la place. Dans ce dernier cas, l’accès à une vue indexée peut être empêché à 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, UPDLOCKet XLOCK qui acquièrent des verrous au niveau des lignes peuvent placer des verrous sur des clés d’index plutôt que sur les lignes de données réelles. Par exemple, si une table a un index non cluster et qu’une instruction SELECT à l’aide d’un indicateur de verrou est gérée par un index de couverture, un verrou est acquis sur la clé d’index dans l’index de couverture plutôt que sur la ligne de données de la table de base.

Si une table contient des colonnes calculées qui sont traitées par des expressions ou des fonctions accédant à des colonnes dans d’autres tables, les indicateurs de table ne sont pas utilisés sur ces dernières et ne sont pas propagés. Par exemple, un indicateur de table NOLOCK est spécifié sur 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 quand elles font l’objet d’un accès.

SQL Server n’autorise pas plusieurs indicateurs de table de chacun des groupes suivants pour chaque table dans la clause FROM :

Indicateurs de granularité : PAGLOCK, NOLOCK, READCOMMITTEDLOCK, ROWLOCK, TABLOCK ou TABLOCKX.
Indicateurs de niveau d’isolement : HOLDLOCK, NOLOCK, READCOMMITTED, REPEATABLEREAD, SERIALIZABLE.

Indicateurs d’index filtré

Un index filtré peut être utilisé comme indicateur de table, mais provoque la génération de l’optimiseur de requête 8622 s’il ne couvre pas toutes les lignes sélectionnées par la requête. Vous trouverez ci-dessous un exemple d'indicateur d'index filtré non valide. L’exemple crée l’index filtré FIBillOfMaterialsWithComponentID, puis l’utilise 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 Créer des index filtrés.

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 prend pas en compte un indicateur d’index si les options de SET n’ont pas les valeurs requises pour les index filtrés. Pour plus d’informations, consultez CREATE INDEX.

Utiliser NOEXPAND

NOEXPAND s’applique seulement 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 fonctionnalité est appelée correspondance de vue indexée. Avant SQL Server 2016 (13.x) avec Service Pack 1, l’utilisation automatique d’une vue indexée par l’optimiseur de requête est prise en charge uniquement dans des éditions spécifiques de SQL Server. Dans SQL Server 2016 (13.x) avec Service Pack 1 et versions ultérieures, toutes les éditions prennent en charge l’utilisation automatique d’une vue indexée. Azure SQL Database et Azure SQL Managed Instance prennent également en charge l’utilisation automatique de vues indexées sans spécification de l’indicateur NOEXPAND.

Pour plus d’informations, consultez le Guide d’architecture de traitement des requêtes.

Pour obtenir la liste des fonctionnalités prises en charge par les éditions de SQL Server sur Windows, consultez :

Toutefois, pour que l’optimiseur de requête considère les vues indexées pour la correspondance, ou utilisez une vue indexée référencée avec l’indicateur de NOEXPAND, les options de SET suivantes doivent être définies sur ON.

ANSI_NULLS
ANSI_PADDING
ANSI_WARNINGS
ARITHABORT ¹
CONCAT_NULL_YIELDS_NULL
QUOTED_IDENTIFIER

¹ARITHABORT est implicitement définie sur ON lorsque ANSI_WARNINGS est définie sur ON. Par conséquent, vous n’avez pas besoin d’ajuster ce paramètre manuellement.

En outre, l’option NUMERIC_ROUNDABORT doit être définie sur OFF.

Pour forcer l’optimiseur de requête à 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 forcer l’utilisation d’une vue indexée particulière dans une requête qui ne nomme pas la vue directement dans la clause FROM. Cependant, l’optimiseur de requête prend en considération l’utilisation de vues indexées même si elles ne sont pas référencées directement dans la requête. Le moteur de base de données SQL Server crée uniquement des statistiques sur une vue indexée lorsqu’un indicateur de table NOEXPAND est utilisé. L’omission de cet indicateur peut entraîner l’affichage d’avertissements du plan d’exécution concernant des statistiques manquantes, ce qui ne peut pas être résolu en créant des statistiques manuellement.

Lors de l’optimisation d’une requête, le moteur de base de données utilise les statistiques de la vue qui ont été créées automatiquement ou manuellement quand la requête référence directement la vue et que l’indicateur NOEXPAND est utilisé.

Utiliser un indicateur de table comme indicateur de requête

indicateurs de table peuvent également être spécifiés en tant qu’indicateur de requête à l’aide de la clause OPTION (TABLE HINT). Nous vous 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.

Autorisations

Les indicateurs KEEPIDENTITY, IGNORE_CONSTRAINTSet IGNORE_TRIGGERS nécessitent des autorisations ALTER sur la table.

Exemples

R. Utiliser l’indicateur TABLOCK pour spécifier une méthode de verrouillage

L’exemple suivant spécifie qu’un verrou partagé est pris sur la table Production.Product de la base de données AdventureWorks2022 et qu’il est conservé jusqu’à la fin de l’instruction UPDATE.

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

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

L’exemple suivant 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 de la base de données AdventureWorks2022.

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

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. Utiliser l’indicateur FORCESCAN pour spécifier une opération d’analyse d’index

L’exemple suivant utilise l’indicateur FORCESCAN pour forcer l’optimiseur de requête à effectuer une opération d’analyse sur la table Sales.SalesOrderDetail de la base de données AdventureWorks2022.

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);

Partager via

Indicateurs de table (Transact-SQL)

Syntaxe

Arguments

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

NOEXPAND

INDEX ( <index_value> [ , ...n ] ) | INDEX = ( <index_value> )

KEEPIDENTITY

KEEPDEFAULTS

FORCESEEK [ ( <index_value> ( <index_column_name> [ , ...n ] ) ) ]

FORCESCAN

HOLDLOCK

IGNORE_CONSTRAINTS

IGNORE_TRIGGERS

NOLOCK

NOWAIT

PAGLOCK

READCOMMITTED

READCOMMITTEDLOCK

READPAST

READUNCOMMITTED

REPEATABLEREAD

ROWLOCK

SERIALIZABLE

SNAPSHOT

SPATIAL_WINDOW_MAX_CELLS = <integer_value>

TABLOCK

TABLOCKX

UPDLOCK

XLOCK

Notes

Indicateurs d’index filtré

Utiliser NOEXPAND

Utiliser un indicateur de table comme indicateur de requête

Autorisations

Exemples

R. Utiliser l’indicateur TABLOCK pour spécifier une méthode de verrouillage

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

C. Utiliser l’indicateur FORCESCAN pour spécifier une opération d’analyse d’index

Contenu connexe

Commentaires

Ressources supplémentaires