Article ID: 296189
Article Last Modified on 6/29/2004
APPLIES TO
- Microsoft SQL Server 7.0 Service Pack 3
This article was previously published under Q296189
BUG #: 101576 (SQLBUG_70)
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.
Additional query words: statistics density
Keywords: kbbug kbfix KB296189
