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.

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)
  

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.