Indexing a View

[Bethany Holliday]

Hi All,

I'm hoping someone can help me. I think I'm missing something very
basic. I'm trying to put a clustered index on a view that I have
created. I keep getting the error:

Server: Msg 8668, Level 16, State 1, Line 1
An index cannot be created on the view 'cew_avwage_usc nty' because the
select list of the view contains a non-aggregate expression.

Here is the create view statement:

CREATE view dbo.cew_avwage_ uscnty
with schemabinding
as
select statefips, countyfips, naics_code, sum(disc * 0) as disc,
data_year, sum(qtr_payroll )/((sum(emp1+emp2 +emp3))/12) as avwage
from dbo.qcew_own_n where year= '2004'
and (ownership = '0'
and (naics_code like '__' or naics_code like '__-__')
or (ownership = '5' and naics_code = '10'))
and countyfips <> '999'
and naics_code <> '99'
and ((disc = '0') or (disc <> '0' and (emp1 <> 0 or emp2 <> 0 or emp3 <>
0)))
group by statefips, countyfips, naics_code, data_year

Here is the create index statement I am using:

create unique clustered index main_cdx
on dbo.cew_avwage_ uscnty (statefips, countyfips, naics_code, year)

Each field that I have listed in the select statement MUST appear in the
view. The disc field needs to be set = 0 and the data_year field needs
to be set = 2004. Initially I had select statement for disc and
data_year set as, disc = '0', year = '2004' .. but thought this was
giving me my problem. I changed it and am still getting the same error?
Is my problem because of the fields that I am grouping by? Any advice
would be very much appreciated. I'm trying to avoid creating an extract
table, as these data are updated regularly - so I would like to only
update the base table and have the view refresh itself.

Thanks again!

Bethany


*** Sent via Developersdex http://www.developersdex.com ***

[Alexander Kuznetsov]

instead of

sum(qtr_payroll )/((sum(emp1+emp2 +emp3))/12) as avwage

use 2 columns

sum(qtr_payroll )
sum(emp1+emp2+e mp3)

also include

count_big(*)

in the view definition

[Bethany Holliday]

Thanks for the tips. I'm a little confused about your advice to use 2
columns. Are you saying I should break it up - sum the payroll
sum(qtr_payroll ) .. and then sum the employment sum(emp1_emp2+e mp3) and
then derive my average wage from those 2 columns? My end result is that
I need an average wage. Average wage is the quarterly payroll, divided
by the sum of the monthly employment divided by 12 ..

Sorry if I missed something obvious .. I tried breaking it up and then
deriving the average wage in a 3 column from the 2 columns, that did not
work either:

sum(qtr_payroll ) as qtr_payrroll, sum(emp1_emp2+e mp3)/12 as qtr_emp,
sum(qtr_payroll / qtr_emp) as avwage
*** Sent via Developersdex http://www.developersdex.com ***

[Alexander Kuznetsov]

Bethany,

I would try this indexed view definition:

sum(qtr_payroll ) sq1,
sum(emp1) se1, sum(emp2) se2, sum(emp3) se3,
count_big(*) cb

and calculate the average when you select from the view.

BTW, what are the types of emp1, emp2, emp3?
You might get an 'imprecise expression in where clause' error if they
are float.

what do you need the condition for:

(emp1 <> 0 or emp2 <> 0 or emp3 <> 0
Why are you using the expression sum(disc * 0) as disc?

[Bethany Holliday]

Alexander,

Thanks again. I will try your suggestions. I did, however, want to
answer your questions just in case I'm making this much more difficult
that I need to. ;)

The type for emp1, emp2 & emp3 is decimal.

I need the disc field to be equal to zero. The actual values in the
disclosure field in the base table are 0, 1, or 2. Intitially I had
this field in the select statement set as disc = 0, but because the
error I was receiving, I thought perhaps it didn't like my equal (=)
sign - setting the value to 0 - so I changed it to multiply the whatever
was there by zero to give me a value of zero.

As for the condition :
and ((disc = '0') or (disc <> '0' and (emp1 <> 0 or emp2 <> 0 or emp3 <>
0)))

That's a little more complicated but here's my attempt to explain it.
In the base table we have disclosure codes of 0, 1 or 2. In the case of
records where ownership = 0 (which is another condition in my where
clause). Generally - for most things, we only want to pull and show
data where disc = 0 - however, in this case, if there is a disc <> 0 AND
there are actual data values > 0 in any of the emp fields, we want to
pull that record and create average wage. The reason I need the
condition is that there can be true zeros in a field, but I don't want
to pull back any records where disc <> 0 and there are zeros in any of
the emp field. This gives me a divide by zero error.

Does that make sense?

Thanks!
Bethany


*** Sent via Developersdex http://www.developersdex.com ***

[Doug]

it isn't what you asked, but I hate using indexed views. It seems like
every time I tried to use one, the stupid engine goes and recreates the
index from scratch doing a giant hash table in an incredibly
inefficient way not using any half way intelligent existing indexes.
So, if you used the indexed view inside a cursor, or in a stored
procedure that gets called repeatedly, the goofy thing would rebuild
itself each time it was called.

made things sooooo slowwwww.

to fix, I copied the really ugly code from the view directly into the
SPROC, documented the heck out of everything, and then the engine would
actually use already existing indexes to make intelligent decisions.

YMMV.

[Alexander Kuznetsov]

Doug,

I don't understand what you are speaking about. I see 2 other problems
with indexed views:

1. sometimes the optimizer just won't use them

2. there may be a lot of lock contention on them.

Can you provide more details?