CREATE STATISTICS (T-SQL)

Creates a histogram and associated density groups (collection) over the supplied column or set of columns.

Syntax

CREATE STATISTICS statistics_name ON table (column [,...n])
    [    WITH
        [ [ FULLSCAN
            | SAMPLE number PERCENT ] [,] ]
        [ NORECOMPUTE]
    ]

Arguments

statistics_name: Is the name of the statistics group to create. Statistics names must conform to the rules for identifiers.
table: Is the name of the table on which to create the named statistics. Table names must conform to the rules for identifiers. table is the table with which the column is associated. Specifying the table owner name is optional. Statistics can be created on tables in another database by specifying a qualified database name.
column: Is the column or set of columns on which to create statistics. Computed columns and columns of the ntext, text, or image data types cannot be specified as statistics columns.
n: Is a placeholder indicating that multiple columns can be specified.
FULLSCAN: Specifies that all rows in table 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: Specifies that a percentage of the data should be read using random sampling to gather the statistics. number can be fractional. This option cannot be used with the FULLSCAN option. If no SAMPLE or FULLSCAN option is given, an automatic sample fraction is computed by SQL Server.
NORECOMPUTE: Specifies that automatic recomputation of the statistics should be disabled. For more information, see UPDATE STATISTICS and sp_autostats. If this option is specified, SQL Server continues to use previously created (“old”) statistics even as the data changes. The statistics are not automatically updated and maintained by SQL Server, which may produce suboptimal plans.

Warning It is recommended that this option be used rarely and only by a trained system administrator.

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.

Permissions

CREATE STATISTICS permissions default to the owner of the table.

Examples

A. Use CREATE STATISTICS with SAMPLE number PERCENT

This example creates the names statistics group (collection), which calculates random sampling statistics on 5 percent of the CompanyName and ContactName columns of the Customers table.

CREATE STATISTICS names

ON Customers (CompanyName, ContactName)

WITH SAMPLE 5 PERCENT

GO

B. Use CREATE STATISTICS with FULLSCAN and NORECOMPUTE

This example creates the names statistics group (collection), which calculates statistics for all rows in the CompanyName and ContactName columns of the Customers table and disables automatic recomputation of statistics.