Index sur les colonnes calculées

  • Article

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

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

Note

SET QUOTED_IDENTIFIER doit être ON lorsque vous créez ou modifiez des index sur des colonnes calculées ou des vues indexées. Pour plus d’informations, consultez SET QUOTED_IDENTIFIER (Transact-SQL).

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 IsDeterministic propriété de la fonction COLUMNPROPERTY indique si un computed_column_expression est déterministe.

Le paramètre computed_column_expression doit être déterministe. Une expression_de_colonne_calculée est déterministe quand toutes les conditions suivantes sont vraies :

  • 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 PERSISTED. Pour plus d’informations, consultez Créer des index sur des colonnes calculées persistantes en fin d’article.

  • 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, les fonctions d’agrégation telles que SUM ou AVG dépendent des données de plusieurs lignes et rendent un computed_column_expression non déterministe.

  • Le paramètre computed_column_expression n’a pas d’accès aux données système ni utilisateur.

Toute colonne calculée qui contient une expression CLR (Common Language Runtime) doit être déterministe et marquée PERSISTED avant que la colonne puisse ê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.

CAST et CONVERT

Lorsque vous faites référence à des littéraux de chaîne du type de données date dans les colonnes calculées indexées dans SQL Server, nous vous recommandons de convertir explicitement le littéral en type de date souhaité à l’aide d’un style de format de date déterministe. Pour obtenir la liste des styles de formats de date déterministes, consultez CAST et CONVERT.

Pour plus d’informations, consultez Conversion non déterministe de chaînes de date littérale en valeurs DATE.

Niveau de compatibilité

La conversion implicite de données de caractères non Unicode entre les classements est considérée comme non déterministe, sauf si le niveau de compatibilité est défini sur 80 ou antérieur.

Lorsque le paramètre de niveau de compatibilité de la base de données est 90, vous ne pouvez pas créer d’index sur des 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 qui contiennent des conversions implicites de chaîne à date, pour éviter toute altération possible de l’index, assurez-vous que les paramètres et DATEFORMAT les LANGUAGE paramètres sont cohérents dans vos bases de données et applications.

Le niveau 90 de compatibilité correspond à SQL Server 2005 (9.x).

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 quand une ou plusieurs des conditions suivantes sont remplies :

  • Il ne s’agit pas d’une expression des types de données float ou réels .

  • Il n’utilise pas de type de données float ou réel 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);

Note

Toute expression float ou real est considérée comme non précise et ne peut pas être une clé d’index. Autrement dit, une expression float ou real 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 IsPrecise propriété de la COLUMNPROPERTY fonction indique si une computed_column_expression est précise.

Conditions requises liées aux types de données

  • La computed_column_expression définie pour la colonne calculée ne peut pas évaluer les types de données texte, 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 de lacolonne calculée est autorisé en tant que colonne clé d’index.
  • Les colonnes calculées dérivées des types de données image, ntextet 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 ANSI_NULLS au niveau de la connexion doit être définie ON sur le moment où l’instruction CREATE TABLEALTER TABLE qui définit la colonne calculée est exécutée. La fonction OBJECTPROPERTY indique si l’option est activée via la IsAnsiNullsOn propriété.

  • La connexion sur laquelle l’index est créé et toutes les connexions trying INSERT, UPDATEou DELETE instructions qui modifient les valeurs dans l’index, doivent avoir six SET options définies ON sur et une option définie sur OFF. L’optimiseur ignore un index sur une colonne calculée pour toute SELECT instruction exécutée par une connexion qui n’a pas ces mêmes paramètres d’option.

    L’option NUMERIC_ROUNDABORT doit être définie OFFsur , et les options suivantes doivent être définies sur ON:

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

Note

Définition de la valeur ANSI_WARNINGS implicitement ONARITHABORT définie lorsque le niveau de compatibilité de la base de données est défini 90 sur ou ON supérieur.

Créer des index sur des colonnes calculées persistantes

Parfois, vous pouvez créer une colonne calculée qui est définie par une expression déterministe, mais non précise. Pour ce faire, lorsque la colonne est marquée PERSISTED dans l’instruction ou ALTER TABLE l’instructionCREATE 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 précision si une fonction qui retourne des expressions de colonne calculées, en particulier une fonction CLR créée dans le .NET Framework, est à la fois déterministe et précise.

Note

Vous ne pouvez pas créer d’index filtré sur une colonne calculée.

Étapes suivantes