Export (0) Print
Expand All
Expand Minimize

sp_createstats (Transact-SQL)

Creates single-column statistics for all eligible columns for all user tables and internal tables in the current database. The new statistic has the same name as the column where it is created.

Topic link icon Transact-SQL Syntax Conventions


sp_createstats [ [ @indexonly = ] 'indexonly' ] 
        [ , [ @fullscan = ] 'fullscan' ] 
    [ , [ @norecompute = ] 'norecompute' ]

[ @indexonly = ] 'indexonly'

Specifies that only the columns participating in an index should be considered for statistics creation. indexonly is char(9). The default is NO.

[ @fullscan = ] 'fullscan'

Specifies that the FULLSCAN option is used with CREATE STATISTICS. If fullscan is omitted, the SQL Server 2005 Database Engine performs a default sample scan. fullscan is char(9). The default is NO.

[ @norecompute = ] 'norecompute'

Specifies that automatic recomputation of statistics is disabled for the newly created statistics. norecompute is char(12). The default is NO.

0 (success) or 1 (failure)

Columns that already have statistics are not touched; for example, the first column of an index or a column with explicitly created statistics. A CREATE STATISTICS statement is executed for each column that satisfies the previous restrictions. FULLSCAN is executed if fullscan is specified.

Statistics are not created on columns that are the leading columns of disabled indexes. When indexonly is specified, statistics are not created on a column in a disabled nonclustered index, unless that column is also used in another enabled index. sp_createstats ignores tables with a disabled clustered index.

Requires membership in the db_owner fixed database role.

The following example creates statistics for all eligible columns for all user tables in the current database.

EXEC sp_createstats;

The following example creates statistics for only the columns that are participating in an index.

EXEC sp_createstats 'indexonly';

Community Additions

ADD
Show:
© 2014 Microsoft