sp_createstats (Transact-SQL)sp_createstats (Transact-SQL)

適用於: 是SQL Server 否Azure SQL Database 否Azure Synapse Analytics (SQL DW) 否平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

呼叫CREATE STATISTICS語句,針對不是 STATISTICS 物件中第一個資料行的資料行建立單一資料行統計資料。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 對於效能評定等應用程式會很有用。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. 在大部分情況下,不需要使用 sp_createstats。當AUTO_CREATE_STATISTICS選項為 on 時,查詢最佳化工具會視需要產生單一資料行統計資料,以便改善查詢計劃。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. 如需產生單一資料行統計資料的詳細資訊,請參閱ALTER DATABASE SET Options (transact-sql)中的AUTO_CREATE_STATISTICS選項。For more information about generating single-column statistics, see the AUTO_CREATE_STATISTICS option in ALTER DATABASE SET Options (Transact-SQL).

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax

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

引數Arguments

[ @indexonly = ] 'indexonly'只在現有索引中的資料行上建立統計資料,而不是任何索引定義中的第一個資料行。[ @indexonly = ] 'indexonly' Creates statistics only on columns that are in an existing index and are not the first column in any index definition. indexonlychar (9)indexonly is char(9). 預設值是 NO。The default is NO.

[ @fullscan = ] 'fullscan'使用CREATE STATISTICS語句搭配FULLSCAN選項。[ @fullscan = ] 'fullscan' Uses the CREATE STATISTICS statement with the FULLSCAN option. fullscanchar (9)fullscan is char(9). 預設值是 NO。The default is NO.

[ @norecompute = ] 'norecompute'使用CREATE STATISTICS語句搭配NORECOMPUTE選項。[ @norecompute = ] 'norecompute' Uses the CREATE STATISTICS statement with the NORECOMPUTE option. norecomputechar (12)norecompute is char(12). 預設值是 NO。The default is NO.

[ @incremental = ] 'incremental'使用CREATE STATISTICS語句搭配增量 = ON選項。[ @incremental = ] 'incremental' Uses the CREATE STATISTICS statement with the INCREMENTAL = ON option. 增量char (12)Incremental is char(12). 預設值是 NO。The default is NO.

傳回碼值Return Code Values

0 (成功) 或 1 (失敗)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 不會建立或更新現有統計資料物件中第一個資料行的統計資料, 這包括針對索引所建立之統計資料的第一個資料行、以 AUTO_CREATE_STATISTICS 選項產生之單一資料行統計資料的資料行,以及使用 CREATE STATISTICS 語句所建立之統計資料的第一個資料行。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 不會在已停用索引的第一個資料行上建立統計資料,除非另一個已啟用的索引中使用該資料行。sp_createstats does not create statistics on the first columns of disabled indexes unless that column is used in another enabled index. sp_createstats 不會在具有已停用之叢集索引的資料表上建立統計資料。sp_createstats does not create statistics on tables with a disabled clustered index.

當此資料表包含資料行集時,sp_createstats 就不會建立疏鬆資料行的統計資料。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.

權限Permissions

需要 db_owner 固定資料庫角色中的成員資格。Requires membership in the db_owner fixed database role.

範例Examples

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

另請參閱See Also

統計資料 Statistics
(Transact-sql)建立統計資料 CREATE STATISTICS (Transact-SQL)
ALTER DATABASE SET 選項 (Transact-SQL) ALTER DATABASE SET Options (Transact-SQL)
DBCC SHOW_STATISTICS (Transact-SQL) DBCC SHOW_STATISTICS (Transact-SQL)
DROP STATISTICS (Transact-SQL) DROP STATISTICS (Transact-SQL)
UPDATE STATISTICS (Transact-SQL) UPDATE STATISTICS (Transact-SQL)
資料庫引擎預存程式 (Transact-sql) Database Engine Stored Procedures (Transact-SQL)
系統預存程序 (Transact-SQL)System Stored Procedures (Transact-SQL)