My present article covers two fundamental questions.
1) What is the maximum number of columns included in Primary Key Index/Constraint?
2) What is fix/solution for the following error:
Msg 1904, Level 16, State 1, Line 1
The index ” on table ‘dbo.Table_2’ has 17 column names in index key list. The maximum limit for index or statistics key column list is 16.
The same error surfaces when example is created using SSMS.
Fix/Solution/Workaround:
Maximum columns per Primary Key Index is 16. In fact, 16 is the limit for columns per Foreign Key and Index Key.
You cannot have more than 16 columns per Index Key, Primary Key or Foreign Key. So, reduce the columns in those column to less than or equal to 16 columns.
Reference : Pinal Dave (https://blog.sqlauthority.com)
13 Comments. Leave new
I really like it. This makes you one special blogger as no one in world writes about this kind of details.
Good Work Sir!
What would be your advise in situations (such as mine) where you just can’t reduce the number of columns you’d like to use as PK? My main data table should ideally contains 20+ columns for its PK.
My options would be to create a composite key.
Other options? Creating a hash of the composite key and store that hash?
I’m really puzzled and investigating for the time being…
Thanks for any answer.
Olivier
Please help me to understand
You are talking about index.
there can be on primary key on a table???
@Olivier
The simplest method would be as you said. CREATE another TABLE and put 4 to 16) of the COLUMNs there with a UNIQUE CONSTRAINT. And ADD an id COLUMN as the PK. Now the 20 COLUMN key is Id and the rest of the COLUMNs.
Most likely there is some form of grouping amongst those COLUMNs anyway. If that is the case, this form of lookup TABLE is nice. No longer the netural key, but it will do. :)
@New techie Praveen
A PRIMARY KEY automatically CREATEs a UNIQUE INDEX. So, when talking about a PRIMARY KEY we can talk about the implicit INDEX as well.
Did you find a solution for this problem.I am facing a similar scenario
Hi NewBie,
There is no solution for this problem I guess. Please read following paragraph in article.
Fix/Solution/Workaround:
Maximum columns per Primary Key Index is 16. In fact, 16 is the limit for columns per Foreign Key and Index Key.
You cannot have more than 16 columns per Index Key, Primary Key or Foreign Key. So, reduce the columns in those column to less than or equal to 16 columns.
This Fix is of no use. Find some workaround. Of No Help
I wonder if this limit still applies in the new version of SQL Server coming out in 2012?
Remove PK and add clustered index on all 20 columns….that may work….
Gentlemen, to work around this problem, to create a PK (ID_TABLE), the result of it will be the concatenation of FKs. Exemple: example: FK1 = 10, FK2 = 30, Fk25 = 40, the result PK = 103040.
Marcius – Thanks!
When doing this, you should cast to varchar and use a separator for the values. Why? Because for instance the following key values would result in the same concatenation:
FK1 = 10, FK2 = 30, Fk25 = 44
FK1 = 10, FK2 = 304, Fk25 = 4
So the PKs should be something like 10|30|44 and 10|304|4 or a hash value of that.