MS SQL统计信息浅析下篇

   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