Have you ever wondered about the limitations of creating indexes and statistics in SQL Server? In a previous article, I discussed the maximum number of columns allowed in an index, which is 16. However, a reader recently reached out to me with a question: does this mean that statistics can only be created on 16 columns as well? The answer is no.

While the limit for creating an index is 16 columns (and 900 bytes), you can create statistics on a total of 32 columns. Let’s take a look at a quick example to illustrate this.

First, let’s set up a sample database and table:

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

Now, let’s try creating statistics on 33 columns:

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

Running this code will generate an error:

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

However, if we try creating statistics 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

So, in simple words, you can create statistics on up to 32 columns in SQL Server. It’s important to note that the purpose of this post is to highlight the difference between the maximum number of columns allowed in an index (16) and the maximum number of columns allowed in statistics (32). Whether or not it is good or bad to have such a high number of columns in statistics is a separate discussion.

I hope this clarifies any confusion regarding the limitations of indexes and statistics in SQL Server. Stay tuned for more informative articles on SQL Server!