FIX: DBCC SHOW_STATISTICS Truncates Output for Fixed-Length Numeric Types (296189)

SYMPTOMS

Running DBCC SHOW_STATISTICS for an index or statistics on a fixed-length numeric type (such as tinyint, smallint, integer, real, float, money, smallmoney, numeric, datetime, or smalldatetime) may display truncated histogram steps. The display is truncated at the number of bytes used to store the value. For example, if the histogram is on an integer column, and a histogram step boundary falls on the value of 123456789, SHOW_STATISTICS shows the value as 1234, because an integer uses 4 bytes of storage.

RESOLUTION

To resolve this problem, obtain the latest service pack for Microsoft SQL Server 7.0. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

301511 INF: How to Obtain the Latest SQL Server 7.0 Service Pack

MORE INFORMATION

Consider the following example of a histogram on a datetime column, which requires 8 bytes of storage. If the value of '2000-12-27 12:55:11' were stored in a table, and that value was a histogram step, the DBCC SHOW_STATISTICS output might resemble the following:

Statistics for INDEX 'idx_test'.
Updated              Rows        Rows Sampled Steps       Density                  Average key length       
-------------------- ----------- ------------ ----------- ------------------------ ------------------- 
Apr 12 2001  1:33PM  10000       10000        295         0.0                      0.0

(1 row(s) affected)

All density              Columns                                                                                                                          
------------------------ ----------------------------------------------------------------------------- 
1.0                      id

(1 row(s) affected)

Steps                
-------------------- 
Dec 27 2
Dec 27 2
Dec 27 2
Dec 27 2
...

Note that when SQL Server converts this value to a string, the default format would be 'Dec 27 2000 12:55PM'. In generating the SHOW_STATISTICS output, this datetime value is converted to this string, and then truncated after 8 characters, which is the length needed to store the datetime datatype.

This problem is a display issue only. When the optimizer reads the histogram as part of optimizing a query, it uses the native datatypes from the histogram, not their string representation, and is therefore not affected by this problem.