Persisted 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 great, however it tends to slow down queries quite a bit. How can I make column Index persisted? I have tried 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 that throws 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 fill it in on insert trigger?

edit: thank you, final solution:

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 could change the column to be a normal column and then update its value when you INSERT/UPDATE that 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 fully correct as it wont work correctly on UPDATE as we wont have the "inserted" table to reference to get the ID and questionid. There is a way around this but i cant remember it right now :(

Checkout this for more info