0 out of 1 rated this helpful - 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 Using Statistics to Improve Query Performance. 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

Syntax

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

Arguments

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

Return Code Values

0 (success) or 1 (failure)

Result Sets

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

Remarks

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 Using Column Sets and Using Sparse Columns.

Permissions

Requires membership in the db_owner fixed database role.

Examples

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

Reference

CREATE STATISTICS (Transact-SQL)

ALTER DATABASE SET Options (Transact-SQL)

DBCC SHOW_STATISTICS (Transact-SQL)

DROP STATISTICS (Transact-SQL)

UPDATE STATISTICS (Transact-SQL)

Database Engine Stored Procedures (Transact-SQL)

System Stored Procedures (Transact-SQL)

Concepts

Using Statistics to Improve Query Performance

Change History

Updated content
Revisions throughout the document to improve accuracy.
Topic refers to new statistics content in the topic, Using Statistics to Improve Query Performance.

Did you find this helpful? Yes No

Not accurate

Not enough depth

Need more code examples

(1500 characters remaining)

Community Additions

ADD