Index sur les colonnes calculées

S’applique à : SQL Server Azure SQL Database Azure 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

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. 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 de type PERSISTED. Pour plus d’informations, consultez Créer des index sur des colonnes calculées persistantes, plus loin dans cet 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, certaines 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.

• Le paramètre computed_column_expression n’a 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.

CAST et CONVERT

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 dans le 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, à 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.

Le niveau de compatibilité 90 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 real.

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

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, ntextou 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 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 groupes d’options SET sur ON et un groupe d’options sur 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éfinie sur OFF et les options suivantes doivent être définies sur ON :

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

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. Vous pouvez le faire quand 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.

Étapes suivantes

• COLUMNPROPERTY (Transact-SQL)
• CREATE TABLE (Transact-SQL)
• ALTER TABLE (Transact-SQL)