Export (0) Print
Expand All
Expand Minimize
This topic has not yet been rated - Rate this topic

sp_createstats (Transact-SQL)

Calls the CREATE STATISTICS statement to create single-column statistics on columns that are not already the first column in a statistics object. Creating single-column statistics increases the number of histograms, which can improve cardinality estimates, query plans, and query performance. The first column of a statistics object has a histogram; other columns do not have a histogram.

sp_createstats is useful for applications such as benchmarking when query execution times are critical and cannot wait for the query optimizer to generate single-column statistics. In most cases, it is not necessary to use sp_createstats; the query optimizer generates single-column statistics as necessary to improve query plans when the AUTO_CREATE_STATISTICS option is on.

For more information about statistics, see Statistics. For more information about generating single-column statistics, see the AUTO_CREATE_STATISTICS option in ALTER DATABASE SET Options (Transact-SQL).

Topic link icon Transact-SQL Syntax Conventions

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

Creates statistics only on columns that are in an existing index and are not the first column in any index definition. indexonly is char(9). The default is NO.

[ @fullscan= ] 'fullscan'

Uses the CREATE STATISTICS statement with the FULLSCAN option. fullscan is char(9). The default is NO.

[ @norecompute= ] 'norecompute'

Uses the CREATE STATISTICS statement with the NORECOMPUTE option. norecompute is char(12). The default is NO.

0 (success) or 1 (failure)

Each new statistics object has the same name as the column it is created on.

sp_createstats does not create or update statistics on columns that are the first column in an existing statistics object; This includes the first column of statistics created for indexes, columns with single-column statistics generated with AUTO_CREATE_STATISTICS option, and the first column of statistics created with the CREATE STATISTICS statement. sp_createstats does not create statistics on the first columns of disabled indexes unless that column is used in another enabled index. sp_createstats does not create statistics on tables with a disabled clustered index.

When the table contains a column set, sp_createstats does not create statistics on sparse columns. For more information about column sets and sparse columns, see Use Column Sets and Use Sparse Columns.

Requires membership in the db_owner fixed database role.

A. Create single-column statistics on all eligible columns

The following example creates single-column statistics on all eligible columns in the current database.

EXEC sp_createstats;
GO

B. Create single-column statistics on all eligible index columns

The following example creates single-column statistics on all eligible columns that are already in an index and are not the first column in the index.

EXEC sp_createstats 'indexonly';
GO
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.