sql-server - 使用子查询保留计算列

Question

我有这样的东西

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]

这很有效，但是它往往会大大减慢查询速度。如何使Index列持久化？我尝试过以下操作:

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]

但这会引发以下错误:表“Answers”中的计算列“Index”无法持久保存，因为该列进行用户或系统数据访问。或者我应该忘记它并使用 >[Index] int not null default(0) 并将其填充到 on insert 触发器中？

编辑:谢谢，最终解决方案:

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

您可以将该列更改为普通列，然后在使用触发器插入/更新该行时更新其值。

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

注意* 这并不完全正确，因为它无法在 UPDATE 上正常工作，因为我们没有“插入”表来引用来获取 ID 和 QuestionID。有一种方法可以解决这个问题，但我现在不记得了:(

Checkout this for more info