CREATE STATISTICS (Transact-SQL)

Article
08/27/2013

Crée des statistiques d'optimisation de requête, notamment des statistiques filtrées, sur une ou plusieurs colonnes d'une table ou vue indexée. Pour la plupart des requêtes, l'optimiseur de requête génère déjà les statistiques utiles à un plan de requête de haute qualité ; dans certains cas, vous devez créer des statistiques supplémentaires avec CREATE STATISTICS ou modifier la conception des requêtes pour améliorer les performances des requêtes.

Les statistiques filtrées peuvent améliorer les performances des requêtes qui effectuent des sélections dans des sous-ensembles bien définis de données. Elles utilisent un prédicat de filtre dans la clause WHERE pour sélectionner le sous-ensemble de données qui est inclus dans les statistiques. CREATE STATISTICS peut utiliser tempdb pour trier l'exemple de lignes pour la génération de statistiques.

Pour plus d'informations sur les statistiques, notamment sur le moment où CREATE STATISTICS doit être utilisé, consultez Statistiques.

Icône Lien de rubrique Conventions de syntaxe Transact-SQL

Syntaxe

CREATE STATISTICS statistics_name 
ON { table_or_indexed_view_name } ( column [ ,...n ] ) 
    [ WHERE <filter_predicate> ]
    [ WITH 
        [ [ FULLSCAN 
          | SAMPLE number { PERCENT | ROWS } 
          | STATS_STREAM = stats_stream ] [ , ] ] 
        [ NORECOMPUTE ] 
    ] ;

<filter_predicate> ::= 
    <conjunct> [AND <conjunct>]

<conjunct> ::=
    <disjunct> | <comparison>

<disjunct> ::=
        column_name IN (constant ,…)

<comparison> ::=
        column_name <comparison_op> constant

<comparison_op> ::=
    IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !<

Arguments

statistics_name
Nom des statistiques à créer.
table_or_indexed_view_name
Nom de la table ou vue indexée pour laquelle les statistiques doivent être créées. Vous pouvez créer les statistiques sur des tables ou des vues indexées tirées d'une autre base de données en spécifiant un nom de table complet.
column [ ,…n]
Spécifie la colonne clé ou la liste de colonnes clés pour lesquelles créer des statistiques. Vous pouvez indiquer comme base de calcul des statistiques toute colonne pouvant être spécifiée en tant que colonne de clé d'index, sauf pour les exceptions suivantes :
- Les colonnes Xml, de texte intégral et FILESTREAM ne peuvent pas être spécifiées.
- Les colonnes calculées ne peuvent être indiquées que si les paramètres de base de données ARITHABORT et QUOTED_IDENTIFIER ont la valeur ON.
- Toute colonne de type CLR définie par l'utilisateur peut être spécifiée si son type prend en charge l'ordre de tri binaire. Les colonnes calculées définies en tant qu'appels à des méthodes d'une colonne de type défini par l'utilisateur peuvent être précisées si les méthodes en question sont marquées comme étant déterministes.
WHERE <filter_predicate>
Spécifie une expression permettant de sélectionner un sous-ensemble des lignes à inclure lors de la création de l'objet de statistiques. Les statistiques créées avec un prédicat de filtre sont appelées des statistiques filtrées. Le prédicat de filtre utilise une logique de comparaison simple et ne peut pas faire référence à une colonne calculée, une colonne UDT, une colonne de type de données spatiales ou une colonne de type de données hierarchyID. Les comparaisons à l'aide de littéraux NULL ne sont pas autorisées avec les opérateurs de comparaison. Utilisez les opérateurs IS NULL et IS NOT NULL à la place.

Voici quelques exemples de prédicats de filtre pour la table Production.BillOfMaterials :

WHERE StartDate > '20000101' AND EndDate <= '20000630'

WHERE ComponentID IN (533, 324, 753)

WHERE StartDate IN ('20000404', '20000905') AND EndDate IS NOT NULL

Pour plus d'informations sur les prédicats de filtre, consultez Créer des index filtrés.
FULLSCAN
Calcule les statistiques en analysant toutes les lignes dans la table ou vue indexée. FULLSCAN et SAMPLE 100 PERCENT ont les mêmes résultats. Cette option ne peut pas être utilisée avec l'option SAMPLE.
SAMPLE number { PERCENT | ROWS }
Spécifie le pourcentage ou nombre de lignes approximatif dans la table ou vue indexée devant être utilisé par l'optimiseur de requête lors de la création des statistiques. Pour PERCENT, number peut être compris entre 0 et 100 et pour ROWS, number peut être compris entre 0 et le nombre total de lignes. Le pourcentage ou nombre de lignes réel échantillonné par l'optimiseur de requête peut ne pas correspondre au pourcentage ou nombre spécifié. Par exemple, l'optimiseur de requête analyse toutes les lignes d'une page de données.

SAMPLE est utile pour les cas spéciaux dans lesquels le plan de requête, basé sur l'échantillonnage par défaut, n'est pas optimal. Dans la plupart des situations, il n'est pas nécessaire de spécifier SAMPLE, car l'optimiseur de requête utilise déjà l'échantillonnage et détermine la taille d'échantillon statistiquement significative par défaut, comme requis pour créer des plans de requêtes de haute qualité.

SAMPLE ne peut pas être utilisé avec l'option FULLSCAN. Lorsque ni SAMPLE ni FULLSCAN n'est spécifié, l'optimiseur de requête utilise les données échantillonnées et calcule la taille d'échantillon par défaut.

Il est déconseillé de spécifier 0 PERCENT ou 0 ROWS. Lorsque 0 PERCENT ou ROWS est spécifié, l'objet de statistiques est créé mais ne contient pas de données de statistiques.

NORECOMPUTE
Désactive l'option de mise à jour automatique des statistiques, AUTO_UPDATE_STATISTICS, pour statistics_name. Si cette option est spécifiée, l'optimiseur de requête effectue les mises à jour des statistiques en cours d'exécution pour statistics_name et désactive les mises à jour ultérieures.

Pour réactiver la mise à jour des statistiques, supprimez les statistiques à l'aide de DROP STATISTICS, puis exécutez CREATE STATISTICS sans l'option NORECOMPUTE.

Attention
L'utilisation de cette option peut produire des plans de requêtes non optimaux. Nous recommandons d'utiliser cette option avec parcimonie et uniquement par un administrateur système qualifié.

Pour plus d'informations sur l'option AUTO_UPDATE_STATISTICS, consultez Options SET de ALTER DATABASE (Transact-SQL). Pour plus d'informations sur la désactivation et la réactivation des mises à jour des statistiques, consultez Statistiques.

STATS_STREAM **=**stats_stream
Identifié à titre d'information uniquement. Non pris en charge. La compatibilité future n'est pas garantie.

Notes

Vous pouvez afficher jusqu'à 32 colonnes par objet de statistiques.

Quand utiliser CREATE STATISTICS

Pour plus d'informations sur le moment où CREATE STATISTICS doit être utilisé, consultez Statistiques.

Dépendances des références pour les statistiques filtrées

L'affichage catalogue sys.sql_expression_dependencies suit chaque colonne du prédicat de statistiques filtrées en tant que dépendance de référence. Réfléchissez aux opérations que vous effectuez sur les colonnes de table avant de créer des statistiques filtrées car vous ne pouvez pas supprimer, renommer, ni modifier la définition d'une colonne de table qui est définie dans un prédicat de statistiques filtrées.

Autorisations

L'utilisateur doit disposer de l'autorisation ALTER TABLE, ou être le propriétaire de la table ou de la vue indexée, ou être membre du rôle de base de données fixe db_ddladmin.

Exemples

A.Utilisation de CREATE STATISTICS avec SAMPLE number PERCENT

L'exemple suivant crée les statistiques ContactMail1, à l'aide d'un exemple aléatoire de 5 pour cent des colonnes BusinessEntityID et EmailPromotion de la table Contact de la base de données AdventureWorks.

USE AdventureWorks2012;
GO
CREATE STATISTICS ContactMail1
    ON Person.Person (BusinessEntityID, EmailPromotion)
    WITH SAMPLE 5 PERCENT;

B.Utilisation de CREATE STATISTICS avec FULLSCAN et NORECOMPUTE

L'exemple suivant crée les statistiques ContactMail2 pour toutes les lignes des colonnes BusinessEntityID et EmailPromotion de la table Contact et désactive le recalcul automatique des statistiques.

CREATE STATISTICS NamePurchase
    ON AdventureWorks2012.Person.Person (BusinessEntityID, EmailPromotion)
    WITH FULLSCAN, NORECOMPUTE;

C.Utilisation de CREATE STATISTICS pour créer des statistiques filtrées

L'exemple suivant crée les statistiques filtrées ContactPromotion1. Le Moteur de base de données échantillonne 50 pour cent des données, puis sélectionne toutes les lignes pour lesquelles EmailPromotion est égal à 2.

USE AdventureWorks2012;
GO
IF EXISTS (SELECT name FROM sys.stats
    WHERE name = N'ContactPromotion1'
    AND object_id = OBJECT_ID(N'Person.Person'))
DROP STATISTICS Person.Person.ContactPromotion1
GO
CREATE STATISTICS ContactPromotion1
    ON Person.Person (BusinessEntityID, LastName, EmailPromotion)
WHERE EmailPromotion = 2
WITH SAMPLE 50 PERCENT;
GO