simulate union to make indexed view

Question

I've got the following table schema in SQL Server 2005 and I'd like to denormalize it into an indexed view for some queries I'm writing until I can make the structural changes permanent.

The tables are as follows

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
)

I want a table with a single row for each grader/grade/document combination where the grader is either grader 1, grader 2, or grader 3

The View I wrote uses UNION, so it doesn't index:

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

Problem is - SQL can't index the view with union...

Do you mean you want to normalize it (instead of denormalize)? — YWE, Nov 18 '10 at 04:46
I wanted to have a temporary normalized structure until I could get the application constructs in place to update a normalized table. Thought a view would do the trick, but alas. — Caveatrob, Nov 18 '10 at 05:41

score 1 · Accepted Answer · answered Nov 18 '10 at 04:48

Why does it have to be an indexed view when you could use a proper table:

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

I understand that you probably want the view so you don't have the hassle of synchronizing data. The only other alternative is to not index the view...

Yeah, that was it. Maybe I'll just do the normalized table and write a routine to update it until I can get the app set up. Thanks! — Caveatrob, Nov 18 '10 at 05:42

simulate union to make indexed view

1 Answers1