Question about Indexed VIEWs.

(SQL Server 2000, SP3a)
Hello all!
I've got a question about Indexed VIEWs that some one can assist with.
If I have an Indexed VIEW that does some aggregation:
create view [dbo].[MyTableSummary] with schemabinding
as
select [PK] = mt.[PK],
[Count] = count_big(*),
[CountA] = sum(case when (mt.[DateCreatedA] is NULL) then 0 else 1 e
nd),
[CountB] = sum(case when (mt.[DateCreatedB] is NULL) then 0 else 1 e
nd)
from [dbo].[MyTable] as mt with (nolock)
group by mt.[PK]
go
And I create the index:
create unique clustered index [CI_MyTableSummary] on [dbo].[MyTa
bleSummary]([PK])
go
Will it denormalize/persist the entire results of the VIEW, or is it just go
ing to persist
the contents of the index(es) and still continue to evaluate the VIEW every
time it's
used? Is there a way to essentially have it persist every column in the VIE
W so that it
doesn't need to evaluate the VIEW every time, if so?
Thanks for any help you can provide!
John PetersonThe entire view will be materialized.
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:eZvSn7REEHA.2600@.TK2MSFTNGP09.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> I've got a question about Indexed VIEWs that some one can assist with.
> If I have an Indexed VIEW that does some aggregation:
> create view [dbo].[MyTableSummary] with schemabinding
> as
> select [PK] = mt.[PK],
> [Count] = count_big(*),
> [CountA] = sum(case when (mt.[DateCreatedA] is NULL) then 0 else 1[
/color]
end),
> [CountB] = sum(case when (mt.[DateCreatedB] is NULL) then 0 else 1[
/color]
end)
> from [dbo].[MyTable] as mt with (nolock)
> group by mt.[PK]
> go
> And I create the index:
> create unique clustered index [CI_MyTableSummary] on
[dbo].[MyTableSummary]([PK])
> go
> Will it denormalize/persist the entire results of the VIEW, or is it just
going to persist
> the contents of the index(es) and still continue to evaluate the VIEW
every time it's
> used? Is there a way to essentially have it persist every column in the
VIEW so that it
> doesn't need to evaluate the VIEW every time, if so?
> Thanks for any help you can provide!
> John Peterson
>|||Thanks Adam -- I thought (hoped) so, but I wasn't sure. Can you direct me t
o a BOL (or
other) reference that would confirm this?
Thanks again!
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:OY$UjASEEHA.1368@.TK2MSFTNGP11.phx.gbl...
> The entire view will be materialized.
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:eZvSn7REEHA.2600@.TK2MSFTNGP09.phx.gbl...
> end),
> end)
> [dbo].[MyTableSummary]([PK])
> going to persist
> every time it's
> VIEW so that it
>|||"Creating an Indexed View" in BOL.
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:e05UjHSEEHA.3424@.tk2msftngp13.phx.gbl...
> Thanks Adam -- I thought (hoped) so, but I wasn't sure. Can you direct me
to a BOL (or
> other) reference that would confirm this?
> Thanks again!
>
> "Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in
message
> news:OY$UjASEEHA.1368@.TK2MSFTNGP11.phx.gbl...
else 1
else 1
just
the
>|||LOL! I thought I read that -- but I must have missed the germane nugget! ;
-)
Thanks Adam!
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:uaHE2JSEEHA.3696@.TK2MSFTNGP10.phx.gbl...
> "Creating an Indexed View" in BOL.
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:e05UjHSEEHA.3424@.tk2msftngp13.phx.gbl...
> to a BOL (or
> message
> else 1
> else 1
> just
> the
>|||Ah...I think this blurb at the end has what I was looking for:
<Quote>
Although only the columns that make up the clustered index key are specified
in the CREATE
UNIQUE CLUSTERED INDEX statement, the complete result set of the view is sto
red in the
database. As in a clustered index on a base table, the B-tree structure of t
he clustered
index contains only the key columns, but the data rows contain all of the co
lumns in the
view result set.
</Quote>
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:uKpSoQSEEHA.2700@.tk2msftngp13.phx.gbl...
> LOL! I thought I read that -- but I must have missed the germane nugget!
;-)
> Thanks Adam!
>
> "Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in messa
ge
> news:uaHE2JSEEHA.3696@.TK2MSFTNGP10.phx.gbl...
>|||How about :
"If such views are frequently referenced in queries, you can improve
performance by creating a unique clustered index on the view. When a unique
clustered index is created on a view, the view is executed and the result
set is stored in the database in the same way a table with a clustered index
is stored. "
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:OVU8rSSEEHA.1128@.TK2MSFTNGP11.phx.gbl...
> Ah...I think this blurb at the end has what I was looking for:
> <Quote>
> Although only the columns that make up the clustered index key are
specified in the CREATE
> UNIQUE CLUSTERED INDEX statement, the complete result set of the view is
stored in the
> database. As in a clustered index on a base table, the B-tree structure of
the clustered
> index contains only the key columns, but the data rows contain all of the
columns in the
> view result set.
> </Quote>
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:uKpSoQSEEHA.2700@.tk2msftngp13.phx.gbl...
nugget! ;-)
message
direct me
with.
0
0
is it
VIEW
column in
>|||Heh! That too! ;-)
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:OY4xTXSEEHA.3568@.tk2msftngp13.phx.gbl...
> How about :
> "If such views are frequently referenced in queries, you can improve
> performance by creating a unique clustered index on the view. When a uniqu
e
> clustered index is created on a view, the view is executed and the result
> set is stored in the database in the same way a table with a clustered ind
ex
> is stored. "
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:OVU8rSSEEHA.1128@.TK2MSFTNGP11.phx.gbl...
> specified in the CREATE
> stored in the
> the clustered
> columns in the
> nugget! ;-)
> message
> direct me
> with.
> 0
> 0
> is it
> VIEW
> column in
>