dm_db_stats_histogram missing row when column/histogram step contains NULL
When statistics on a nullable column contain a NULL sys.dmdbstatshistogram will not return a row for the corresponding histogram step (when RANGEHIKEY is a NULL). DBCC SHOWSTATISTICS returns all steps including the aforementioned missing one. Without all the histogram levels dmdbstats_histogram is not usable.
I can confirm this behaviour with INT and VARCHAR columns, I haven't tested other data types). I have attached the reproduction code and a screenshot. Any questions please let me know. Thank you.
SQL Version: 14.0.3025
This makes automation and analysis using the DMOs impossible. Back to dynamic SQL.
Discovered this for a column where the vast majority of data was NULL. Very confusing!
(Microsoft SQL Server 2017 (RTM-CU11) (KB4462262) - 14.0.3038.14 (X64) )
Gary Harding commented
Agree, the missing histogram step for NULL values contains useful information in the equal_rows column.
Without it, the DMO is not a complete replacement for DBCC SHOW_STATISTICS, which I imagine was the intention.