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

View products that this article applies to.

This article was previously published under Q296189

BUG #: 101576 (SQLBUG_70)

Expand all | Collapse all

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.

Back to the top

CAUSE

The code incorrectly uses the number of bytes needed to store the base datatype as the length at which to terminate the string representation of the value.

Back to the top

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 (http://support.microsoft.com/kb/301511/EN-US/ ) INF: How to Obtain the Latest SQL Server 7.0 Service Pack

Back to the top

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article. This problem was first corrected in Microsoft SQL Server 7.0 Service Pack 4.

Back to the top

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.

Back to the top