MS SQL統計信息淺析上篇對SQL SERVER 數據庫統計信息做了一個整體的介紹，隨著我對數據庫統計信息的不斷認識、理解，于是有了MS SQL統計信息淺析下篇。 下面是我對SQL Server統計信息的一些探討或認識，如有不對的地方，希望大家能夠指正。

觸發統計信息更新條件疑問

    關于這個觸發統計信息更新的條件。因為我在很多資料上看到過，例如Microsoft  SQL Server 企業級平臺管理實踐。 我自己上篇也是這樣解釋的。

   1：普通表上，觸發數據庫自動更新統計信息的條件

           1、 在一個空表中有數據的改動。

           2、 當統計信息創建時，表的行數只有500或以下，且后來統計對象中的引導列（統計信息的第一個字段數據）的更改次數大于500.

           3、 當表的統計信息收集時，超過了500行，且統計對象的引導列（統計信息的第一個字段數據）后來更改次數超過500+表總行數的20%時

    2：臨時表

          If the statistics object is defined on a temporary table, it is out of date as discussed above, except that there is an additional threshold for recomputation at 6 rows, with a test otherwise identical to test 2 in the PRevious list.。

    3： 表變量

            表變量沒有統計信息

官方資料http://msdn.microsoft.com/en-us/library/dd535534%28v=sql.100%29.aspx 也是這樣解釋的。

A statistics object is considered out of date in the following cases:

  If the statistics is defined on a regular table, it is out of date if:

1.        The table size has gone from 0 to >0 rows (test 1).
2.        The number of rows in the table when the statistics were gathered was 500 or less, and the colmodctr of the leading column of the statistics object has changed by more than 500 since then (test 2).
3.        The table had more than 500 rows when the statistics were gathered, and the colmodctr of the leading column of the statistics object has changed by more than 500 + 20% of the number of rows in the table when the statistics were gathered (test 3).

·         For filtered statistics, the colmodctr is first adjusted by the selectivity of the filter before these conditions are tested. For example, for filtered statistics with predicate selecting 50% of the rows, the colmodctr is multiplied by 0.5.

·         One limitation of the automatic update logic is that it tracks changes to columns in the statistics, but not changes to columns in the predicate. If there are many changes to the columns used in predicates of filtered statistics, consider using manual updates to keep up with the changes.

·         If the statistics object is defined on a temporary table, it is out of date as discussed above, except that there is an additional threshold for recomputation at 6 rows, with a test otherwise identical to test 2 in the previous list.

Table variables do not have statistics at all.

但是又一次我的實驗顯示不是那么一回事，有興趣的可以按照下面SQL語句試試，

CREATE TABLE TEST1

(

 ID      INT           ,

 NAME    VARCHAR(8)    ,

 CONSTRAINT PK_TEST1 PRIMARY KEY(ID)

)

GO