30,000 maximum non-indexed column stats in a SQL Server database? << Breakin' the law, Breakin' the law...

**Update 2016 12 07**
Turns out the per-table limit is 30,000 statistics :-)
See end of post for my boring test that showed this.
***** 

Current SQL Server documentation (as of 2016 December 7) indicates that the maximum  number of non-indexed columns with statistics in a database is 30,000.

https://msdn.microsoft.com/en-us/library/ms143432.aspx

I suspect that is an old limitation that hasn't been updated.  (Or perhaps there's a wrinkle I haven't considered?)

I know of a pretty robust database :-) Check version so my results can be reproducible...

SELECT @@version AS SQLServer_version


SQLServer_version
Microsoft SQL Server 2014 - 12.0.4422.0 (X64) 
Jul 27 2015 16:56:19 
Copyright (c) Microsoft Corporation

Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: )

That's SQL Server 2014 SP1 CU2.
So how many auto-generated stats and how many columns in the database?

SELECT
(SELECT COUNT (*)
 FROM  sys.stats s
 JOIN sys.tables t ON t.object_id = s.object_id
 WHERE t.type_desc = 'user_table'
 AND t.is_ms_shipped = 0
 AND s.auto_created = 1
 AND NOT EXISTS (SELECT 1 FROM sys.indexes i 
                 WHERE s.stats_id = i.index_id 
                 AND s.object_id = i.object_id)
 ) AS user_table_auto_created_stats,
(SELECT COUNT (*) 
 FROM  sys.columns c
 JOIN sys.tables t ON c.object_id = t.object_id
 WHERE t.type_desc = 'user_table'
 AND t.is_ms_shipped = 0) AS user_table_columns

user_table_auto_created_stats	user_table_columns
64765	158796

Wow.  That's a lot more than the documented limit of 30,000 for stats on nonindexed columns.

I thought about it a bit... maybe some of those auto-created stats weren't first ordinal position in the index - yet still considered "indexed columns" for this purpose?

Modified the query a little, as below, to exclude columns that are accounted for in sys.index_columns - even the "include" columns.  That exclusion didn't decrease the number of auto-created stats in the database at all.  So it looks to me that the documented maximum is well below the actual maximum.  Maybe next year I'll poke around to find the actual maximum... not all that important to me at the moment, just an oddity that I came across.

SELECT COUNT (*) AS user_table_auto_created_stats
FROM  sys.stats s
JOIN sys.tables t ON t.object_id = s.object_id
JOIN sys.stats_columns sc ON sc.object_id = s.object_id AND sc.stats_id = s.stats_id
WHERE t.type_desc = 'user_table'
AND t.is_ms_shipped = 0
AND s.auto_created = 1
AND NOT EXISTS (SELECT 1 FROM sys.indexes i 
                WHERE s.stats_id = i.index_id 
                AND s.object_id = i.object_id)
AND NOT EXISTS (SELECT 1 FROM sys.index_columns ic 
                WHERE s.stats_id = ic.index_id 
                AND s.object_id = ic.object_id
                AND sc.column_id = ic.column_id)

user_table_auto_created_stats

64765

***** Updated *****

A friend pointed out that the same references indicates a maximum of 30,000 columns in a wide table.  That got me thinking - maybe 30,000 stats is a per-table maximum?

Not too hard to test.  Yep - limit per table.

PRINT @@version;
CREATE TABLE dbo.max_stats (bigint_1 BIGINT);
DECLARE @count INT = 1, @sqlText NVARCHAR(1000)
WHILE @count < 30100
BEGIN
SET @sqlText = 'CREATE STATISTICS max_stats' + CONVERT(NVARCHAR(256),@count) + ' ON dbo.MAX_STATS(bigint_1)'
EXEC (@sqlText)
SET @count = @count+1
END

Microsoft SQL Server 2014 - 12.0.4422.0 (X64)
    Jul 27 2015 16:56:19
    Copyright (c) Microsoft Corporation
    Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: )

Msg 1910, Level 16, State 1, Line 1
Could not create statistics 'max_stats30001' because it exceeds the maximum of 30000 allowed per table or view.

00:03:13