• Article

Création de vues indexées

Pour que vous puissiez créer un index cluster sur une vue, celle-ci doit respecter les conditions suivantes :

  • Les options ANSI_NULLS et QUOTED_IDENTIFIER doivent être activées (ON) lors de l'exécution de l'instruction CREATE VIEW. La fonction OBJECTPROPERTY indique cette information pour les vues par le biais des propriétés ExecIsAnsiNullsOn ou ExecIsQuotedIdentOn.

  • L'option ANSI_NULLS doit être activée (ON) lors de l'exécution de toutes les instructions CREATE TABLE qui créent des tables référencées par la vue.

  • La vue ne doit pas faire référence à d'autres vues, mais uniquement à des tables de base.

  • Toutes les tables de base référencées par la vue doivent figurer dans la même base de données que celle-ci et appartenir au même propriétaire.

  • La vue doit être créée avec l'option SCHEMABINDING, laquelle lie la vue au schéma des tables de base sous-jacentes.

  • Les fonctions définies par l'utilisateur référencées dans la vue doivent avoir été créées avec l'option SCHEMABINDING.

  • Les tables et les fonctions définies par l'utilisateur doivent être référencées par des noms à deux composantes dans la vue. Les noms à une, trois et quatre composantes ne sont pas autorisés.

  • Toutes les fonctions référencées par des expressions dans la vue doivent être déterministes. La propriété IsDeterministic de la fonction OBJECTPROPERTY indique si une fonction définie par l'utilisateur est déterministe. Pour plus d'informations, consultez Fonctions déterministes et non déterministes.

    Notes

    Si vous faites référence aux littéraux de chaîne datetime et smalldatetime au sein de vues indexées dans SQL Server 2008, 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 (Transact-SQL). Les expressions qui impliquent une conversion implicite des chaînes de caractères en datetime ou smalldatetime sont considérées comme non déterministes, à moins que le niveau de compatibilité soit égal ou inférieur à 80. Cela est dû au fait que les résultats dépendent des paramètres LANGUAGE et DATEFORMAT de la session serveur. Par exemple, les résultats de l'expression CONVERT (datetime, '30 listopad 1996', 113) dépendent du paramètre LANGUAGE, car la chaîne 'listopad' désigne des mois différents selon la langue. De même, dans l'expression DATEADD(mm,3,'2000-12-01'), SQL Server interprète la chaîne '2000-12-01' en fonction du paramètre DATEFORMAT.

    La conversion implicite de données de caractères non-Unicode entre les classements est également considérée comme non déterministe, à moins que le niveau de compatibilité ne soit réglé sur 80 ou antérieur.

    La création d'index sur des vues contenant ces expressions n'est pas autorisée en mode de compatibilité 90. Cependant, les vues existantes qui contiennent ces expressions provenant d'une base de données mise à niveau sont gérables. Si vous utilisez des vues indexées contenant une chaîne implicite pour les conversions de dates, veillez à ce que les paramètres LANGUAGE et DATEFORMAT soient cohérents dans vos bases de données et applications afin d'éviter toute altération potentielle des vues indexées.

  • Si la définition de vue utilise une fonction d'agrégation, la liste SELECT doit également comprendre COUNT_BIG (*).

  • 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 en tant qu'attribut de la méthode .NET Framework.

    DATA ACCESS = NO SQL

    Déterminée en affectant à l'attribut DataAccess la valeur DataAccessKind.None et à l'attribut SystemDataAccess la valeur SystemDataAccessKind.None.

    EXTERNAL ACCESS = NO

    Cette propriété a la valeur NO par défaut pour les routines CLR.

    Pour plus d'informations sur la définition d'attributs des méthodes de routine CLR, consultez Attributs personnalisés pour les routines CLR.

    AttentionAttention

    Il est déconseillé de définir les propriétés des méthodes de routine CLR en contradiction avec leurs fonctionnalités au risque de provoquer une altération des données.

  • L'instruction SELECT de la vue ne peut pas contenir les éléments syntaxiques Transact-SQL suivants :

    • La syntaxe * ou table_name**.*** pour spécifier des colonnes. Les noms de colonnes doivent être indiqués explicitement.

    • Un nom de colonne de table utilisé comme expression simple ne peut pas être spécifié dans plusieurs colonnes de vue. Une colonne peut être référencée plusieurs fois sous réserve que toutes ses références (ou toutes sauf une) fassent partie d'un expression complexe ou d'un paramètre de fonction. Par exemple, la liste de sélection suivante n'est pas valide :

      SELECT ColumnA, ColumnB, ColumnA

      La liste de sélection suivante est valide :

      SELECT SUM(ColumnA) AS SumColA, ColumnA % ColumnB AS ModuloColAColB, COUNT_BIG(*) AS cBig FROM dbo.T1 GROUP BY ModuloColAColB

    • Une expression sur une colonne utilisée dans la clause GROUP BY, ou une expression sur les résultats d'un agrégat.

    • Une table dérivée.

    • Une expression de table commune.

    • Des fonctions d'ensemble de lignes.

    • Des opérateurs UNION, EXCEPT ou INTERSECT.

    • Des sous-requêtes.

    • Des jointures externes ou réflexives.

    • La clause TOP.

    • La clause ORDER BY.

    • Le mot clé DISTINCT.

    • COUNT (COUNT_BIG(*) est autorisé.)

    • Les fonctions d'agrégation AVG, MAX, MIN, STDEV, STDEVP, VAR ou VARP. Si AVG(expression) est spécifié dans des requêtes faisant référence à la vue indexée, l'optimiseur peut calculer fréquemment le résultat requis si la liste de sélection de la vue contient SUM(expression) et COUNT_BIG(expression). Par exemple, la liste SELECT d'une vue indexée ne peut pas contenir l'expression AVG(column1). Si elle contient les expressions SUM(column1) et COUNT_BIG(column1), SQL Server peut calculer la moyenne dans une requête qui fait référence à la vue et spécifie AVG(column1).

    • Une fonction SUM qui référence une expression acceptant les valeurs NULL.

    • La clause OVER, qui inclut des fonctions de classement ou d'agrégation de fenêtre.

    • Une fonction d'agrégation CLR définie par l'utilisateur.

    • Les prédicats de texte intégral CONTAINS ou FREETEXT.

    • La clause COMPUTE ou COMPUTE BY.

    • Des opérateurs CROSS APPLY ou OUTER APPLY.

    • Opérateurs PIVOT ou UNPIVOT

    • Des indicateurs de table (s'applique au niveau de compatibilité 90 ou supérieur uniquement).

    • Des indicateurs de jointure.

    • Références directes aux expressions Xquery. Les références indirectes, comme les expressions Xquery à l'intérieur d'une fonction définie par l'utilisateur liée au schéma, sont acceptables.

  • Si GROUP BY est spécifié, la liste de sélection de la vue doit contenir une expression COUNT_BIG(*), et la définition de la vue ne peut pas spécifier ROLLING, CUBE ou GROUPING SETS.

Conditions requises pour l'instruction CREATE INDEX

Le premier index créé sur une vue doit être un index cluster unique. Après cela, vous pouvez créer des index non-cluster. Les conventions d'appellation des index de vues sont les mêmes que celles des index de tables. La seule différence est que le nom de table est remplacé par un nom de vue. Pour plus d'informations, consultez CREATE INDEX (Transact-SQL).

L'instruction CREATE INDEX doit répondre aux exigences suivantes, ainsi qu'aux exigences habituelles liées à CREATE INDEX :

  • L'utilisateur qui exécute l'instruction CREATE INDEX doit être le propriétaire de la vue.

  • Les options SET suivantes doivent avoir la valeur ON si l'instruction CREATE INDEX est exécutée :

    • ANSI_NULLS

    • ANSI_PADDING

    • ANSI_WARNINGS

    • CONCAT_NULL_YIELDS_NULL

    • QUOTED_IDENTIFIER

  • L'option NUMERIC_ROUNDABORT doit être désactivée (OFF). Il s'agit de la valeur par défaut.

  • Si la base de données s'exécute au niveau de compatibilité 80 ou moins, la valeur ON doit être affectée à l'option ARITHABORT.

  • Lorsque vous créez un index cluster ou non-cluster, l'option IGNORE_DUP_KEY doit avoir la valeur OFF (valeur par défaut).

  • La vue ne peut pas comprendre de colonnes de type text, ntext ou image, même non référencées dans l'instruction CREATE INDEX.

  • Si l'instruction SELECT dans la définition de la vue spécifie une clause GROUP BY, la clé de l'index cluster unique ne peut faire référence qu'à des colonnes spécifiées dans la clause GROUP BY.

  • Une expression imprécise qui forme la valeur d'une colonne de clé d'index doit faire référence à une colonne stockée dans une table de base sous-jacente de la vue. Cette colonne peut être une colonne stockée ordinaire ou une colonne calculée persistante. Aucune autre expression imprécise ne peut faire partie de la colonne de clé d'une vue indexée.

Observations

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 peut être définie à l'aide de l'option 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. Pour plus d'informations, consultez Utilisation de types de données de valeur élevée.

Une fois l'index cluster créé, toute connexion tentant de modifier les données de base de la vue doivent également posséder les mêmes valeurs d'options que celles nécessaires à la création de l'index. SQL Server génère une erreur et restaure toute instruction INSERT, UPDATE ou DELETE affectant l'ensemble de résultats de la vue si la connexion qui exécute l'instruction ne possède pas les valeurs d'options correctes. Pour plus d'informations, consultez Options SET affectant les résultats.

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é, l'ensemble de résultats stocké est aussi supprimé, et l'optimiseur traite de nouveau la vue comme une vue standard.

Bien que seules les colonnes qui composent la clé de l'index cluster soient spécifiées dans l'instruction CREATE UNIQUE CLUSTERED INDEX, l'ensemble de résultats complet de la vue est stocké dans la base de données. Comme dans l'index cluster d'une table de base, la structure B-tree de l'index cluster contient uniquement les colonnes clés, mais les lignes de données englobent toutes les colonnes de l'ensemble de résultats de la vue.

Si vous souhaitez ajouter un index à une vue dans un système existant, vous devez la lier au schéma. Effectuez les opérations suivantes :

  • supprimer la vue et la recréer en spécifiant WITH SCHEMABINDING ;

  • créer une seconde vue possédant le même texte que la vue existante mais un nom différent, et l'optimiseur prend alors en compte les index de la nouvelle vue, même si elle n'est pas directement référencée dans la clause FROM des requêtes.

    Notes

    Les vues ou tables impliquées dans une vue créée avec la clause SCHEMABINDING ne peuvent pas être supprimées, sauf si cette vue perd, à la suite de sa suppression ou de sa modification, la liaison au schéma. En outre, les instructions ALTER TABLE portant sur des tables impliquées dans des vues liées au schéma échouent si elles affectent la définition des vues.

Vous devez veiller à ce que la nouvelle vue remplisse toutes les conditions requises associées à une vue indexée. Ceci peut vous amener à changer le propriétaire de la vue et de toutes les tables de base qu'elle référence, afin que l'ensemble de ces éléments appartienne au même utilisateur.

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. Pour plus d'informations, consultez Désactivation d'index.

Exemple

L'exemple suivant crée une vue et un index sur cette vue. Deux requêtes utilisant la vue indexée sont incluses.

USE AdventureWorks2008R2;
GO
--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;
GO
--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
--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
    JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID=o.SalesOrderID
        AND ProductID BETWEEN 700 and 800
        AND OrderDate >= CONVERT(datetime,'05/01/2002',101)
GROUP BY OrderDate, ProductID
ORDER BY Rev DESC;
GO
--This query can use the above indexed view.
SELECT  OrderDate, SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Rev
FROM Sales.SalesOrderDetail AS od
    JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID=o.SalesOrderID
        AND DATEPART(mm,OrderDate)= 3
        AND DATEPART(yy,OrderDate) = 2002
GROUP BY OrderDate
ORDER BY OrderDate ASC;
GO