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 the key is a nvarchar(max). A quick google found this. It, however, doesn't explain what a solution is. How do I create something like a 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 shouldn't be over 8000 bytes per row and should only use the first 900 bytes. So that 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

)

Note: The key size can't be over 450 characters. 

If the key's size is over 450 characters then switch to varchar instead of nvarchar (e.g. if you don't want to store characters from more than one codepage) then that will be increased to 900 characters.