模拟联合以创建索引视图

Question

我已经在SQL Server2005中获得了以下表架构，并且我想将其反规范化为索引视图，用于我正在编写的一些查询，直到我可以使结构更改永久生效为止。

表如下所示

Writing
(
DocumentSerial int
Grader1_ID int
Grade_1 int
Grader2_ID int 
Grade_2 int 
Grader3_ID int 
Grade_3 int
)

Users
(userID int,
firstname,
lastname
)

我想要一个表，每个分级员/年级/文档组合都有一行，其中分级员是1分、2分或3分

我写的视图使用了UNION，所以它不会索引：

select documentSerial,grader1_id as grader_id, grade_1 as grade ,1 as sequence
 from Writing w inner join User U on w.grader1_id=u.userid
UNION
select documentSerial,grader2_id as grader_id, grade_2 as grade ,2 as sequence
 from Writing w inner join User U on w.grader2_id=u.userid
UNION
select documentSerial,grade31_id as grader_id, grade_3 as grade ,3 as sequence
 from Writing w inner join User U on w.grader3_id=u.userid

问题是- SQL不能索引带有union的视图...

Answer 1

当您可以使用适当的表时，为什么它必须是索引视图：

SELECT x.*
  INTO normalized_table
  FROM (select documentSerial,grader1_id as grader_id, grade_1 as grade ,1 as sequence
          from Writing w 
          join User U on w.grader1_id = u.userid
        UNION ALL
        select documentSerial,grader2_id as grader_id, grade_2 as grade ,2 as sequence
          from Writing w 
          join User U on w.grader2_id = u.userid
        UNION ALL
        select documentSerial,grade31_id as grader_id, grade_3 as grade ,3 as sequence
          from Writing w 
          join User U on w.grader3_id = u.userid) x

我知道您可能想要视图，这样就不会有同步数据的麻烦了。唯一的另一种选择是不索引视图...