SQL SERVER – Maximum Column Per Index – 32

2 years ago

A very common question, I often receive is how many columns one can have for a single index. Well, the answer is you can have 32 Maximum Column Per Index but that does not mean you should have 32 indexes. Now, how many columns ideally one index should you have is the topic of another blog post. Let us see what happens if we try to include more than 32 columns in a single index.

Today’s blog post is inspired by my earlier written blog post on a very similar topic posted over here and here. So if you are going to say, it is similar, yes they have similar thoughts.

First, let us create an index with 33 columns. I am going to take simple columns like INT as a datatype for all the columns.

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 we will try to create an index with 33 columns on the same table.

CREATE INDEX [IX_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

Now, when you try to run the above script, it will give you the following error.

Msg 1904, Level 16, State 1, Line 19
The index ‘IX_Test1’ on table ‘dbo.Test1’ has 33 columns in the key list. The maximum limit for index key column list is 32.

This is because the column has 33 columns and it is not allowed.

However, if you have only 32 columns the index will be created successfully.

CREATE INDEX [IX_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

The script will run successfully. Similarly, you can’t have more than 33 columns in statistics as well. Here is the blog post about it. You can reach out to me on twitter for further questions.

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