Share via

CREATE STATISTICS (Transact-SQL)

  • Article

Creates a histogram and associated density groups (collections) over the supplied column or set of columns of a table or indexed view. String summary statistics are also created on statistics built on char, varchar, varchar(max), nchar, nvarchar, nvarchar(max), text, and ntext columns. The query optimizer uses this statistical information to choose the most efficient plan for retrieving or updating data. Up-to-date statistics allow the optimizer to accurately assess the cost of different query plans, and choose a high-quality plan. For more information about statistics in SQL Server 2005, see Statistics Used by the Query Optimizer in Microsoft SQL Server 2005 at Microsoft TechNet.

Topic link iconTransact-SQL Syntax Conventions

Syntax

CREATE STATISTICS statistics_name 
ON { table | view } ( column [ ,...n ] ) 
    [ WITH 
        [ [ FULLSCAN 
          | SAMPLE number { PERCENT | ROWS } 
          | STATS_STREAM = stats_stream ] [ , ] ] 
        [ NORECOMPUTE ] 
    ] ;

Arguments

  • statistics_name
    Is the name of the statistics group to create. Statistics names must comply with the rules for identifiers and must be unique to the table or view on which they are created.
  • table
    Is the name of the table on which to create the named statistics. Table names must comply with the rules for identifiers. table is the table with which column is associated. Specifying the owner name of the table is optional. Statistics can be created on tables in another database by specifying a qualified table name.
  • view
    Is the name of the view on which to create the named statistics. A view must have a clustered index before statistics can be created on it. View names must comply with the rules for identifiers. view is the view with which column is associated. Specifying the owner name of the view is optional. Statistics can be created on views in another database by specifying a qualified view name.

  • column
    Is the column or set of columns on which to create statistics. Any column that can be specified as an index key can also be specified for statistics, with the following exceptions:

    • xml columns cannot be specified.
    • The maximum allowable size of the combined column values can exceed the 900-byte limit that is imposed on the index key value.

    Computed columns can be specified only if the ARITHABORT and QUOTED_IDENTIFIER database options are set to ON. CLR user-defined type columns can be specified if the type supports binary ordering. Computed columns defined as method invocations off a user-defined type column can be specified if the methods are marked deterministic. For more information about creating CLR user-defined type columns, see Working with CLR User-defined Types.

  • FULLSCAN
    Specifies that all rows in table or view should be read to gather the statistics. Specifying FULLSCAN provides the same behavior as SAMPLE 100 PERCENT. This option cannot be used with the SAMPLE option.

  • SAMPLE number { PERCENT | ROWS }
    Specifies that a percentage, or a specified number of rows, of the data should be read by using random sampling to gather the statistics. number must be an integer. If PERCENT is specified, number should be from 0 through 100; if ROWS is specified, number can be from 0 to the n total rows.

    The SQL Server 2005 Database Engine makes sure a minimum number of values are sampled to ensure useful statistics. If using the PERCENT, ROWS, or number option causes too few rows to be sampled, the Database Engine automatically corrects the sampling based on the number of existing rows in the table or view. At least 1,000 data pages, approximately, are sampled. If using the PERCENT, ROWS, or number option causes more values than are needed for a useful sample, the Database Engine tries to match the requested sample amount. However, because samples are taken by scanning complete data pages, the actual sample size may not exactly match the amount specified. When 0 PERCENT or ROWS is specified, the result is an empty statistics set.

    SAMPLE cannot be used with the FULLSCAN option. If SAMPLE or FULLSCAN is not specified, an automatic sample is computed by the Database Engine.

  • NORECOMPUTE
    Specifies that the Database Engine should not automatically recompute statistics. If this option is specified, the Database Engine continues to use previously created (old) statistics, even as the data changes. The statistics are not automatically updated and maintained by the Database Engine. This may produce suboptimal plans.

    Warning

    We recommend the limited use of this option, and then only by a qualified system administrator.

  • STATS_STREAM **=**stats_stream
    This syntax is for internal use only and is not supported. Microsoft reserves the right to change this syntax at any time.

Remarks

Only the table owner can create statistics on that table. The owner of a table can create a statistics group (collection) at any time, whether or not there is data in the table.

If the AUTO_UPDATE_STATISTICS database option is set to ON (this is the default setting) and the NORECOMPUTE clause is not specified, the Database Engine will automatically update any statistics that are manually created.

CREATE STATISTICS can be executed on an indexed view. Statistics on indexed views are used by the optimizer only if the view is directly referenced in the query and the NOEXPAND hint is specified for the view. Otherwise, statistics are derived from the underlying tables before the indexed view is substituted into the query plan. This substitution is supported only on Microsoft SQL Server 2005 Enterprise and Developer editions.

Permissions

Requires ALTER permission on the table or view.

Examples

A. Using CREATE STATISTICS with SAMPLE number PERCENT

The following example creates the ContactMail1 statistics group (collection). This calculates random sampling statistics on five percent of the ContactID and EmailAddress columns of the Contact table of the AdventureWorks database.

USE AdventureWorks;
GO
CREATE STATISTICS ContactMail1
    ON Person.Contact (ContactID, EmailAddress)
    WITH SAMPLE 5 PERCENT;

B. Using CREATE STATISTICS with FULLSCAN and NORECOMPUTE

The following example creates the ContactMail2 statistics group (collection). This calculates statistics for all rows in the ContactID and EmailAddress columns of the Contact table and disables automatic recomputing of statistics.

CREATE STATISTICS NamePurchase
    ON AdventureWorks.Person.Contact (ContactID, EmailAddress)
    WITH FULLSCAN, NORECOMPUTE;

See Also

Reference

ALTER DATABASE (Transact-SQL)
CREATE INDEX (Transact-SQL)
DBCC SHOW_STATISTICS (Transact-SQL)
DROP STATISTICS (Transact-SQL)
sys.stats (Transact-SQL)
sys.stats_columns (Transact-SQL)
sp_autostats (Transact-SQL)
sp_createstats (Transact-SQL)
UPDATE STATISTICS (Transact-SQL)
EVENTDATA (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance