CODEWITHSUNDEEP
sql-serverindexingtype-conversioncalculated-columns
i-one
i-one

Reputation: 5120

Index on computed column of `int` type can't be created, because of column is imprecise

Assume there is a table, like:

create table #data
(
    ID int identity(1, 1),
    Value float,
    VCluster as (ID % 5),
    VType as (cast(case when Value > 1 then 1 when Value < -1 then -1 else 0 end as int))
)

I need to create two indexes on its computed columns as follows:

create index #ix_data_1 on #data (VCluster)
create index #ix_data_2 on #data (VType)

First one creates well, but when I'm trying to create second one I'm getting error message: "Cannot create index or statistics '#ix_data_2' on table '#data' because the computed column 'VType' is imprecise and not persisted..."

I queried system views as:

select c.name, c.system_type_id, t.name as type_name
from tempdb.sys.columns c
    join tempdb.sys.types t on t.system_type_id = c.system_type_id
where c.object_id = object_id('tempdb..#data')
order by c.column_id

And got following result:

name       system_type_id type_name
---------- -------------- ----------
ID         56             int
Value      62             float
VCluster   56             int
VType      56             int

So, the type of VType column is int, but it seems that somehow it is "not very int". I know I can make column persisted and create index, but is there a way to avoid it and make column VType "100% int"?

Upvotes: 0

Views: 192

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239684

It's not the result type that's making it imprecise - it's its dependence on a float input value. There's nothing more you can do except the solution you've already ruled out - mark it as PERSISTED.

Upvotes: 2

Related Questions