Hi Rekha,

I think Anith's suggestion is good. I collected some more information for
you. You can create indexed views only if you install SQL Server 2000
Enterprise Edition or Microsoft SQL Server 2000 Developer Edition.

I am not sure if you want to achieve better performance with indexed views.
Improved performance depends on the queries used in the reports. Indexed
views work best when the underlying data is infrequently updated. If the
underlying data is updated frequently, then the cost of maintaining the
indexed view data may outweigh the performance benefits of using the
indexed view.

Indexed views improve the performance of these types of queries:
1) Joins and aggregates processes for many rows.
2) Joins and aggregates operations that are frequently performed by many
queries.
3) Decision support workloads.

Indexed views usually do not improve the performance of these types of
queries:
1) OLTP systems with many writes.
2) Databases with many updates.
3) Queries that do not involve aggregations or joins.
4) Aggregations of data with a high degree of cardinality for the key. A
high degree of cardinality means the key contains many different values.
5) Expanding joins, which are views whose result sets are larger than the
original data in the base tables.

You may refer to SQL Server Books Online,Topic: Designing an Indexed View.
[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm[/url]
_8_des_06_6ptj.asp

This posting is provided "AS IS" with no warranties, and confers no rights.
Sincerely,
William Wang
Microsoft Partner Online Support

--------------------
| Content-Class: urn:content-classes:message
| From: "Rekha" <someonesomewhere.com>
| Sender: "Rekha" <someonesomewhere.com>
| Subject: Database design confusion
| Date: Tue, 8 Jul 2003 12:08:34 -0700
| Lines: 32
| Message-ID: <019601c34584$545daa10$a501280aphx.gbl>
| MIME-Version: 1.0
| Content-Type: text/plain;
| cht="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Thread-Index: AcNFhFRdMnhvgMUcStWBg6X73+RmlQ==
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Newsgroups: microsoft.public.sqlserver.programming
| Path: cpmsftngxa09.phx.gbl
| Xref: cpmsftngxa09.phx.gbl microsoft.public.sqlserver.programming:35786
| NNTP-Posting-Host: TK2MSFTNGXA13 10.40.1.165
| X-Tomcat-NG: microsoft.public.sqlserver.programming
|
| Hi,
|
| The products we design in our company range from using
| MSDE in our low end to using SQL Server Standard Edition
| for our high end. We currently have a couple of tables
| normalized for inserts. We now need to think of writing
| meanigful reports and as such we have two trains of
| thought at work:
|
| 1) create indexed views and then write reports based on
| this(BTW, we are using Crystal Reports). This would be a
| new thing for us(previously we've only used stored
| procedures and regular views). Since it's a table with
| potentially constant inserts(it's logging data from
| various devices), will indexed views help?
|
| 2)find a way(Triggers or a .NET component) to constantly
| move the data from the single table to several smaller
| tables. Problem is here we have to ship the shrink wrap
| product with all the maintenance utilities(archiving,
| backup, importing etc..)so that could be a lot of work.
| Should the smaller tables be a static number of
| dynamically created tables?
|
| I'd like some good advice on a safe approach to start out
| with. This is the start of a new generation of products
| for us and a lot of our applications will be built on this
| database, so good design is crucial.For now, I foresee
| only scaling up and scaling out. Any help or references
| would be immensely appreciated.
|
| Thanks
|