SQL 属于不能用作索引中的键列的类型
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2863993/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
is of a type that is invalid for use as a key column in an index
提问by Daniel Renshaw
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
其中 key 是 nvarchar(max)。一个快速的谷歌找到了这个。然而，它并没有解释什么是解决方案。我如何创建类似 Dictionary 的东西，其中键和值都是字符串，显然键必须是唯一的并且是单一的。我的 sql 语句是
create table [misc_info] (
[id] INTEGER PRIMARY KEY IDENTITY NOT NULL,
[key] nvarchar(max) UNIQUE NOT NULL,
[value] nvarchar(max) NOT NULL);
回答by Daniel Renshaw
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:
唯一约束不能超过每行 8000 个字节，即使这样也只会使用前 900 个字节，因此您的密钥最安全的最大大小是：
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 varcharinstead of nvarchar(e.g. if you don't need to store characters from more than one codepage) then that could increase to 900 characters.
即密钥不能超过 450 个字符。如果您可以切换到varchar而不是nvarchar（例如，如果您不需要存储来自多个代码页的字符），则可以增加到 900 个字符。
回答by Marwan
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:
SQL Server（直到 2008 R2）存在一个限制，即 varchar(MAX) 和 nvarchar(MAX)（以及其他几种类型，如 text、ntext）不能在索引中使用。您有 2 个选择：
1. 在关键字段上设置一个有限的大小，例如。nvarchar(100)
2. 创建一个检查约束，将值与表中的所有键进行比较。条件是：
([dbo].[CheckKey]([key])=(1))
and [dbo].[CheckKey] is a scalar function defined as:
和 [dbo].[CheckKey] 是一个标量函数，定义为：
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.
但请注意，原生索引比检查约束的性能更高，因此除非您确实无法指定长度，否则不要使用检查约束。
回答by Don
The only solution is to use less data in your Unique Index. Your key can be NVARCHAR(450) at most.
唯一的解决方案是在您的唯一索引中使用更少的数据。您的密钥最多可以是 NVARCHAR(450)。
"SQL Server retains the 900-byte limit for the maximum total size of all index key columns."
“SQL Server 保留了所有索引键列的最大总大小的 900 字节限制。”
Read more at MSDN
在MSDN 上阅读更多内容
回答by Klaus Byskov Pedersen
A solution would be to declare your key as nvarchar(20).
一个解决方案是将您的密钥声明为nvarchar(20).
回答by Neil Moss
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:
注意到 klaisbyskov 关于您的密钥长度需要为 GB 大小的评论，并假设您确实需要这个，那么我认为您唯一的选择是：
- 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.
- 使用键值的散列
- 在 nchar(40) 上创建一列（例如，对于 sha1 哈希），
- 在哈希列上放置一个唯一键。
- 保存或更新记录时生成哈希
- 触发器在插入或更新时查询表中的现有匹配。
Hashing comes with the caveat that one day, you mightget a collision.
散列伴随着警告，有一天，你可能会发生碰撞。
Triggers will scan the entire table.
触发器将扫描整个表。
Over to you...
给你...
