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 found this  It however doesnt 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

User · Answer

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

User · Answer

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   key    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

User · Answer

A solution would be to declare your key as nvarchar 20

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to sql-server-2008