Indexed view

17 Mar 2006 1:19 PM

simonZ

I created view:

CREATE VIEW dbo.v_test with schemabinding as
select s.productID,sum(s.productQ)as quantity FROM
dbo.invoice i INNER JOIN dbo.sell s ON i.invoiceID=s.invoiceID
WHERE s.productID is not null AND i.status<3
GROUP BY s.productID

Now, I would like to create index:

CREATE UNIQUE CLUSTERED INDEX IVW_sumObj
ON dbo.v_test (productID,quantity)

I get an error:

A clustered index cannot be created on the view 'v_test' because the index
key includes columns which are not in the GROUP BY clause.

Then I try just create index only in one column:

CREATE UNIQUE CLUSTERED INDEX IVW_sumObj
ON dbo.v_test (productID)

but I get the following error:
An index cannot be created on the view 'v_test' because the view definition
does not include count_big(*).

How can I solve this?

regards,S

17 Mar 2006 3:01 PM

ML

Indexes on views containing aggregate functions can only be created when the
view definition includes the count_big(*) reference.

You are very nearly done:

create view dbo.v_test
with schemabinding
as
select s.productID
         ,sum(s.productQ) as quantity
         ,count_big(*) as countBig
         from dbo.invoice i
                 inner join dbo.sell s
                               on i.invoiceID=s.invoiceID
         where (s.productID is not null)
                   and (i.status < 3)
         group by s.productID

After this minor change first create a unique clustered index, then any
other nonclustered index as appropriate.

ML

---
http://milambda.blogspot.com/