CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sqlindexed-view
Caveatrob
Caveatrob

Reputation: 13277

simulate union to make indexed view

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...

Upvotes: 1

Views: 724

Answers (1)

OMG Ponies
OMG Ponies

Reputation: 332631

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...

Upvotes: 1

Related Questions