is of a type that is invalid for use as a key column in an index

sql, sql-server-2008

Solution

A unique constraint can't be over 8000 bytes per row and will only use the first 900 bytes even then so the safest maximum size for your keys would be:

create table [misc_info]
( 
    [id] INTEGER PRIMARY KEY IDENTITY NOT NULL, 
    [key] nvarchar(450) UNIQUE NOT NULL, 
    [value] nvarchar(max) NOT NULL
)

i.e. the key can't be over 450 characters. If you can switch to `varchar` instead of `nvarchar` (e.g. if you don't need to store characters from more than one codepage) then that could increase to 900 characters.

Problem

I have an error at ``` Column 'key' in table 'misc_info' is of a type that is invalid for use as a key column in an index. ``` where key is a nvarchar(max). A quick google search finds that the maximum length of an index is 450 chars. However, this doesn't explain what a solution is. How do I create something like Dictionary where the key and value are both strings and obviously the key must be unique and is single? My sql statement was ``` create table [misc_info] ( [id] INTEGER PRIMARY KEY IDENTITY NOT NULL, [key] nvarchar(max) UNIQUE NOT NULL, [value] nvarchar(max) NOT NULL); ```

Original source