Des index sur les colonnes calculées

Vous pouvez définir des index sur des colonnes calculées tant que les exigences suivantes sont remplies :

• Exigences en matière de propriété

• Exigences de déterminisme

• Exigences de précision

• Exigences en matière de type de données

• Configuration requise pour les options SET

Exigences en matière de propriété

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

Exigences de déterminisme

La computed_column_expression doit être déterministe. Un computed_column_expression est déterministe lorsqu’un ou plusieurs des éléments suivants sont vrais :

• Toutes les fonctions référencées par l’expression sont déterministes et précises. Ces fonctions incluent les fonctions définies par l’utilisateur et 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 persistante. Pour plus d’informations, consultez Création d’index sur des colonnes calculées persistantes plus loin dans cette rubrique.

• Toutes les colonnes référencées dans l’expression proviennent de la table qui contient la colonne calculée.

• Aucune référence de colonne n’extrait les données 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 computed_column_expression n’a pas d’accès aux données système ou d’accès aux données 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 autorisées dans les définitions de colonnes calculées. Les colonnes calculées dont le type est un type défini par l’utilisateur CLR peuvent être indexées tant que le type est comparable. Pour plus d’informations, consultez Types CLR définis par l’utilisateur.

Exigences de précision

Le computed_column_expression doit être précis. Une computed_column_expression est précise lorsqu’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 ni float ni real comme type de données dans sa définition. Par exemple, dans l’instruction suivante, la colonne y est 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 computed_column_expression est précis.

Exigences relatives au type de données

• La computed_column_expression définie pour la colonne calculée ne peut pas être évaluée aux types de données text, ntext, ou image.

• Les colonnes calculées dérivées de image, ntext, text, varchar(max), nvarchar(max), varbinary(max), et xml peuvent être indexées tant que le type de données de colonne calculée est autorisé en tant que colonne clé d'index.

• Les colonnes calculées dérivées des colonnes calculées , ntextet text les types de imagedonnées peuvent être des colonnes non clés (incluses) dans un index non cluster tant que le type de données de colonne calculée est autorisé en tant que colonne d’index non clé.

Configuration requise pour l’option SET

• L’option de niveau connexion ANSI_NULLS doit être définie sur ON lorsque l’instruction CREATE TABLE ou ALTER TABLE qui définit la colonne calculée est exécutée. La fonction OBJECTPROPERTY indique si l’option est activée via la propriété IsAnsiNullsOn .

• La connexion sur laquelle l’index est créé et toutes les connexions qui essaient d’exécuter des instructions INSERT, UPDATE ou DELETE qui modifient les valeurs dans l’index doivent avoir six options SET définies sur ON et une option définie sur OFF. L’optimiseur ignore un index sur une colonne calculée pour toute instruction SELECT 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 sur OFF, et les options suivantes doivent être définies sur ON :

  • ANSI_NULLS

  • ANSI_PADDING

  • ANSI_WARNINGS (Paramètre des avertissements ANSI)

  • ARITHABORT

  • CONCAT_NULL_YIELDS_NULL

  • IDENTIFIANT_CITÉ

  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'au moins 90.

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

Vous pouvez créer un index sur une colonne calculée définie avec une expression déterministe, mais imprécise si la colonne est marquée PERSISTED dans l’instruction CREATE TABLE ou ALTER TABLE. Cela signifie que le moteur de base de données utilise ces valeurs persistantes lorsqu’il crée un index sur la colonne et lorsque l’index est référencé dans une requête. Cette option vous permet de créer un index sur une colonne calculée lorsque .NET Framework est à la fois déterministe et précis.

Contenu associé

COLUMNPROPERTY (Transact-SQL)