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

Article translations Article translations
Article ID: 296189 - View products that this article applies to.
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)
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.

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.

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

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.

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.

Properties

Article ID: 296189 - Last Review: October 23, 2013 - Revision: 4.1
APPLIES TO
  • Microsoft SQL Server 7.0 Service Pack 3
Keywords: 
kbnosurvey kbarchive kbbug kbfix KB296189

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com