Indexed Views

Hi,
I would like to know how indexing works for indexed views under the
following situation.
I have multiple indexed views which has a common base table. But all the
views have different set of data beacuse of certain join conditions with
other tables.
So what happens when i insert a new row into this common base table?
Does it re-index all the indexed views which uses this table?
What other overheads are there for insert/updates/deletes in the tables?
Can i possibly get any kind of SQL profiler trace as to what goes on behind
scene?
Thanks in advance.
Vikramhi Vikram,
I think i didnt get your question right.
View is not a table. its just a query or a virtual table. the indexes come
into force only when u extract data from a view.
over heads on table:
if u are using clustered index, then the data is sorted physically as btree
on the data pages.
if it a non clustered, an entry is made/ changed in the mapping table
you can check the complexity using "Execution Plan"
please let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Vikram Kamath" wrote:

> Hi,
> I would like to know how indexing works for indexed views under the
> following situation.
> I have multiple indexed views which has a common base table. But all the
> views have different set of data beacuse of certain join conditions with
> other tables.
> So what happens when i insert a new row into this common base table?
> Does it re-index all the indexed views which uses this table?
> What other overheads are there for insert/updates/deletes in the tables?
> Can i possibly get any kind of SQL profiler trace as to what goes on behin
d
> scene?
> Thanks in advance.
> Vikram|||> So what happens when i insert a new row into this common base table?
> Does it re-index all the indexed views which uses this table?
Yes, the clsuter index and data on the indexed views are updated. See
"Creating an Indexed View" in BOL. These paragraphs are from BOL.
*****
Creating a clustered index on a view stores the data as it exists at the
time the index is created. An indexed view also automatically reflects
modifications made to the data in the base tables after the index is created
,
the same way an index created on a base table does. As modifications are mad
e
to the data in the base tables, the data modifications are also reflected in
the data stored in the indexed view. The requirement that the clustered inde
x
of the view be unique improves the efficiency with which SQL Server can find
the rows in the index that are affected by any data modification.
Indexed views can be more complex to maintain than indexes on base tables.
You should create indexes only on views where the improved speed in
retrieving results outweighs the increased overhead of making modifications.
This usually occurs for views that are mapped over relatively static data,
process many rows, and are referenced by many queries.
*****
AMB
"Vikram Kamath" wrote:

> Hi,
> I would like to know how indexing works for indexed views under the
> following situation.
> I have multiple indexed views which has a common base table. But all the
> views have different set of data beacuse of certain join conditions with
> other tables.
> So what happens when i insert a new row into this common base table?
> Does it re-index all the indexed views which uses this table?
> What other overheads are there for insert/updates/deletes in the tables?
> Can i possibly get any kind of SQL profiler trace as to what goes on behin
d
> scene?
> Thanks in advance.
> Vikram|||On Wed, 10 Aug 2005 09:06:32 -0700, Vikram Kamath wrote:

>Hi,
>I would like to know how indexing works for indexed views under the
>following situation.
>I have multiple indexed views which has a common base table. But all the
>views have different set of data beacuse of certain join conditions with
>other tables.
>So what happens when i insert a new row into this common base table?
>Does it re-index all the indexed views which uses this table?
>What other overheads are there for insert/updates/deletes in the tables?
>Can i possibly get any kind of SQL profiler trace as to what goes on behind
>scene?
Hi Vikram,
I don't think that MS has published what exactly happens when an indexed
view is updated. But you could take a good look at the execution plan
and see if that gives any hints.
Here's what I *think* happens. Or rather, how I would have chosen to
implement it if that had been my job.
Suppose you have an indexed view like this:
SELECT Region, COUNT_BIG(*) AS Tally, SUM(Value) AS Total
FROM dbo.MyTable
GROUP BY Region
Now, when rows are inserted to, deleted from or updated in MyTable,
there's no need to recompute the count and the sum - simply adding the
number of new rows and the sum of their values to, and subtracting the
number of deleted rows and the sum of their values from the appropriate
regions would suffice.
But OTOH - maybe the MS engineers found a much better way...
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)