Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

What is imprecise column in SQL Server?

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

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.

What does imprecise column mean?

UPDATE. The definition is following:

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

UPDATE2. The MyDeterministicClrFunction is defined as following:

[SqlFunction(IsDeterministic = true)]
public static SqlString MyDeterministicClrFunction(SqlString input)
{
    return input;
}
like image 468
TN. Avatar asked May 07 '12 14:05

TN.


1 Answers

Per MSDN, CLR Function columns must be persisted to be indexed:

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.

like image 156
JNK Avatar answered Sep 20 '22 15:09

JNK