SQL Server 中的不精确列是什么? [英] What is imprecise column in SQL Server?

问题描述

在 nvarchar 类型的计算列上创建索引引发以下错误:

Creating index on computed column of type nvarchar raises following error:

无法在表MyTable"上创建索引或统计信息MyIndex"，因为计算列 'MyColumn' 不精确且未持久化.考虑从索引或统计键或标记中删除列计算列持久化.

Cannot create index or statistics 'MyIndex' on table 'MyTable' because the computed column 'MyColumn' is imprecise and not persisted. Consider removing column from index or statistics key or marking computed column persisted.

不精确列是什么意思?

更新.定义如下:

alter table dbo.MyTable
    add [MyColumn] as dbo.MyDeterministicClrFunction(MyOtherColumn)
go  
create index MyIndex on dbo.MyTable(MyColumn)
go

更新2.MyDeterministicClrFunction 定义如下:

[SqlFunction(IsDeterministic = true)]
public static SqlString MyDeterministicClrFunction(SqlString input)
{
    return input;
}

推荐答案

Per MSDN, CLR Function 列必须持久化才能被索引:

任何包含公共语言运行时 (CLR) 表达式的计算列都必须是确定性的并标记为 PERSISTED，然后才能对该列建立索引.计算列定义中允许使用 CLR 用户定义类型表达式.只要类型是可比较的，就可以对类型为 CLR 用户定义类型的计算列进行索引.有关详细信息，请参阅 CLR 用户定义类型.

Any computed column that contains a common language runtime (CLR) expression must be deterministic and marked PERSISTED before the column can be indexed. CLR user-defined type expressions are allowed in computed column definitions. Computed columns whose type is a CLR user-defined type can be indexed as long as the type is comparable. For more information, see CLR User-Defined Types.

坚持这个专栏，我怀疑它会起作用.

Persist the column and I suspect it will work.

这篇关于SQL Server 中的不精确列是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！