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.

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 (http://blog.sqlauthority.com)

About these ads

11 thoughts on “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

  1. I really like it. This makes you one special blogger as no one in world writes about this kind of details.

    Good Work Sir!

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

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

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

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

  6. Pingback: SQL SERVER – Weekly Series – Memory Lane – #024 | SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s