Export (0) Print
Expand All
ABS
ALL
AND
ANY
AVG
bit
COS
COT
DAY
EXP
GO
IN
LEN
LOG
MAX
MIN
NOT
OR
PI
SET
SIN
STR
SUM
TAN
USE
VAR
Expand Minimize

CREATE STATISTICS

SQL Server 2000

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

See Also

CREATE INDEX

DBCC SHOW_STATISTICS

DROP STATISTICS

sp_autostats

sp_createstats

sp_dboption

UPDATE STATISTICS

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft