title: "sp_createstats (Transact-SQL) | Microsoft Docs" ms.custom: "" ms.date: "03/14/2017" ms.prod: "sql" ms.prod_service: "database-engine, sql-database" ms.service: "" ms.component: "system-stored-procedures" ms.reviewer: "" ms.suite: "sql" ms.technology:
- "database-engine" ms.tgt_pltfrm: "" ms.topic: "language-reference" f1_keywords:
- "sp_createstats_TSQL"
- "sp_createstats" dev_langs:
- "TSQL" helpviewer_keywords:
- "sp_createstats" ms.assetid: 8204f6f2-5704-40a7-8d51-43fc832eeb54 caps.latest.revision: 47 author: "edmacauley" ms.author: "edmaca" manager: "craigg" ms.workload: "Inactive" monikerRange: "= azuresqldb-current || >= sql-server-2016 || = sqlallproducts-allversions"
[!INCLUDEtsql-appliesto-ss2008-asdb-xxxx-xxx-md]
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).
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.
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.
The following example creates single-column statistics on all eligible columns in the current database.
EXEC sp_createstats;
GO
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
Statistics
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)