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

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);

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.

There is a limitation in SQL Server (up till 2008 R2) that varchar(MAX) and nvarchar(MAX) (and several other types like text, ntext ) cannot be used in indices. You have 2 options:

1. Set a limited size on the key field ex. nvarchar(100)
   

2. Create a check constraint that compares the value with all the keys in the table. The condition is:

   ([dbo].CheckKey=(1))

and [dbo].[CheckKey] is a scalar function defined as:

CREATE FUNCTION [dbo].[CheckKey]
(
	@key nvarchar(max)
)
RETURNS bit
AS
BEGIN
	declare @res bit
	if exists(select * from key_value where [key] = @key)
		set @res = 0
	else
		set @res = 1
		
	return @res
END

But note that a native index is more performant than a check constraint so unless you really can't specify a length, don't use the check constraint.

The only solution is to use less data in your Unique Index. Your key can be NVARCHAR(450) at most.

"SQL Server retains the 900-byte limit for the maximum total size of all index key columns."

Read more at MSDN

A solution would be to declare your key as nvarchar(20).

Noting klaisbyskov's comment about your key length needing to be gigabytes in size, and assuming that you do in fact need this, then I think your only options are:

1. use a hash of the key value
   • Create a column on nchar(40) (for a sha1 hash, for example),
   • put a unique key on the hash column.
   • generate the hash when saving or updating the record
2. triggers to query the table for an existing match on insert or update.

Hashing comes with the caveat that one day, you might get a collision.

Triggers will scan the entire table.

Over to you...