Partager via

  • Article

Création d'index sur des colonnes calculées

Vous pouvez définir des index sur des colonnes calculées si les règles suivantes sont respectées :

  • Conditions requises liées à la propriété

  • Conditions requises liées au déterminisme

  • Conditions requises liées à la précision

  • Conditions requises liées aux types de données

  • Conditions requises liées à l'option SET

Conditions requises liées à la propriété

Toutes les références de fonctions dans la colonne calculée doivent avoir le même propriétaire que la table.

Conditions requises liées au déterminisme

Une expression est déterministe lorsqu'elle retourne toujours le même résultat pour un ensemble donné d'entrées. La propriété IsDeterministic de la fonction COLUMNPROPERTY indique si un paramètre computed_column_expression est déterministe ou non.

  • Le paramètre computed_column_expression doit être déterministe. Un paramètre computed_column_expression est déterministe lorsqu'une ou plusieurs des conditions suivantes sont réunies :

    • Toutes les fonctions référencées par l'expression sont déterministes et précises, notamment les fonctions définies par l'utilisateur et les fonctions intégrées. Pour plus d'informations, consultez Fonctions déterministes et non déterministes. Les fonctions peuvent être imprécises si la colonne calculée est de type PERSISTED. Pour plus d'informations, consultez Création d'index sur des colonnes calculées persistantes, plus loin dans cette rubrique.

    • Toutes les colonnes auxquelles l'expression fait référence proviennent de la table contenant la colonne calculée.

    • Aucune référence de colonne n'extrait de données provenant de plusieurs lignes. Par exemple, des fonctions d'agrégation, telles que SUM ou AVG, dépendent de données provenant de plusieurs lignes et rendraient un paramètre computed_column_expression non déterministe.

    • Pas d'accès aux données système ni utilisateur.

  • Une colonne calculée qui contient une expression CLR (Common Language Runtime) doit être déterministe et marquée comme PERSISTED avant de pouvoir être indexée. Les expressions de type CLR définies par l'utilisateur sont permises dans les définitions de colonnes calculées. Les colonnes calculées de type CLR définies par l'utilisateur peuvent être indexées à condition que le type soit comparable. Pour plus d'informations, consultez Types CLR définis par l'utilisateur.

[!REMARQUE]

Lorsque vous faites référence aux littéraux de chaîne de type de données de date dans des colonnes calculées indexées dans SQL Server, il est recommandé de convertir explicitement le littéral vers le type de date souhaité à l'aide d'un style de format de date déterministe. Pour une liste dont les styles de formats de date sont déterministes, consultez CAST et CONVERT. Les expressions qui impliquent une conversion implicite des chaînes de caractères en type de données de date sont considérées comme non déterministes, à moins que le niveau de compatibilité de la base de données ne soit réglé sur 80 ou sur une valeur inférieure. Ceci est dû au fait que les résultats dépendent des paramètres LANGUAGE et DATEFORMAT en vigueur pendant la session serveur. Par exemple, les résultats de l'expression CONVERT (datetime, '30 listopad 1996', 113) dépendent du paramètre LANGUAGE parce que la chaîne « 30 listopad 1996 » signifie des mois différents dans des langues différentes. De même, dans l'expression DATEADD(mm,3,'2000-12-01'), le moteur de base de données interprète la chaîne '2000-12-01' en se basant sur le paramètre DATEFORMAT.

La conversion implicite de données de caractères non Unicode entre les classements est aussi considérée comme non déterministe, à moins que le niveau de compatibilité soit égal ou inférieur à 80.

Lorsque le niveau de compatibilité de la base de données est égal à 90, vous ne pouvez pas créer d'index sur les colonnes calculées qui contiennent ces expressions. Cependant, les colonnes calculées existantes qui contiennent ces expressions provenant d'une base de données mise à niveau sont gérables. Si vous utilisez des colonnes calculées indexées contenant une chaîne implicite pour les conversions de dates, afin d'éviter tout endommagement éventuel d'index, assurez-vous que les paramètres LANGUAGE et DATEFORMAT sont cohérents dans vos bases de données et applications.

Conditions requises liées à la précision

Le paramètre computed_column_expression doit être précis. Un paramètre computed_column_expression est précis lorsqu'une ou plusieurs des conditions suivantes sont réunies :

  • Ce n'est pas une expression composée de données de type float ou real.

  • L'expression n'utilise pas de données de type float ou real dans sa définition. Par exemple, dans l'instruction suivante, la colonne y est de type int et déterministe, mais pas précise.

    CREATE TABLE t2 (a int, b int, c int, x float, 
   y AS CASE x 
         WHEN 0 THEN a 
         WHEN 1 THEN b 
         ELSE c 
      END)

[!REMARQUE]

Toute expression float ou real est considérée comme non précise et ne peut pas être une clé d'index. Autrement dit, elle peut être utilisée dans une vue indexée mais pas en tant que clé. Ce point s'applique également aux colonnes calculées. Toute fonction, expression ou fonction définie par l'utilisateur est considérée imprécise si elle contient des expressions float ou real, même logiques (comparaisons).

La propriété IsPrecise de la fonction COLUMNPROPERTY indique si un paramètre computed_column_expression est précis ou non.

Conditions requises liées aux types de données

  • Le paramètre computed_column_expression défini pour la colonne calculée ne peut pas correspondre aux types de données text, ntext ou image.

  • Les colonnes calculées dérivées des types de données image, ntext, text, varchar(max), nvarchar(max), varbinary(max) et xml peuvent être indexées tant que le type de données utilisé dans la colonne calculée lui permet d'être une colonne d'index clés.

  • Les colonnes calculées dérivées des types de données image, ntext et text peuvent être des colonnes (incluses) non-clés dans un index non-cluster tant que le type de données utilisé dans la colonne calculée lui permet d'être une colonne d'index non-clés.

Conditions requises liées à l'option SET

  • L'option de niveau de connexion ANSI_NULLS doit être activée (ON) lors de l'exécution de l'instruction CREATE TABLE ou ALTER TABLE qui définit la colonne calculée. La fonction OBJECTPROPERTY indique si l'option est activée par le biais de la propriété IsAnsiNullsOn.

  • La connexion sur laquelle l'index est créé, ainsi que toutes les connexions tentant d'exécuter des instructions INSERT, UPDATE ou DELETE appelées à modifier des valeurs de l'index, doivent comporter six options SET activées (ON) et une désactivée (OFF). L'optimiseur ignore un index sur une colonne calculée dès qu'une instruction SELECT est exécutée par une connexion qui ne respecte pas ces paramètres d'options.

    • L'option NUMERIC_ROUNDABORT doit être désactivée (OFF) et les options suivantes doivent être activées (ON) :

    • ANSI_NULLS

    • ANSI_PADDING

    • ANSI_WARNINGS

    • ARITHABORT

    • CONCAT_NULL_YIELDS_NULL

    • QUOTED_IDENTIFIER

    L'affectation de la valeur ON à ANSI_WARNINGS affecte de manière implicite la valeur ON à ARITHABORT lorsque le niveau de compatibilité de la base de données est défini à 90. Si le niveau de compatibilité de la base de données est défini à 80 ou moins, la valeur ON doit être affectée de manière explicite à l'option ARITHABORT. Pour plus d'informations, consultez Options SET affectant les résultats.

Création d'index sur des colonnes calculées persistantes

Vous pouvez créer un index sur une colonne calculée définie par une expression déterministe mais non précise si la colonne est marquée comme PERSISTED dans l'instruction CREATE TABLE ou ALTER TABLE. Cela signifie que le moteur de base de données stocke les valeurs calculées dans la table et qu'il les met à jour lorsque les autres colonnes dont dépendent les colonnes calculées sont mises à jour. Le moteur de base de données utilise ces valeurs persistantes pour créer un index sur la colonne et lorsqu'une requête fait référence à l'index. Cette option vous permet de créer un index sur une colonne calculée lorsque le Moteur de base de données ne peut pas prouver avec certitude qu'une fonction qui retourne des expressions de colonnes calculées, en particulier une fonction CLR créée dans .NET Framework, est à la fois déterministe et précise.