具有子查询的持久化计算列

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)，并在插入时触发器中填充它？

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

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

- Lukáš Novotný

---

说实话，我不太确定你试图解决什么问题 - 是选择查询很慢吗？它没有涉及到你的“索引”列，所以我不明白那与此有何关系 - 不过你可能想要添加一两个索引... - Neville Kuyt

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

NB* 这并不完全正确，因为在UPDATE时它将无法正常工作，因为我们没有“inserted”表来引用获取ID和questionid。有一种解决方法，但我现在记不起来了:(

查看此链接以获取更多信息

- SecretDeveloper

---

这不考虑多行插入/更新。 - Alex M

Answer 2

计算列只存储要执行的计算公式。这就是为什么从表中查询计算列时会变慢的原因。如果您想将值持久化到实际的表列中，那么使用触发器是正确的。

- HardCode

---

持久化计算列应该保存计算出的值（它们通过绑定模式来检测更新需求）……问题是计算列通常存在的限制（在持久化计算列中更为显著）。 - user166390