generating a script for building a new index on a new computed column in 20 tables

Question

hi we are now on 2019 but last time we dealt with this were on 2014.

as i recall, building an index on our computed bigint equivalent of our row version is simple when certain ansi, abort etc settings are of certain values.

otherwise, the tables involved must be reloaded I think after the ansi etc settings are changed. and those tables have fk dependencies from other tables.

so if the community believes me, and agrees that even under 2019 (we haven't checked) this could be a problem, does anyone know of an article where a script that generates a script for this kind of thing is available? i dot think we automated it last time because there were fewer tables involved and most of them didnt have those settings interferring.

Answer 1

we got an answer , sorry. we failed previously under 2012 (not 2014) unless we reloaded. the group i'm working with jumped from 2012 to 2019.

and we have some evidence now that under 2019 you can build an index on a computed column regardless of the ansi etc settings.

erland and tom may have told us this before. I vaguely remembered that and shared it with our dba. he went ahead and tried it and reported its fine under 2019.

Answer 2

I am sorry, but I don't understand much of this.

But when it comes to ANSI settings and indexed computed columns, nothing has changed. And I have no reason to think that this will ever change. SET ANSI_PADDING OFF etc are legacy options. I would love to see them go away, because they cause a lot of confusion. But nor do I expect that to happen.

Answer 3

thx, i'll have to let him know that he might be fooling himself into thinking something has changed after 2012. he believes that in 2012 the legacy settings that were in play when the table was created could prevent building an index on a computed column. and the only option was to reload the table from scratch. but after 2012 no. i wonder if he created the tables in 2019 using different legacy settings.

in the meantime i'm bummed. i just learned that a filtered index isnt permitted on a computed column at least in 2017. i'm going to try building one in 2019.

Answer 4

thx erland, this did not work on 2019

select @@version
drop table revised1
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[revised1](
[ID] [int] IDENTITY(1,1) NOT NULL,
whatever char(1),
row_version_stamp timestamp,
[ROW_VERSION_NUMBER] AS (CONVERT([bigint],[ROW_VERSION_STAMP])) --persisted
CONSTRAINT [PK_revised1] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

create index ix1 on revised1(ROW_VERSION_NUMBER) where row_version_number > 7

the upload image mechanism doesn't seem to be working but this is the error

Msg 10609, Level 16, State 1, Line 21
Filtered index 'ix1' cannot be created on table 'revised1' because the column 'ROW_VERSION_NUMBER' in the filter expression is a computed column. Rewrite the filter expression so that it does not include this column.