If I run this query on our system:

SELECT STATS_DATE(t.object_id, s.stats_id) AS stdate, * 
from    sys.stats s WITH (NOLOCK)
INNER JOIN sys.tables t WITH (NOLOCK) ON t.object_id = s.object_id
WHERE t.name LIKE 'mytable%'

I sometimes see that the stats_date function returns NULL. This should not be possible according to BOL, except if there is an error. It does not state which error.

The table is quite big, 11 billion rows. If I run:

UPDATE STATISTICS fct.mytable ( pk_mytable)

the stats_date comes back again as not null.

Why is the date null, and how can I prevent that?

It is only the stats for the primary key that is NULL.

If I run:

DBCC SHOW_STATISTICS ('fct.MyTable', 'IX_MyTableID') 

it comes back with the 3 result sets that I expect. If I run:

DBCC SHOW_STATISTICS ('fct.MyTable', 'PK_MyTable') 

It returns:

enter image description here


1 Answer 1


Per MSDN it can return null on error, although why you would experience an error with this I am not sure. Have you considered doing an outer apply to the sys.dm_db_stats_properties function to see if you get the same results?

SELECT  t.name ,
        s.object_id ,
        s.stats_id ,
        c.name ,
        sc.stats_column_id ,
        s.name ,
        sp.last_updated ,
        sp.rows_sampled ,
        sp.modification_counter ,
        sp.steps ,
FROM    [sys].[stats] AS [s]
        INNER JOIN sys.stats_columns sc ON s.stats_id = sc.stats_id
                                            AND s.object_id = sc.object_id
        INNER JOIN sys.columns c ON c.object_id = sc.object_id
                                    AND c.column_id = sc.column_id
        INNER JOIN sys.tables t ON c.object_id = t.object_id
        OUTER APPLY sys.dm_db_stats_properties([s].[object_id],
                                                [s].[stats_id]) AS [sp]
WHERE   t.name LIKE 'mytable%';

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