Stable computed column with subquery

Question

I have something like this

create function Answers_Index(@id int, @questionID int) returns int as begin return (select count([ID]) from [Answers] where [ID] < @id and [ID_Question] = @questionID) end go create table Answers ( [ID] int not null identity(1, 1), [ID_Question] int not null, [Text] nvarchar(100) not null, [Index] as [dbo].[Answers_Index]([ID], [ID_Question]), ) go insert into Answers ([ID_Question], [Text]) values (1, '1: first'), (2, '2: first'), (1, '1: second'), (2, '2: second'), (2, '2: third') select * from [Answers] 

Which works fine, however it slows down the queries a bit. How can I save Index columns? I tried the following:

 create table Answers ( [ID] int not null identity(1, 1), [ID_Question] int not null, [Text] nvarchar(100) not null, ) go create function Answers_Index(@id int, @questionID int) returns int with schemabinding as begin return (select count([ID]) from [dbo].[Answers] where [ID] < @id and [ID_Question] = @questionID) end go alter table Answers add [Index] as [dbo].[Answers_Index]([ID], [ID_Question]) persisted go insert into Answers ([ID_Question], [Text]) values (1, '1: first'), (2, '2: first'), (1, '1: second'), (2, '2: second'), (2, '2: third') select * from [Answers] 

But this causes the following error: Computed column 'Index' in table 'Answers' cannot be persisted because the column does user or system data access. Or should I just forget about it and use [Index] int not null default(0) and populate it in the on insert trigger?

edit: thanks, final decision:

 create trigger [TRG_Answers_Insert] on [Answers] for insert, update as update [Answers] set [Index] = (select count([ID]) from [Answers] where [ID] < a.[ID] and [ID_Question] = a.[ID_Question]) from [Answers] a inner join [inserted] i on a.ID = i.ID go 

Answer 1

You can change a column like a regular column, and then update its value when you INSERT / UPDATE this row using a trigger.

 create table Answers ( [ID] int not null identity(1, 1), [ID_Question] int not null, [Text] nvarchar(100) not null, [Index] Int null ) CREATE TRIGGER trgAnswersIU ON Answers FOR INSERT,UPDATE AS DECLARE @id int DECLARE @questionID int SELECT @id = inserted.ID, @questionID = inserted.ID_question UPDATE Answer a SET Index = (select count([ID]) from [Answers] where [ID] < @id and [ID_Question] = @questionID) WHERE a.ID = @id AND a.ID_question = @questionID GO 

NB * This is not entirely correct, since it does not work correctly in UPDATE, since we will not have a “inserted” table for links to get the identifier and the questionnaire. There is a way around this, but I can't remember it right now :(

Checkout for more information

Answer 2

Computed columns only store the calculation formula to execute. Therefore, when querying a computed column from a table, it will be slower. If you want to store the values ​​in the actual column of the table, then you are using the trigger correctly.