sp_createstats (Transact-SQL)
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Calls the CREATE STATISTICS statement to create single-column statistics on columns that aren't 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 don't have a histogram.
sp_createstats
is useful for applications such as benchmarking when query execution times are critical and can't wait for the query optimizer to generate single-column statistics. In most cases, it's 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 syntax conventions
Syntax
sp_createstats
[ [ @indexonly = ] 'indexonly' ]
[ , [ @fullscan = ] 'fullscan' ]
[ , [ @norecompute = ] 'norecompute' ]
[ , [ @incremental = ] 'incremental' ]
[ ; ]
Arguments
[ @indexonly = ] 'indexonly'
Creates statistics only on columns that are in an existing index and aren't the first column in any index definition. @indexonly is char(9), with a default of NO
.
[ @fullscan = ] 'fullscan'
Uses the CREATE STATISTICS statement with the FULLSCAN
option. @fullscan is char(9), with a default of NO
.
[ @norecompute = ] 'norecompute'
Uses the CREATE STATISTICS statement with the NORECOMPUTE
option. @norecompute is char(12), with a default of NO
.
[ @incremental = ] 'incremental'
Uses the CREATE STATISTICS statement with the INCREMENTAL = ON
option. @incremental is char(12), with a default of NO
.
Return code values
0
(success) or 1
(failure).
Result set
Each new statistics object has the same name as the column it's created on.
Remarks
sp_createstats
doesn't 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
doesn't create statistics on the first columns of disabled indexes unless that column is used in another enabled index. sp_createstats
doesn't create statistics on tables with a disabled clustered index.
When the table contains a column set, sp_createstats
doesn't create statistics on sparse columns. For more information about column sets and sparse columns, see Use column sets and Use 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 aren't the first column in the index.
EXEC sp_createstats 'indexonly';
GO