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

Question

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 found this. It, however, 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);

Answer 1

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.