SQL SERVER – Fix: Error: Msg 1904, Level 16 The statistics on table has 33 column names in statistics key list. The maximum limit for index or statistics key column list is 32

Earlier I wrote an article where I demonstrated that an index with more than 16 column is not possible. Here is the link to the article. After reading the same article I received email from user suggesting does it mean that statistics can be only created on only 16 columns. Well, answer is NO. One can create statistics on total of 32 columns, where as the limit of creating index is only 16 columns (and 900 bytes).

Here is the quick example where when attempted to create statistics on 33 columns is generating error but when statistics are created on 32 columns it works successfully.

Set up Script:

CREATE DATABASE TestDB
GO
USE TestDB
GO
CREATE TABLE Test1
(ID1 INT,  ID2 INT, ID3 INT,ID4 INT, ID5 INT, ID6 INT,ID7 INT, ID8 INT, ID9 INT,ID10 INT, ID11 INT, ID12 INT,ID13 INT, ID14 INT, ID15 INT,ID16 INT, ID17 INT, ID18 INT,ID19 INT, ID20 INT, ID21 INT,ID22 INT, ID23 INT, ID24 INT,ID25 INT, ID26 INT, ID27 INT,ID28 INT, ID29 INT, ID30 INT,ID31 INT, ID32 INT, ID33 INT)GO

Here is the example when index created on 33 columns it gives error.

CREATE STATISTICS [Stats_Test1]
ON [dbo].[Test1]
([ID1], [ID2], [ID3], [ID4], [ID5],[ID6], [ID7], [ID8], [ID9], [ID10],[ID11], [ID12], [ID13], [ID14], [ID15],[ID16], [ID17], [ID18], [ID19], [ID20],[ID21], [ID22], [ID23], [ID24], [ID25],[ID26], [ID27], [ID28], [ID29], [ID30],[ID31], [ID32], [ID33]
)GO

Msg 1904, Level 16, State 2, Line 1
The statistics ‘Stats_Test1′ on table ‘dbo.Test1′ has 33 column names in statistics key list. The maximum limit for index or statistics key column list is 32.

Here is the example when index created on 32 columns it works perfectly fine.

CREATE STATISTICS [Stats_Test1]
ON [dbo].[Test1]
([ID1], [ID2], [ID3], [ID4], [ID5],[ID6], [ID7], [ID8], [ID9], [ID10],[ID11], [ID12], [ID13], [ID14], [ID15],[ID16], [ID17], [ID18], [ID19], [ID20],[ID21], [ID22], [ID23], [ID24], [ID25],[ID26], [ID27], [ID28], [ID29], [ID30],[ID31], [ID32]
)GO

Well, in simple words, one can create statistics on 32 columns. Please additionally, note, the scope of this blog is not to discuss if that is good or bad. The purpose of this post is notice the Index can have maximum 16 columns but statistics can have 32 columns.

Reference : Pinal Dave (http://blog.sqlauthority.com)

<code style=”font-size: 12px;”><span style=”color:blue”>CREATE STATISTICS </span><span style=”color:black”>[Stats_Test1] <br></span><span style=”color:blue”>ON </span><span style=”color:black”>[dbo].[Test1]<br></span><span style=”color:gray”>(</span><span style=”color:black”>[ID1]</span><span style=”color:gray”>, </span><span style=”color:black”>[ID2]</span><span style=”color:gray”>, </span><span style=”color:black”>[ID3]</span><span style=”color:gray”>, </span><span style=”color:black”>[ID4]</span><span style=”color:gray”>, </span><span style=”color:black”>[ID5]</span><span style=”color:gray”>, <br></span><span style=”color:black”>[ID6]</span><span style=”color:gray”>, </span><span style=”color:black”>[ID7]</span><span style=”color:gray”>, </span><span style=”color:black”>[ID8]</span><span style=”color:gray”>, </span><span style=”color:black”>[ID9]</span><span style=”color:gray”>, </span><span style=”color:black”>[ID10]</span><span style=”color:gray”>, <br></span><span style=”color:black”>[ID11]</span><span style=”color:gray”>, </span><span style=”color:black”>[ID12]</span><span style=”color:gray”>, </span><span style=”color:black”>[ID13]</span><span style=”color:gray”>, </span><span style=”color:black”>[ID14]</span><span style=”color:gray”>, </span><span style=”color:black”>[ID15]</span><span style=”color:gray”>, <br></span><span style=”color:black”>[ID16]</span><span style=”color:gray”>, </span><span style=”color:black”>[ID17]</span><span style=”color:gray”>, </span><span style=”color:black”>[ID18]</span><span style=”color:gray”>, </span><span style=”color:black”>[ID19]</span><span style=”color:gray”>, </span><span style=”color:black”>[ID20]</span><span style=”color:gray”>,<br></span><span style=”color:black”>[ID21]</span><span style=”color:gray”>, </span><span style=”color:black”>[ID22]</span><span style=”color:gray”>, </span><span style=”color:black”>[ID23]</span><span style=”color:gray”>, </span><span style=”color:black”>[ID24]</span><span style=”color:gray”>, </span><span style=”color:black”>[ID25]</span><span style=”color:gray”>, <br></span><span style=”color:black”>[ID26]</span><span style=”color:gray”>, </span><span style=”color:black”>[ID27]</span><span style=”color:gray”>, </span><span style=”color:black”>[ID28]</span><span style=”color:gray”>, </span><span style=”color:black”>[ID29]</span><span style=”color:gray”>, </span><span style=”color:black”>[ID30]</span><span style=”color:gray”>,<br></span><span style=”color:black”>[ID31]</span><span style=”color:gray”>, </span><span style=”color:black”>[ID32]<br></span><span style=”color:gray”>)<br></span><span style=”color:black”>GO</span></code>