Créer des vues indexées

Article
07/25/2024

S’applique à : SQL Server Azure SQL Database Azure SQL Managed Instance

Cet article décrit comment créer des index sur une vue. Le premier index créé sur une vue doit être un index cluster unique. Après avoir créé l'index cluster unique, vous pouvez créer davantage d'index non cluster. La création d'un index cluster unique sur une vue améliore les performances des requêtes, car la vue est stockée dans la base de données au même titre qu'une table avec un index cluster. L'optimiseur de requête peut utiliser des vues indexées pour accélérer l'exécution des requêtes. Il n'est pas nécessaire de référencer la vue dans la requête pour que l'optimiseur envisage d'utiliser cette vue.

Étapes

Les étapes suivantes de création d'une vue indexée sont essentielles à la réussite de l'implémentation de la vue indexée :

Vérifiez que les options SET sont correctes pour toutes les tables existantes qui seront référencées dans la vue.
Vérifiez que les options SET de la session sont définies correctement avant de créer des tables et la vue.
Vérifiez que la définition de la vue est déterministe.
Vérifiez que la table de base a le même propriétaire que la vue.
Créez la vue en utilisant l’option WITH SCHEMABINDING.
Créez l'index cluster unique sur la vue.

Lorsque vous exécutez des opérations (de langage de manipulation de données, ou DML) UPDATE, DELETE, INSERT sur une table référencée par un grand nombre de vues indexées, ou par moins de vues mais complexes, ces vues indexées référencées doivent également être mises à jour. Par conséquent, les performances des requêtes DML peuvent se dégrader considérablement ou, dans certains cas, un plan de requête ne peut même pas être produit.

Dans de tels scénarios, testez vos requêtes DML avant une utilisation en production, analysez le plan de requête et optimisez/simplifiez l’instruction DML.

Options SET requises pour les vues indexées

L'évaluation de la même expression peut produire des résultats différents dans le moteur de base de données si des options SET différentes sont actives lors de l'exécution de la requête. Par exemple, lorsque l'option SET CONCAT_NULL_YIELDS_NULL est définie sur ON, l'expression 'abc' + NULL retourne la valeur NULL. Cependant, si l’option CONCAT_NULL_YIELDS_NULL est définie sur OFF, la même expression produit abc.

Pour pouvoir gérer correctement les vues et retourner des résultats cohérents, les vues indexées nécessitent des valeurs fixes pour plusieurs options SET. Les options SET répertoriées dans le tableau ci-dessous doivent être définies avec les valeurs indiquées dans la colonne Required value chaque fois que les conditions suivantes sont réunies :

La vue et les index suivants sur la vue sont créés.
Tables de base référencées dans la vue quand celle-ci est créée.
Lorsqu'une insertion, une mise à jour ou une suppression est exécutée sur une table qui participe à la vue indexée. Cette exigence inclut des opérations telles que la copie en bloc, la réplication et les requêtes distribuées.
L'optimiseur de requête utilise la vue indexée pour générer le plan de requête.

Options définies	Valeur requise	Valeur de serveur par défaut	Default Valeur OLE DB et ODBC	Default Valeur DB-Library
`ANSI_NULLS`	`ON`	`ON`	`ON`	`OFF`
`ANSI_PADDING`	`ON`	`ON`	`ON`	`OFF`
`ANSI_WARNINGS` ¹	`ON`	`ON`	`ON`	`OFF`
`ARITHABORT`	`ON`	`ON`	`OFF`	`OFF`
`CONCAT_NULL_YIELDS_NULL`	`ON`	`ON`	`ON`	`OFF`
`NUMERIC_ROUNDABORT`	`OFF`	`OFF`	`OFF`	`OFF`
`QUOTED_IDENTIFIER`	`ON`	`ON`	`ON`	`OFF`

¹ Définir ANSI_WARNINGS sur ON définit implicitement ARITHABORT sur ON.

Si vous utilisez une connexion serveur OLE DB ou ODBC, la seule valeur qui doit être modifiée est le paramètre ARITHABORT. Toutes les valeurs DB-Library doivent être définies correctement au niveau du serveur à l’aide de sp_configure, ou dans l’application à l’aide de la commande SET.

Important

Il est fortement recommandé de définir l’option utilisateur ARITHABORT sur ON sur le serveur dès la création de la première vue indexée ou du premier index sur une colonne calculée dans une base de données sur le serveur.

Exigence de vue déterministe

La définition d'une vue indexée doit être déterministe. Une vue est déterministe si toutes les expressions de la liste de sélection, ainsi que les clauses WHERE et GROUP BY sont déterministes. Les expressions déterministes retournent toujours le même résultat chaque fois qu'elles sont évaluées avec un groupe de valeurs d'entrée spécifiques. Seules les fonctions déterministes peuvent participer à des expressions déterministes. Par exemple, la fonction DATEADD est déterministe, car elle retourne toujours le même résultat pour un groupe donné de valeurs d’arguments pour ses trois paramètres. GETDATE n’est pas déterministe, car elle est toujours appelée avec le même argument, mais la valeur qu’elle retourne change chaque fois qu’elle s'exécute.

Pour déterminer si une colonne de vue est déterministe, utilisez la propriété IsDeterministic de la fonction COLUMNPROPERTY. Pour déterminer si une colonne déterministe d’une vue avec une liaison de schéma est précise, utilisez la propriété IsPrecise de la fonction COLUMNPROPERTY. COLUMNPROPERTY retourne 1 si TRUE, 0 si FALSE, et NULL pour l’entrée qui n’est pas valide. Cela signifie que la colonne n'est pas déterministe ou pas précise.

Même si une expression est déterministe, si elle contient des expressions flottantes, le résultat exact dépend de l'architecture du processeur ou de la version du microcode. Pour garantir l'intégrité des données, ces expressions peuvent participer seulement sous forme de colonnes non clés de vues indexées. Les expressions déterministes qui ne contiennent pas d'expressions flottantes s'appellent des expressions précises. Seules les expressions déterministes précises peuvent participer à des colonnes clés et dans les clauses WHERE et GROUP BY des vues indexées.

Autres conditions requises

Outre les options SET et les exigences concernant la fonction déterministe, les conditions suivantes doivent être satisfaites

L’utilisateur qui exécute CREATE INDEX doit être le propriétaire de la vue.
Quand vous créez l’index, l’option d’index IGNORE_DUP_KEY doit être définie sur OFF (valeur par défaut).
Les tables doivent être référencées par des noms en deux parties, <schema>.<tablename>, dans la définition de la vue.
Les fonctions définies par l’utilisateur référencées dans la vue doivent avoir été créées avec l’option WITH SCHEMABINDING.
Toutes les fonctions définies par l’utilisateur référencées dans la vue doivent être référencées par des noms en deux parties,<schema>.<function>.
La propriété d’accès aux données d’une fonction définie par l’utilisateur doit avoir la valeur NO SQL, et la propriété d’accès externe doit avoir la valeur NO.
Les fonctions CLR (Common Language Runtime) peuvent s'afficher dans la liste SELECT de la vue mais ne peuvent pas faire partie de la définition de la clé d'index cluster. Ces fonctions ne peuvent pas apparaître dans la clause WHERE de la vue ou dans la clause ON d'une opération JOIN au sein de la vue.

Les propriétés des méthodes et fonctions CLR des types CLR définis par l'utilisateur employés dans la définition de vue doivent être définies de la manière illustrée dans le tableau suivant.

Propriété	Remarque
DETERMINISTIC = TRUE	Doit être déclarée explicitement comme attribut de la méthode Microsoft .NET Framework.
PRECISE = TRUE	Doit être déclarée explicitement comme attribut de la méthode .NET Framework.
DATA ACCESS = NO SQL	Déterminé en réglant l'attribut `DataAccess` sur `DataAccessKind.None` et l'attribut `SystemDataAccess` sur `SystemDataAccessKind.None`.
EXTERNAL ACCESS = NO	Cette propriété a la valeur NO par défaut pour les routines CLR.

La vue doit être créée avec l’option WITH SCHEMABINDING.
La vue doit référencer seulement des tables de base qui sont dans la même base de données que la vue. La vue ne peut pas faire référence à d'autres vues.
Si la clause GROUP BY est présente, la définition VIEW doit contenir COUNT_BIG(*), mais pas HAVING. Ces restrictions de GROUP BY sont applicables seulement à la définition de la vue indexée. Une requête peut utiliser une vue indexée dans son plan d’exécution, même si elle ne répond pas à ces restrictions de GROUP BY.
Si la définition de la vue contient une clause GROUP BY, la clé de l’index cluster unique peut référencer seulement les colonnes définies dans la clause GROUP BY.

L'instruction SELECT de la définition de la vue ne doit pas contenir la syntaxe Transact-SQL suivante :

Fonctions Transact-SQL	Alternatives possibles
`COUNT`	Utilisez `COUNT_BIG`.
Fonctions `ROWSET` (`OPENDATASOURCE`, `OPENQUERY`, `OPENROWSET`, et `OPENXML`)
Moyenne arithmétique (`AVG`)	Utiliser `COUNT_BIG` et `SUM` comme colonnes séparées
Fonctions d'agrégation statistique (`STDEV`, `STDEVP`, `VAR` et `VARP`)
Fonction `SUM` qui référence une expression acceptant les valeurs Null	Utiliser `ISNULL` à l'intérieur de `SUM()` pour rendre l'expression non-nullable
Autres fonctions d’agrégation (`MIN`, `MAX`, `CHECKSUM_AGG` et `STRING_AGG`)
Fonctions d'agrégation définies par l'utilisateur (SQL CLR)

Clause SELECT	Élément Transact-SQL	Alternative possible
`WITH cte AS`	Expressions de table communes (CTE) `WITH`
`SELECT`	Sous-requêtes
`SELECT`	`SELECT [ <table>. ] *`	Nommer explicitement les colonnes
`SELECT`	`SELECT DISTINCT`	Utilisez `GROUP BY`.
`SELECT`	`SELECT TOP`
`SELECT`	Clause `OVER`, qui inclut des fonctions de classement ou d’agrégation de fenêtre
`FROM`	`LEFT OUTER JOIN`
`FROM`	`RIGHT OUTER JOIN`
`FROM`	`FULL OUTER JOIN`
`FROM`	`OUTER APPLY`
`FROM`	`CROSS APPLY`
`FROM`	Expressions de table dérivées (c'est-à-dire utilisant `SELECT` dans la clause `FROM`)
`FROM`	Jointures réflexives
`FROM`	Variables de table
`FROM`	Fonctions table incluses
`FROM`	Fonctions table multi-instructions
`FROM`	`PIVOT`, `UNPIVOT`
`FROM`	`TABLESAMPLE`
`FROM`	`FOR SYSTEM_TIME`	Interroger directement la table d’historique temporel
`WHERE`	Prédicats de texte intégral (`CONTAINS`, `FREETEXT`, `CONTAINSTABLE`, `FREETEXTTABLE`)
`GROUP BY`	Opérateurs `CUBE`, `ROLLUP` ou `GROUPING SETS`	Définir des vues indexées séparées pour chaque combinaison de colonnes `GROUP BY`
`GROUP BY`	`HAVING`
Opérateurs de jeu	`UNION`, `UNION ALL`, `EXCEPT`, `INTERSECT`	Utiliser respectivement `OR`, `AND NOT` et `AND` dans la clause `WHERE`
`ORDER BY`	`ORDER BY`
`ORDER BY`	`OFFSET`

Type de colonne source	Alternative possible
Colonnes de grande valeur de type (text, ntext et image) déconseillées	Faites migrer les colonnes vers varchar(max), nvarchar(max) et varbinary(max) respectivement.
colonnes xml ou FILESTREAM
float ¹ colonnes dans la clé d’index
Jeux de colonnes éparses

¹ La vue indexée peut contenir des colonnes float, mais ces colonnes ne peuvent pas être incluses dans la clé d’index cluster.

Important

Les vues indexées ne sont pas prises en charge en plus des requêtes temporelles (qui utilisent la clause FOR SYSTEM_TIME).

Recommandations pour dateHeure et smalldatetime

Si vous faites référence aux littéraux de chaîne datetime et smalldatetime au sein de vues indexées, il est recommandé de convertir explicitement le littéral en type 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 sur les expressions déterministes et non déterministes, consultez la section Considérations de cette page.

Les expressions qui impliquent une conversion implicite de chaînes de caractères en datetime ou smalldatetime sont considérées comme non déterministes. Pour plus d’informations, consultez Conversion non déterministe de chaînes de date littérale en valeurs DATE.

Considérations sur les performances avec les vues indexées

Lors de l’exécution d’instructions DML (comme UPDATE, DELETE ou INSERT) sur une table référencée par un grand nombre de vues indexées, ou par moins de vues mais complexes, ces vues indexées référencées doivent également être mises à jour pendant l’exécution d’instructions DML. Par conséquent, les performances des requêtes DML peuvent se dégrader considérablement ou, dans certains cas, un plan de requête ne peut même pas être produit. Dans de tels scénarios, testez vos requêtes DML avant une utilisation en production, analysez le plan de requête et optimisez/simplifiez l’instruction DML.

Pour empêcher le moteur de base de données d'utiliser les vues indexées, incluez l'indicateur OPTION (EXPAND VIEWS) dans la requête. En outre, si l'une des options énumérées est définie incorrectement, cette option empêche l'optimiseur d'utiliser les index sur les vues. Pour plus d’informations sur l’indicateur OPTION (EXPAND VIEWS), consultez SELECT .

Considérations supplémentaires

La valeur de l’option large_value_types_out_of_row des colonnes contenues dans une vue indexée est héritée de la valeur de la colonne correspondante dans la table de base. Cette valeur est définie à l’aide de sp_tableoption. La valeur par défaut des colonnes constituées à partir d'expressions est 0. Cela signifie que les types de valeurs élevées sont stockés dans la ligne.
Des vues indexées peuvent être créées sur une table partitionnée, et elles peuvent elles-mêmes être partitionnées.
Si une vue est supprimée, tous ses index le sont également. Tous les index non cluster et les caractéristiques créées automatiquement d'une vue sont supprimés lorsque son index cluster l'est. Les statistiques créées par l'utilisateur sur la vue sont conservées. Les index non cluster peuvent toutefois être supprimés individuellement. Lorsque l'index cluster de la vue est supprimé, le jeu de résultats stocké est aussi supprimé, et l'optimiseur traite de nouveau la vue comme une vue standard.
Les index sur les tables et les vues peuvent être désactivés. Lorsqu'un index cluster sur une table est désactivé, les index sur les vues associées à la table le sont également.

autorisations

Pour créer la vue, un utilisateur a besoin de l’autorisation CREATE VIEW dans la base de données et de l’autorisation ALTER sur le schéma où la vue est créée. Si la table de base se trouve dans un autre schéma, REFERENCES est l’autorisation minimale nécessaire sur la table. Si l’utilisateur qui crée l’index diffère des utilisateurs qui ont créé la vue, pour la création de l’index seul, l’autorisation ALTER sur la vue est nécessaire (couverte par ALTER sur le schéma).

Les index peuvent être seulement créés sur des vues qui ont le même propriétaire que la ou les tables référencées. Ce concept est également appelé chaîne de propriété intacte entre la vue et les tables. En règle générale, lorsque la table et la vue résident dans le même schéma, le même propriétaire de schéma s’applique à tous les objets du schéma. C’est pourquoi il est possible de créer une vue et non le propriétaire de la vue. En revanche, il est également possible que les objets individuels d’un schéma aient des propriétaires explicites différents. La colonne principal_id dans sys.tables contient une valeur si le propriétaire est différent du propriétaire de schéma.

Créer une vue indexée : un exemple T-SQL

L'exemple suivant crée une vue et un index sur cette vue, dans la base de données AdventureWorks.

--Set the options to support indexed views.
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING,
    ANSI_WARNINGS,
    CONCAT_NULL_YIELDS_NULL,
    ARITHABORT,
    QUOTED_IDENTIFIER,
    ANSI_NULLS ON;

--Create view with SCHEMABINDING.
IF OBJECT_ID('Sales.vOrders', 'view') IS NOT NULL
    DROP VIEW Sales.vOrders;
GO

CREATE VIEW Sales.vOrders
    WITH SCHEMABINDING
AS
SELECT SUM(UnitPrice * OrderQty * (1.00 - UnitPriceDiscount)) AS Revenue,
    OrderDate,
    ProductID,
    COUNT_BIG(*) AS COUNT
FROM Sales.SalesOrderDetail AS od,
    Sales.SalesOrderHeader AS o
WHERE od.SalesOrderID = o.SalesOrderID
GROUP BY OrderDate,
    ProductID;
GO

--Create an index on the view.
CREATE UNIQUE CLUSTERED INDEX IDX_V1 ON Sales.vOrders (
    OrderDate,
    ProductID
);
GO

Les deux requêtes suivantes montrent comment la vue indexée peut être utilisée, même si la vue n'est pas spécifiée dans la clause FROM.

--This query can use the indexed view even though the view is
--not specified in the FROM clause.
SELECT SUM(UnitPrice * OrderQty * (1.00 - UnitPriceDiscount)) AS Rev,
    OrderDate,
    ProductID
FROM Sales.SalesOrderDetail AS od
INNER JOIN Sales.SalesOrderHeader AS o
    ON od.SalesOrderID = o.SalesOrderID
        AND o.OrderDate >= CONVERT(DATETIME, '05/01/2012', 101)
WHERE od.ProductID BETWEEN 700
        AND 800
GROUP BY OrderDate,
    ProductID
ORDER BY Rev DESC;
GO

--This query will also use the above indexed view.
SELECT OrderDate,
    SUM(UnitPrice * OrderQty * (1.00 - UnitPriceDiscount)) AS Rev
FROM Sales.SalesOrderDetail AS od
INNER JOIN Sales.SalesOrderHeader AS o
    ON od.SalesOrderID = o.SalesOrderID
        AND o.OrderDate >= CONVERT(DATETIME, '03/01/2012', 101)
        AND o.OrderDate < CONVERT(DATETIME, '04/01/2012', 101)
GROUP BY OrderDate
ORDER BY OrderDate ASC;

Enfin, cet exemple montre comment effectuer des requêtes directement à partir de la vue indexée. Avant SQL Server 2016 (13.x) Service Pack 1, l’utilisation automatique d’une vue indexée par l’optimiseur de requête est prise en charge seulement dans certaines éditions de SQL Server. Dans l'édition SQL Server Standard, vous devez utiliser l'indicateur de requête NOEXPAND pour interroger directement la vue indexée. Depuis SQL Server 2016 (13.x) Service Pack 1, toutes les éditions prennent en charge l'utilisation automatique d'une vue indexée. Azure SQL Database et Azure SQL Managed Instance prennent également en charge l’utilisation automatique de vues indexées sans spécification de l’indicateur NOEXPAND. Pour plus d’informations, consultez Indicateurs de table (Transact-SQL).

--This query uses the indexed view directly, on Enterprise edition.
SELECT OrderDate, Revenue
FROM Sales.vOrders
WHERE OrderDate >= CONVERT(DATETIME, '03/01/2012', 101)
    AND OrderDate < CONVERT(DATETIME, '04/01/2012', 101)
ORDER BY OrderDate ASC;

--This query uses the indexed view directly, with the NOEXPAND hint.
SELECT OrderDate, Revenue
FROM Sales.vOrders WITH (NOEXPAND)
WHERE OrderDate >= CONVERT(DATETIME, '03/01/2012', 101)
    AND OrderDate < CONVERT(DATETIME, '04/01/2012', 101)
ORDER BY OrderDate ASC;

Pour plus d’informations, consultez CREATE VIEW.

Partage via

Créer des vues indexées

Étapes

Options SET requises pour les vues indexées

Exigence de vue déterministe

Autres conditions requises

Recommandations pour dateHeure et smalldatetime

Considérations sur les performances avec les vues indexées

Considérations supplémentaires

autorisations

Créer une vue indexée : un exemple T-SQL

Commentaires

Ressources supplémentaires

Partage via

Créer des vues indexées

Étapes

Options SET requises pour les vues indexées

Exigence de vue déterministe

Autres conditions requises

Recommandations pour dateHeure et smalldatetime

Considérations sur les performances avec les vues indexées

Considérations supplémentaires

autorisations

Créer une vue indexée : un exemple T-SQL

Contenu connexe

Commentaires

Ressources supplémentaires