Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All
Expand Minimize

sp_createstats (Transact-SQL)

Applies To: SQL Server 2014, SQL Server 2016 Preview

Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).

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).

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database.

Topic link icon Transact-SQL Syntax Conventions

sp_createstats 
    [   [ @indexonly =   ] { 'indexonly'   | 'NO' } ] 
    [ , [ @fullscan =    ] { 'fullscan'    | 'NO' } ] 
    [ , [ @norecompute = ] { 'norecompute' | 'NO' } ]
    [ , [ @incremental = ] { 'incremental' | 'NO' } ]

[ @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.

[ @incremental= ] 'incremental'

Uses the CREATE STATISTICS statement with the INCREMENTAL = ON option. Incremental is char(12). The default is NO.

Applies to: SQL Server (SQL Server 2014 through current version).

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

Community Additions

ADD
Show:
© 2015 Microsoft