Specifically I'm most interested in:

  • The 'All density' number in the density vector
  • Numbers in the histogram

E.g. are they floats / decimals? What precision / scale / length do they have?

The motivation for this is that I think I might be seeing rounding errors, so would like to know how precisely we should expect cardinality estimations to match with these stats.

I'd be happy for an answer for any version of SQL Server, but 2012 and 2014 are most relevant to me.


Below are the field names and datatypes of each of the result sets as reported by SQL Server (I tested on SQL Server 2012, 2014, and 2016, though I would expect these to remain consistent across SQL Server versions):


[Name]                  NVARCHAR(128)
[Updated]               NVARCHAR(20)
[Rows]                  BIGINT
[Rows Sampled]          BIGINT
[Steps]                 SMALLINT
[Density]               REAL
[Average key length]    REAL
[String Index]          NCHAR(3)
[Filter Expression]     NVARCHAR(MAX)
[Unfiltered Rows]       BIGINT


[All density]           REAL
[Average Length]        REAL
[Columns]               NVARCHAR(4000)


[RANGE_HI_KEY]          INT -- Datatype varies based on datatype of first key column
[RANGE_ROWS]            REAL
[EQ_ROWS]               REAL

To be clear, these datatypes are the result set datatypes from the DBCC commands; there is no indication of the actual datatypes used to store that info. However, it seems reasonable to assume that the datatypes of the result set are the same as those used the store the info.

Also, for anyone curious as to how I was able to determine these datatypes: I got the info from a SQLCLR stored procedure I created as part of the SQL# library. It is called DB_DescribeResultSets and is similar to sys.dm_exec_describe_first_result_set but handles multiple result sets, temp tables, dynamic SQL, etc (because it runs the query instead of just parsing it). The DB_DescribeResultSets stored procedure is not available in the Free version, though. However, you could do the same thing in any .NET app. It just requires running a query through a SqlDataReader and then using the GetSchemaTable method to get the result set schema.

I ran DB_DescribeResultSets as follows:

EXEC SQL#.DB_DescribeResultSets
  @TheQuery = N'DBCC SHOW_STATISTICS(N''msdb.dbo.sysjobs'',
  @RowNumberToGetValuesFrom = 1,
  @ResultSetNumberToDescribe = 0, -- 0 = all result sets
  @ShowHiddenFields = 1,
  @ResultSet = ''; -- this is an XML OUTPUT param and can't have a default in SQLCLR

There is no right or wrong data type that you should strictly follow. Choose the one that does not give you errors or truncation.

Refer to Kalen's blog on Accessing Distribution Statistics.

So to answer your question :

The 'All density' number in the density vector --> should be numeric(10,8)

Numbers in the histogram should be as

RANGE_HI_KEY sql_variant, 
RANGE_ROWS bigint, 
EQ_ROWS bigint, 

Worth mentioning Aaron Bertrand's excellent answer to Where are Statistics physically stored in SQL Server?

  • "Choose the one..." - I don't get a choice, do I? Isn't this a property of SQL Server?
    – alksdjg
    Oct 27 '15 at 21:33
  • @alksdjg I just meant that you can use smallint or int or numeric or decimal. Some datatypes, you dont have a choice :-)
    – Kin Shah
    Oct 27 '15 at 21:34
  • I'm not asking how I should read these values in, I'm asking how they are stored in SQL Server, which I don't believe I have a choice over, is that wrong?
    – alksdjg
    Oct 27 '15 at 21:37
  • Also, I can see many example of values in the histogram having decimal places, wouldn't that conflict with bigint?
    – alksdjg
    Oct 27 '15 at 21:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.