Warning

Question

Warning

Fracn

Old Hand

Points: 389
More actions
July 16, 2007 at 5:26 am

#92565

Hello!
While creating a view and related index, I received the following warning:
Warning: The optimizer cannot use the index because the select list of the view contains a non-aggregate expression.
What does it mean?
Thanks!!!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 1

It means that you are using functions inside the select statement of the view and the index that youhave created on the view will not be used by the query optimizer to enhance performance.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

Stewart Joslyn SSCertifiable Points: 6131 More actions · Answer 2

To expand on Sugesh's post,

SELECT SUM(Column1) FROM MyTable would be OK because SUM() is an aggregate expression. SELECT Column1 + Column2 FROM MyTable is not OK because this is an expression but is not an 'aggregate'.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

To further the explantion...

You cannot put indexes on views unless they are specifically designed to be "Indexed Views". There's a long list of requirements to make an Indexed View in Books Online...

So far as normal indexes go, they can and do use the indexes on the underlying tables as would any SELECT.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)