SQL SERVER - Maximum Column Per Index - 32 - SQL Authority with Pinal Dave
source link: https://blog.sqlauthority.com/2021/03/22/sql-server-maximum-column-per-index-32/?utm_campaign=sql-server-maximum-column-per-index-32
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
SQL SERVER – Maximum Column Per Index – 32
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)
Recommend
-
6
SQL Authority with Pinal Dave
-
6
One of the conversations that keep on coming up during Comprehensive Database Performance Health Check is at what level one should s...
-
6
Are Index Scans Bad? The answer No. Index Scans are Not Always Bad. This was in-depth discussed with my client during the Comprehensive Database...
-
4
The best part of my job is working with my clients on the topic of SQL Server Performance Tuning when I am working with them on Comprehensive D...
-
7
SQL SERVER – COUNT(*) and Index Used – Part 2Yesterday I wrote a blog post on the topic SQL SERVER – COUNT(*) and Index Used, lo...
-
3
SQL SERVER – Altering Column – From NULL to NOT NULLLet us see a very simple script today. It is about Altering Column – From NULL to NOT NULL. I recently had to perform this task for my client...
-
3
SQL SERVER – MAX Column ID Used in TableHow to find out how many columns a table had in the history of the table? An interesting question isn’t it. Well, we discussed this during
-
7
SQL SERVER – Prevent Users from Changing IndexEarlier this week, I wrote a blog post SQL SERVER – Trigger on Databas...
-
7
SQL SERVER – Rebuilding Index with CompressionWe can be Rebuilding Index with Compression even if it was originally not created with compressions. Let us learn how we can do it today. I had recently discussed with my client at
-
3
SQL SERVER – Check If a Column Exists 2016 OnwardsIt is totally fine to use the syntax and code which is working fine in our code, particularly if it is supported by your application and server...
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK