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

This article was previously published under Q296189
This article has been archived. It is offered "as is" and will no longer be updated.
BUG #: 101576 (SQLBUG_70)
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.
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.
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 theMicrosoft Knowledge Base:
301511 INF: How to Obtain the Latest SQL Server 7.0 Service Pack
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.
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 2Dec 27 2Dec 27 2Dec 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.
statistics density

Article ID: 296189 - Last Review: 10/23/2013 18:28:20 - Revision: 4.1

  • Microsoft SQL Server 7.0 Service Pack 3
  • kbnosurvey kbarchive kbbug kbfix KB296189