Problems with Indexing a view to a single table.

[danno64]

I have a view into a single table in which I've added
some fields to the view that are derived from combinations
of other fields. That's the reason why I created the view.

Most of these derived fields are caclulated financial
data, but one field is just an identifier that is
manipulated based on data in 3 other fields.

The view was not created by JOINing tables, there is
no WHERE claused used to limit rows, and no aggregate
functions such as MAX, SUM, or DISTINCT were used.

The original table itself is indexed on it's primary key
which is an identity field.

However, when I try to create a UNIQUE CLUSTERED index on
the derived identifier field in the view, I get a warning
that says:

"Warning: The optimizer cannot use the index because the
select list of the view contains a non-aggregate
expression."

I'm not sure why I'm getting this warning....but true to
their word, when I run an execution plan on a query that should make use of the view's index
(e.g.
SELECT * FROM vMyView WHERE MyDerivedIdent = '1234'),
it tells me it's using a Table Scan. It ignores the index.

My questions are:

1. Is there something inherent about trying to index a
view that is a view to one table? Or is there a
problem with indexing fields that exist only in the view?

2. Why does the lack of a non-aggregate expression in
the select list prohibit the index from being used. I
guess I do not totally understand what this is telling me.

Any help would be appreciated.
Thank-you

 

[ClayG]

Have you tried creating an index without the Unique and Clustered properties ?

 

[danno64]

The first index on a view must be unique and clustered.

 

[vongrunt]

There are bazillion requirements for indexed views - ANSI_NULLS set, schema binding, determinism, blah. Show us code for creating view+index.