What data type are the numbers in a SQL Server statistics object?

Question

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.

Answer 1

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):

DBCC SHOW_STATISTICS() WITH STAT_HEADER

[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

DBCC SHOW_STATISTICS() WITH DENSITY_VECTOR

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

DBCC SHOW_STATISTICS() WITH HISTOGRAM

[RANGE_HI_KEY]          INT -- Datatype varies based on datatype of first key column
[RANGE_ROWS]            REAL
[EQ_ROWS]               REAL
[DISTINCT_RANGE_ROWS]   BIGINT
[AVG_RANGE_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'',
                                     nc1);',
  @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

Answer 2

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, 
DISTINCT_RANGE_ROWS bigint, 
AVG_RANGE_ROWS bigint

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