Partage via

Indicateurs de table (Transact-SQL)

  • Article

S’applique à : SQL Server Azure SQL Database Azure SQL Managed Instance

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 n'affectent que la table ou 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 ]

À quelques exceptions près, les indicateurs de table sont pris en charge dans la clause FROM uniquement lorsque les indicateurs sont spécifiés à l'aide du 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, il doit être défini 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 Utilisation de 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. 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 seulement dans une instruction INSERT quand 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 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 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 des valeurs d’identité lors de l’importation de données en bloc (SQL Server).

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

KEEPDEFAULTS

Applicable seulement dans une instruction INSERT quand 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 * FROM OPENROWSET(BULK...), consultez Conserver les valeurs NULL ou utiliser les valeurs par défaut lors de 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 des colonnes supplémentaires 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 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 fait qu’aucun un plan n’est trouvé, 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, UPDATE ou 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 cluster qui sont automatiquement incluses dans l’index ne peuvent pas être spécifiées, mais elles 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 imposer des modifications des requêtes qui font référence à 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 provoquer une erreur Plan cannot be generated dans plusieurs cas. Dans une version ultérieure, il se peut que des modifications internes de l’optimiseur de requête autorisent la prise en considération de davantage de plans.

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. Quand il est combiné avec un indicateur d’index (INDEX = index_name, FORCESCAN), l’optimiseur de requête prend en compte seulement les chemins d’accès d’analyse dans 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 les index partitionnés, FORCESCAN est appliqué après que les partitions ont été éliminées via l’évaluation des prédicats de la 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, UPDATE ou 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. L’option HOLDLOCK s’applique seulement à la table ou à la vue pour laquelle elle est spécifiée, et seulement pour la durée de la transaction définie par l’instruction où elle est utilisée. HOLDLOCK ne peut pas être utilisée dans une instruction SELECT qui inclut l’option FOR BROWSE.

IGNORE_CONSTRAINTS

Applicable seulement dans une instruction INSERT quand 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 uniques et contraintes de validation, ainsi que les contraintes de clé primaire et de clé étrangère. Lorsque IGNORE_CONSTRAINTS est spécifié pour une opération d'importation en bloc, INSERT doit ignorer ces contraintes sur une table cible. 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 les contraintes CHECK et FOREIGN KEY, vous pouvez importer les données, puis utiliser des instructions Transact-SQL pour les nettoyer.

Toutefois, quand les 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, 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, le coût de la revalidation de la contrainte peut être supérieur au coût de l’application des contraintes CHECK et FOREIGN KEY aux données incrémentielles.

IGNORE_TRIGGERS

Applicable seulement dans une instruction INSERT quand 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 seulement si votre application ne dépend d’aucun déclencheur et que l’optimisation des performances est importante.

NOLOCK

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

Remarque

Pour les instructions UPDATE ou DELETE : cette fonctionnalité sera supprimée dans une version ultérieure 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 doivent respecter les règles du niveau d'isolation READ COMMITTED en utilisant le verrouillage ou le contrôle de version 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 quand les données sont lues et libère ces verrous quand l’opération de lecture est achevée. Si l’option de base de données READ_COMMITTED_SNAPSHOT a la valeur ON, le moteur de base de données n’acquiert pas de verrous et utilise le contrôle de version de ligne. Pour plus d’informations sur les niveaux d’isolation, consultez SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

Remarque

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

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 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 (Transact-SQL). Cet indicateur ne peut pas être spécifié sur la table cible d’une instruction INSERT ; sinon, 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 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. Quand il est spécifié dans une instruction UPDATE et quel que soit l’emplacement auquel il est défini dans celle-ci, l’indicateur READPAST est appliqué seulement quand 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 mise à jour ou de suppression qui utilisent READPAST peuvent se bloquer quand elles lisent des clés étrangères ou des vues indexées, ou quand elles modifient des index secondaires.

READPAST peut être spécifié seulement dans les transactions fonctionnant aux niveaux d’isolement 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é quand l’option de base de données READ_COMMITTED_SNAPSHOT est définie sur ON et que l’une des conditions suivantes est vraie :

  • 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, 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).

Les indicateurs READUNCOMMITTED et NOLOCK s’appliquent seulement aux verrous de données. Toutes les requêtes, y compris celles avec les indicateurs READUNCOMMITTED et NOLOCK, obtiennent des verrous Sch-S (Stabilité du schéma) lors de la compilation et de 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.

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 de 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 la contention 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 définie sur 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 quand READUNCOMMITTED est spécifié, résolvez-le comme vous le feriez pour une erreur de blocage (message d’erreur 1205) et réessayez 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 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 (Transact-SQL).

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 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 (Transact-SQL).

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 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 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. 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 au lieu des 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 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 de 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 va provoquer la génération par l’optimiseur de requête de l’erreur 8622 s’il 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 Create Filtered Indexes.

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

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) Service Pack 1, l’utilisation automatique d’une vue indexée par l’optimiseur de requête est prise en charge seulement dans certaines éditions de SQL Server. Depuis, 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 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 être définies sur ON.

  • ANSI_NULLS
  • ANSI_PADDING
  • ANSI_WARNINGS
  • ARITHABORT 1
  • CONCAT_NULL_YIELDS_NULL
  • QUOTED_IDENTIFIER

1 ARITHABORT a implicitement la valeur ON quand ANSI_WARNINGS a la 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 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 directement la vue 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 automatiquement des statistiques sur une vue indexée seulement quand 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

Un indicateur de table peut également être spécifié comme indicateur de requête avec 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 (Transact-SQL).

Autorisations

Les indicateurs KEEPIDENTITY, IGNORE_CONSTRAINTS et IGNORE_TRIGGERS requièrent 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 établi sur la table Production.Product de la base de données AdventureWorks2022 et maintenu 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);

Contenu connexe