Index sur les colonnes calculées
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
ouAVG
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éfinieON
sur le moment où l’instructionCREATE TABLE
ALTER TABLE
qui définit la colonne calculée est exécutée. La fonction OBJECTPROPERTY indique si l’option est activée via laIsAnsiNullsOn
propriété.La connexion sur laquelle l’index est créé et toutes les connexions trying
INSERT
,UPDATE
ouDELETE
instructions qui modifient les valeurs dans l’index, doivent avoir sixSET
options définiesON
sur et une option définie surOFF
. L’optimiseur ignore un index sur une colonne calculée pour touteSELECT
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éfinieOFF
sur , et les options suivantes doivent être définies surON
:ANSI_NULLS
ANSI_PADDING
ANSI_WARNINGS
ARITHABORT
CONCAT_NULL_YIELDS_NULL
QUOTED_IDENTIFIER
Note
Définition de la valeur ANSI_WARNINGS
implicitement ON
ARITHABORT
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.
Étapes suivantes
Commentaires
https://aka.ms/ContentUserFeedback.
Bientôt disponible : Tout au long de 2024, nous allons supprimer progressivement GitHub Issues comme mécanisme de commentaires pour le contenu et le remplacer par un nouveau système de commentaires. Pour plus d’informations, consultezEnvoyer et afficher des commentaires pour