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

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.
SQL SERVER - Maximum Columns per Primary Key - Fix : Error : Msg 1904, Level 16, The index on table has column names in index key list. The maximum limit for index or statistics key column list is 16 pk161

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.

SQL SERVER - Maximum Columns per Primary Key - Fix : Error : Msg 1904, Level 16, The index on table has column names in index key list. The maximum limit for index or statistics key column list is 16 pk16

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

Solarwinds
, ,
Previous Post
SQLAuthority News – SQL Server 2008 Service Pack 1 Released – Available for Download
Next Post
SQL SERVER – Fix Error 9803. Invalid data for type “numeric” – Data Type Mapping

Related Posts

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!

    Reply
  • 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

    Reply
  • New techie Praveen
    July 21, 2009 3:12 pm

    Please help me to understand

    You are talking about index.

    there can be on primary key on a table???

    Reply
  • Brian Tkatch
    July 21, 2009 6:14 pm

    @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. :)

    Reply
  • Brian Tkatch
    July 21, 2009 6:16 pm

    @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.

    Reply
  • Did you find a solution for this problem.I am facing a similar scenario

    Reply
  • 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.

    Reply
  • This Fix is of no use. Find some workaround. Of No Help

    Reply
  • I wonder if this limit still applies in the new version of SQL Server coming out in 2012?

    Reply
  • Remove PK and add clustered index on all 20 columns….that may work….

    Reply
  • Marcius Linhares
    May 22, 2015 1:41 am

    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.

    Reply
    • Marcius – Thanks!

      Reply
    • 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.

      Reply

Leave a Reply

Menu