Dose it make sense to create indexted views on a single table?

Hi, all experts here,

Thank you very much for your kind attention.

I am wondering if there is any sense to create indexed views on single table? I simple want to improve the report query performance as most of the reports data are from a single table. As views most of the time are created as for joined across tables.

Thank you very much for your advices and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

H Helen,

Theres is no point creating a indexed view with a view that has the columns from single table.

Infact, you will creating an overhead on your transactions as they will have to keep the indexes on view upto date on top the indexes on the table.

regards

Jag

|||

Hi, Jag,

Thank you very much for your advices and kind help. It's been very helpful.

With best regards,

Yours sincerely,

|||

Jag,

What if the single table was a transaction table, and the view was an aggregation of that transaction table.

I was under the impression that that was one of the primary reasons to utilise Indexed Views, that the aggregated data is available without having to transverse the underlying table rows, therebye improving query performance.

Cheers,

Will

|||

i would agree with Will ... though the view is refering single table... if this view is aggregation and the table is having millions of records, it make sense that you may create indexed view.

Madhu

|||

Hi Will,

You are right, if view is going to be storing aggregated data, then It is worth creating an index on a view even if is a single table.

regards

Jag

Does this make sense ?

We have one publication having all the tables, then another publication for
stored procs, yet another for views and one for indexed views. And all set
up for transactional replication. I understand the need for the tables
portion. Right now, the stored procs, views and indexed views are just to
have their schemas on the subscriber as a one time. I believe its an
overkill to have those set up as transactional replication right ? We could
maybe set them up as snapshot replication ... right ?
Thanks
Hassan,
for stored procedures and views and UDFs in many ways it doesn't make any
difference, however sp_addscriptexec can be useful in the case of incorrect
dependencies, and it is not available with snapshot replication. For this
reason I'd recommend you use transactional. NB be sure to select the option
to have an independant distribution agent, so problems synchronizing the
views publication won't block the tables one.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Other than Paul's excellent advise - keep in mind the sp_addscritpexec
only works for subscribers deployed through UNCs.
You may also need to run sp_refreshsubscriptions before re-running any
publications which contain procs, views, and functions which you have
modified. IIRC Paul pointed out to me that replication is not aware of
changes to procedures which it is publishing and you need to drop and
recreate these publications.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com