CREATE STATISTICS
Creates a histogram and associated density groups (collections) over the supplied column or set of columns.
Syntax
CREATE STATISTICS statistics_name
ON { table | view } ( column [ ,...n ] )
[ WITH
[ [ FULLSCAN
| SAMPLE number { PERCENT | ROWS } ] [ , ] ]
[ 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.
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 conform to the rules for identifiers. view is the view with which the column is associated. Specifying the view owner name is optional. Statistics can be created on views 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 | ROWS }
Specifies that a percentage, or a specified number of rows, of the data should be read using random sampling to gather the statistics. number can be only an integer: if PERCENT, number should be from 0 through 100; if ROWS, number can be from 0 to the n total rows.
This option cannot be used with the FULLSCAN option. If no SAMPLE or FULLSCAN option is given, an automatic sample is computed by Microsoft® SQL Server™.
NORECOMPUTE
Specifies that automatic recomputation of the statistics should be disabled. 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.
CREATE STATISTICS can be executed on views with clustered index, or indexed views. 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, the statistics are derived from the underlying tables before the indexed view is substituted into the query plan. Such substitution is supported only on Microsoft SQL Server 2000 Enterprise and Developer Editions.
Permissions
CREATE STATISTICS permissions default to members of the sysadmin fixed server role and the db_ddladmin and db_owner fixed database roles and the table owner, and are not transferable.
Examples
A. Use CREATE STATISTICS with SAMPLE number PERCENT
This example creates the names statistics group (collection), which calculates random sampling statistics on five 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.
CREATE STATISTICS names
ON Northwind..Customers (CompanyName, ContactName)
WITH FULLSCAN, NORECOMPUTE
GO