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

Do you really need your key to be (potentially) 4GB large AND unique? SqlServer does not allow this because checking uniqueness could potentially be a **very** time-consuming operation. — Klaus Byskov Pedersen, May 19 '10 at 08:50
@KlausByskovPedersen some more powerful DBMS like PostgreSQL is smart enough to allow it and index a digest instead. But you have a point. — Matthieu, Oct 21 '19 at 17:49
If you are using entity framework with a code first approach, you will run into this error if you try to index variables of type string. — Cale Sweeney, Oct 15 '21 at 14:48

score 287 · Answer 1 · edited Apr 16 '20 at 20:06

287

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.

edited Apr 16 '20 at 20:06

urig

16,016
26
115
184

answered May 19 '10 at 09:03

Daniel Renshaw

33,729
8
75
94

1

For varchar, would the limit still be varchar(450) ? – Steam Dec 19 '13 at 23:59
11

You have space to use either `varchar(900)` OR `nvarchar(450)`. – Daniel Renshaw Dec 20 '13 at 07:43
My understanding is that a varchar will take 4 bytes to determine the length of the item, meaning the actual limit needs to be varchar(896). Is this correct? – mrmillsy May 15 '14 at 11:31
2

@mrmillsy The declared maximum size does not include the overhead (which is 2 bytes, not 4) and the overhead bytes are not included in the limit on maximum index row size. http://technet.microsoft.com/en-us/library/ms176089(v=sql.100).aspx – Daniel Renshaw May 15 '14 at 11:50
I see. However, I receive this warning if I try to create a unique on a column with VARCHAR(900): "Warning! The maximum key length is 900 bytes. The index 'UQ_Tester_ID1ID2' has maximum length of 904 bytes. For some combination of large values, the insert/update operation will fail." Can I assume this is incorrect? – mrmillsy May 15 '14 at 12:35
For example: CREATE TABLE tester ( ID1 INT NOT NULL PRIMARY KEY ,ID2 VARCHAR(900) NOT NULL ,CONSTRAINT UQ_Tester_ID1ID2 UNIQUE (ID1, ID2) ) This is on SQL Server 2008 R2, by the way. – mrmillsy May 15 '14 at 12:36
I did a little test and attempted to insert a VARCHAR(900) into the ID2 field from the above example table. The insert failed with the following error: "Operation failed. The index entry of length 904 bytes for the index 'UQ_Tester_ID1ID2' exceeds the maximum length of 900 bytes." This suggests to me that the actual size limit of data in the key field is in fact 896, unless I'm doing something very wrong. However I was interested to note that the same error came up when trying to create the index keyed to a CHAR(900) ... I'm missing something obvious regarding the size of the type. – mrmillsy May 15 '14 at 12:47
1

@mrmillsy You're getting that message because you're including the `ID1 int` in the index. That `int` requires 4 bytes, in addition to the 900 bytes for the `varchar`. – Daniel Renshaw May 15 '14 at 16:23

score 35 · Answer 2 · answered May 19 '10 at 09:12

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.

Clever - nicer than triggers, I feel. – Neil Moss May 19 '10 at 09:24 — Neil Moss, May 19 '10 at 09:24

score 17 · Answer 3 · answered May 19 '10 at 09:03

17

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

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

5 Answers5

Linked