SQLTeam.com | Weblogs | Forums

Indexing for Compute Column concatenate columns

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

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

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;