由网友(ソ愿岁月温柔对她丷)分享简介：我使用的是SQL Server 2008中。我有一个为nvarchar（max）列名为标题，然后我想补充的唯一索引它。由于列大于900bytes，我决定创建一个HASHBYTES计算列（根据建议的计算器）。我如何创建HASHBYTES列？ alter table将软商品加TitleHash AS（HASHBYTES（...

我使用的是SQL Server 2008中。

我有一个为nvarchar（max）列名为标题，然后我想补充的唯一索引它。 由于列大于900bytes，我决定创建一个HASHBYTES计算列（根据建议的计算器）。

我如何创建HASHBYTES列？

alter table将软商品加TitleHash AS（HASHBYTES（SHA1，[标题]））坚持;

这工作，创建计算列。

但是，试图添加一个索引，我得到了以下错误时：

的添加选定列将产生的索引关键字为8000字节的最大长度。
允许的最大索引长度为900字节。
如果该键列的总价值超过900字节INSERT和UPDATE操作失败。
你想继续吗？
 

这是用来创建索引的查询：

  CREATE非聚集索引[UIX_TitleHash] ON [DBO]。[软商品]
（
    [TitleHash] ASC
）WITH（PAD_INDEX = OFF，STATISTICS_NORECOMPUTE = OFF，SORT_IN_TEMPDB = OFF，IGNORE_DUP_KEY = OFF，DROP_EXISTING = OFF，ONLINE = OFF，ALLOW_ROW_LOCKS = ON，ALLOW_PAGE_LOCKS = ON）ON [PRIMARY]
走
 

解决方案

在HASHBYTES列被创建为 VARBINARY（MAX），除非你明确告诉它有20个字节有很多：

  alter table将dbo.Softs
  添加TitleHash AS CAST（HASHBYTES（SHA1，[标题]）为varbinary（20））坚持
 

一旦你做到了这一点，那么你就可以创建索引的列（唯一与否）：

  CREATE UNIQUE聚集索引[UIX_TitleHash]
  ON [DBO]。[软商品]（[TitleHash] ASC）
 

现在这个应该工作得很好。

I'm using SQL Server 2008.

I have a NVARCHAR(MAX) column called Title and i want to add an unique index for it. Because the column is bigger than 900bytes , I decided to create a HashBytes computed column (based on recommendation on StackOverflow).

How do i create the HashBytes column?

alter table Softs add TitleHash AS (hashbytes('SHA1',[Title])) PERSISTED;

this worked and the computed column was created.

BUT when trying to add a index i get the following error:

Adding the selected columns will result in an index key with a maximum length of 8000 bytes.  
The maximum permissible index length is 900 bytes. 
INSERT and UPDATE operations fail if the combined value of the key columns exceeds 900 bytes.  
Do you want to continue?

This is the query used to create the index:

CREATE NONCLUSTERED INDEX [UIX_TitleHash] ON [dbo].[Softs] 
(
    [TitleHash] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

解决方案

The hashbytes column gets created as a VARBINARY(MAX) unless you do specifically tell it that 20 bytes are plenty:

alter table dbo.Softs 
  add TitleHash AS CAST(hashbytes('SHA1', [Title]) AS VARBINARY(20)) PERSISTED

Once you've done that, then you can create your index (unique or not) on that column:

CREATE UNIQUE NONCLUSTERED INDEX [UIX_TitleHash] 
  ON [dbo].[Softs]([TitleHash] ASC)

Now this should work just fine.