A question on indexed views and the optimizer ...

Gentle SQL gurus,
Say I have a table "a" which has a column "ca". And I have a table "b"
which has a column "cb".
And I define a view "ajoinb" as
create view ajoinb as
select ..., a.ca, b.cb, ...
from a
inner join b
on b.fktoa = a.pk
And then I create an index on this view "ajoinb" as
create index "indexedviewonajoinb" on ajoinb (ca, cb)
Presumably if I run a query like
select ...
from ajoinb
where ca = <value a>
and cb = <value b>
then the optimizer will use the indexed view.
But what if the query is like this:
select ...
from a
inner join b
on b.fktoa = a.pk
where a.ca = <value a>
and b.cb = <value b>
Will the optimizer still use the index I created called
"indexedviewonajoinb" even though I am not explicitly doing the select
on the view "ajoinb"?
A second question: What is the data structure for an indexed view? For
a regular index you have a key and the value is a pointer to the
underlying table. For an indexed view does SQL server store one pointer
to each table in the view for each key in the index?
Thanks in advance for any and all help.
Regards,
Anil> Will the optimizer still use the index I created called
> "indexedviewonajoinb" even though I am not explicitly doing the select
> on the view "ajoinb"?
Probably. But you need to test to make sure. Also, only on EE. On SE you must reference the view and
also use the NOEXPAND hint for the index on the view to be used.
> A second question: What is the data structure for an indexed view?
The first index you create in the view need to be both unique and clustered. So the structure is
just like any old clustered index. The key you define for this index on the view is what the index
is ordered on, and the other columns you have as "bonus" in the leaf level of that index.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<sqlpractitioner@.gmail.com> wrote in message
news:1160671653.825698.82430@.i3g2000cwc.googlegroups.com...
> Gentle SQL gurus,
> Say I have a table "a" which has a column "ca". And I have a table "b"
> which has a column "cb".
> And I define a view "ajoinb" as
> create view ajoinb as
> select ..., a.ca, b.cb, ...
> from a
> inner join b
> on b.fktoa = a.pk
> And then I create an index on this view "ajoinb" as
> create index "indexedviewonajoinb" on ajoinb (ca, cb)
> Presumably if I run a query like
> select ...
> from ajoinb
> where ca = <value a>
> and cb = <value b>
> then the optimizer will use the indexed view.
> But what if the query is like this:
> select ...
> from a
> inner join b
> on b.fktoa = a.pk
> where a.ca = <value a>
> and b.cb = <value b>
> Will the optimizer still use the index I created called
> "indexedviewonajoinb" even though I am not explicitly doing the select
> on the view "ajoinb"?
> A second question: What is the data structure for an indexed view? For
> a regular index you have a key and the value is a pointer to the
> underlying table. For an indexed view does SQL server store one pointer
> to each table in the view for each key in the index?
> Thanks in advance for any and all help.
> Regards,
> Anil
>