CREATE STATISTICS (Transact-SQL)

針對資料表或索引檢視表的一個或多個資料行建立查詢最佳化統計資料，包括篩選的統計資料。對於大部分查詢而言，查詢最佳化工具已經產生高品質查詢計畫的必要統計資料。不過，在少數情況下，您必須使用 CREATE STATISTICS 來建立其他統計資料或修改查詢設計，以便改善查詢效能。

對於從定義完善的資料子集中選取的查詢而言，篩選的統計資料可以改善查詢效能。篩選的統計資料會在 WHERE 子句中使用篩選述詞來選取統計資料中所含的資料子集。CREATE STATISTICS 可以使用 tempdb 來排序資料列的範例，以便建立統計資料。

如需有關統計資料的詳細資訊，包括使用 CREATE STATISTICS 的時機，請參閱＜使用統計資料來改善查詢效能＞。

Transact-SQL 語法慣例

語法

CREATE STATISTICS statistics_name 
ON { table_or_indexed_view_name } ( column [ ,...n ] ) 
    [ WHERE <filter_predicate> ]
    [ WITH 
        [ [ FULLSCAN 
          | SAMPLE number { PERCENT | ROWS } 
          | STATS_STREAM = stats_stream ] [ , ] ] 
        [ NORECOMPUTE ] 
    ] ;

<filter_predicate> ::= 
    <conjunct> [AND <conjunct>]

<conjunct> ::=
    <disjunct> | <comparison>

<disjunct> ::=
        column_name IN (constant ,…)

<comparison> ::=
        column_name <comparison_op> constant

<comparison_op> ::=
    IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !<

引數

• statistics_name
  這是要建立之統計資料的名稱。

• table_or_indexed_view_name
  這是要建立統計資料之資料表或索引檢視表的名稱。只要指定限定的資料表名稱，就可以在另一個資料庫的資料表或索引檢視表上建立統計資料。

• column [ ,…n]
  指定要建立統計資料的索引鍵資料行或索引鍵資料行清單。您可以指定任何可指定為索引鍵資料行的資料行，但下列例外狀況除外：

  • 您無法指定 xml、全文檢索和 FILESTREAM 資料行。

  • 只有在 ARITHABORT 和 QUOTED_IDENTIFIER 資料庫設定為 ON 時，才能指定計算資料行。

  • 如果 CLR 使用者定義型別可支援二進位排序，您可以指定這個類型的資料行。如果方法標示為具決定性，就能指定從使用者定義型別資料行中定義為方法引動過程的計算資料行。如需有關建立 CLR 使用者定義型別資料行的詳細資訊，請參閱＜使用 CLR 使用者定義型別＞。

• WHERE <filter_predicate>
  指定運算式，以便選取在建立統計資料物件時要包含的資料列子集。使用篩選述詞所建立的統計資料稱為篩選的統計資料。篩選述詞會使用簡單比較邏輯，而且無法參考計算資料行、UDT 資料行、空間資料類型資料行或 hierarchyID 資料類型資料行。比較運算子不允許使用 NULL 常值的比較。請改用 IS NULL 和 IS NOT NULL 運算子。

  下面是一些 Production.BillOfMaterials 資料表之篩選述詞的範例：

  WHERE StartDate > '20040101' AND EndDate <= '20040630'

  WHERE ComponentID IN (533, 324, 753)

  WHERE StartDate IN ('20040404', '20040905') AND EndDate IS NOT NULL

  如需有關篩選述詞的詳細資訊，請參閱＜篩選索引設計指導方針＞。

• FULLSCAN
  掃描資料表或索引檢視表中的所有資料列，藉以計算統計資料。FULLSCAN 和 SAMPLE 100 PERCENT 的結果相同。FULLSCAN 不能搭配 SAMPLE 選項一起使用。

• SAMPLE number { PERCENT | ROWS }
  指定當查詢最佳化工具建立統計資料時，要在資料表或索引檢視表中使用的近似百分比或資料列數目。如果使用 PERCENT，number 可以是從 0 到 100；如果使用 ROWS，number 則可以是從 0 到總資料列數目。查詢最佳化工具所取樣的實際百分比或資料列數目可能會與指定的百分比或數目不符。例如，查詢最佳化工具會掃描資料頁面上的所有資料列。

  在特殊情況下，根據預設取樣的查詢計畫並非最佳化，此時 SAMPLE 便非常有用。通常，查詢最佳化工具已經會依預設使用取樣並決定具有統計價值的取樣大小，因此不需要使用 SAMPLE 便可以建立高品質的查詢計畫。

  SAMPLE 不能和 FULLSCAN 選項一起使用。如果 SAMPLE 或 FULLSCAN 都未指定，查詢最佳化工具會依預設使用取樣資料並計算取樣大小。

  我們建議您不要指定 0 PERCENT 或 0 ROWS。將 PERCENT 或 ROWS 指定為 0 時，雖會建立統計資料物件，但是不會包含統計資料。

• NORECOMPUTE
  針對 statistics_name 停用自動統計資料更新選項 AUTO_STATISTICS_UPDATE。如果您指定了這個選項，查詢最佳化工具就會針對 statistics_name 完成任何進行中的統計資料更新並停用未來的更新。

  若要重新啟用統計資料更新，請使用 DROP STATISTICS 來移除統計資料，然後再執行不含 NORECOMPUTE 選項的 CREATE STATISTICS。

  注意
  使用這個選項可能會產生次佳查詢計畫。我們建議您盡量少用這個選項，而且只有合格的系統管理員可以使用。

  如需有關 AUTO_STATISTICS_UPDATE 選項的詳細資訊，請參閱＜ALTER DATABASE SET 選項 (Transact-SQL)＞。如需有關停用並重新啟用統計資料更新的詳細資訊，請參閱＜使用統計資料來改善查詢效能＞。

• STATS_STREAM **=**stats_stream
  僅供參考之用。不支援。我們無法保證未來的相容性。

備註

您最多可以針對每個統計資料物件列出 32 個資料行。

使用 CREATE STATISTICS 的時機

如需有關使用 CREATE STATISTICS 之時機的詳細資訊，請參閱＜使用統計資料來改善查詢效能＞。

篩選統計資料的參考相依性

sys.sql_expression_dependencies 目錄檢視會將篩選統計資料述詞中的每個資料行當做參考相依性來追蹤。請在建立篩選統計資料之前先考慮要在資料表資料行上執行的作業，因為在篩選統計資料述詞中定義的資料表資料行是無法卸除、重新命名或變更定義的。

權限

需要 ALTER TABLE 權限、使用者必須是資料表或索引檢視表的擁有者，或者使用者必須是其中一個 db_ddladmin 固定資料庫角色的成員。

範例

A. 搭配 SAMPLE 數目 PERCENT 使用 CREATE STATISTICS

下列範例會建立 ContactMail1 統計資料，其方式是使用 AdventureWorks2008R2 資料庫之 Person 資料表內，BusinessEntityID 和 EmailAddress 資料行的 5% 隨機取樣。

USE AdventureWorks2008R2;
GO
CREATE STATISTICS ContactMail1
    ON Person.Person (BusinessEntityID, EmailPromotion)
    WITH SAMPLE 5 PERCENT;

B. 搭配 FULLSCAN 和 NORECOMPUTE 使用 CREATE STATISTICS

下列範例會針對 Person 資料表的 BusinessEntityID 和 EmailAddress 資料行中的所有資料列來建立 ContactMail2 統計資料，且會停用統計資料的自動重新計算。

CREATE STATISTICS NamePurchase
    ON AdventureWorks2008R2.Person.Person (BusinessEntityID, EmailPromotion)
    WITH FULLSCAN, NORECOMPUTE;

C. 使用 CREATE STATISTICS 來建立篩選的統計資料

下列範例會建立篩選的統計資料 ContactPromotion1。Database Engine 會取樣百分之 50 的資料，然後選取 EmailPromotion 等於 2 的所有資料列。

USE AdventureWorks2008R2;
GO
IF EXISTS (SELECT name FROM sys.stats
    WHERE name = N'ContactPromotion1'
    AND object_id = OBJECT_ID(N'Person.Person'))
DROP STATISTICS Person.Person.ContactPromotion1
GO
CREATE STATISTICS ContactPromotion1
    ON Person.Person (BusinessEntityID, LastName, EmailPromotion)
WHERE EmailPromotion = 2
WITH SAMPLE 50 PERCENT;
GO

請參閱

參考

UPDATE STATISTICS (Transact-SQL)

sp_updatestats (Transact-SQL)

DBCC SHOW_STATISTICS (Transact-SQL)

DROP STATISTICS (Transact-SQL)

sys.stats (Transact-SQL)

sys.stats_columns (Transact-SQL)

概念

使用統計資料來改善查詢效能