SQL Server Indexed View Error

Question

I realize this is a very contrived example, but I've simplified the full version down to the following which demonstrates the problem:

CREATE VIEW model.Appointments_Partition1
WITH SCHEMABINDING AS
  SELECT CONVERT(varchar(15), AppointmentId) as Id, 
         ap.AppTypeId as AppointmentTypeId, 
         ap.Duration as DurationMinutes, 
         ap.AppointmentId as EncounterId, 
         COUNT_BIG(*) as __count_big
    FROM dbo.Appointments ap 
    JOIN dbo.PracticeCodeTable pct ON SUBSTRING(pct.Code, 1, 1) = ap.ScheduleStatus 
                                  AND pct.ReferenceType = 'AppointmentStatus' 
   WHERE ap.AppTime > 0
GROUP BY CONVERT(varchar(15), AppointmentId), ap.AppTypeId, ap.Duration, ap.AppointmentId

CREATE UNIQUE CLUSTERED INDEX [IX_Appointments_Partition1_Id]
ON model.Appointments_Partition1 ([Id]);

I get:

Msg 8668, Level 16, State 0, Line 12
Cannot create the clustered index 'IX_Appointments_Partition1_Id' on view 'PracticeRepository.model.Appointments_Partition1' because the select list of the view contains an expression on result of aggregate function or grouping column. Consider removing expression on result of aggregate function or grouping column from select list.

I'm including count_big...so why is the group by a problem?....and how can I resolve the error?

score 5 · Accepted Answer · answered Nov 18 '11 at 06:34

Here is the same error message with some boolean logic applied to it:

Cannot create the clustered index '...' on view '...' because the select list of the view contains an expression on a grouping column. Consider removing expression on a grouping column from the select list.

You need to remove the CONVERT in CONVERT(varchar(15), AppointmentId)

Zohaib · Answer 2 · 2011-11-18T05:33:15.070

-1

I find this reason on one of the blogs, seems reasonable to me

No, you can't use schema binding on a view that has an aggregate. And you can't index a view unless you use schema binding. You also can't bind an index that uses outer or left joins. Basically, you can only bind a view that contains a simple select statement.

http://www.tek-tips.com/viewthread.cfm?qid=1401646

You can go through the blog and see if it exactly matched your scenario.

http://technet.microsoft.com/en-us/library/cc917715.aspx

If you want to build index on views, then you must create views with schema binding, in the above link it is explained in detail. Go through the section of Design Considerations

edited Nov 18 '11 at 05:33

answered Nov 18 '11 at 05:20

Zohaib

7,026
3
26
35

1

That's not correct according to this: "If the view definition uses an aggregate function, the SELECT list must also include COUNT_BIG (*)." http://msdn.microsoft.com/en-us/library/ms191432(v=SQL.90).aspx – Jeff Nov 18 '11 at 05:24
@JeffN825 please see above edited answer. Thats correct for simple views, but inorder to build index on views, you need to use schema binded views. Problem is not in aggregate functions, its in the index on the view. – Zohaib Nov 18 '11 at 05:28
it is schema binded. look at the question – Jeff Nov 18 '11 at 05:44
@JeffN825 Sorry, my bad.. If problem is not yet solved, I was going through the link of technet and it is stated there that View definition should not contain CONVERT (producing imprecide result), although does not matched 100% with your case, but no harm in trying.. – Zohaib Nov 18 '11 at 05:55

SQL Server Indexed View Error

2 Answers2