Indexing for Compute Column concatenate columns

1

Hi All,

Good days.
I need help. I have this:-
alter table Products
add keyCheck AS (Isnull(CommodityCode,'') + '' + Isnull(ItemDescription,'')) PERSISTED;

create nonclustered index ix_keyCheck on Products(keyCheck)

Why I can't create an index for the computed column?

Please advise.

Regards,
Micheale

2

The information you have provided is not sufficient to say why you are not able to create an index on the persisted column. There are a set of specific requirements that must be satisfied in order for you to be able to create the index. See here

3

Most likely a setting(s) are incorrect. To create an index on a computed column, 7 settings must be in effect:

SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET NUMERIC_ROUNDABORT OFF;
SET QUOTED_IDENTIFIER ON;