Re: materialized view vs. denormalized table

From: Rick Sawtell (ricksawtell_at_hotmail.com)
Date: 09/07/04 

Date: Tue, 7 Sep 2004 12:56:45 -0700

A denormalized table is simply a table that doesn't follow the 3nf
standards. This happens a lot to improve efficiency of the queries at the
cost of storage space.

So a denormalized table may have duplicate columns, data or other items in
it that violate the normal forms.

A materialized view however is simply a view that has been created on a
table(s) that has then had a clustered index created for that view. SQL
Server stores the clustered index on the view. This can make queries much
faster. This is especially true in instances where there is a large amount
of calculations or aggregations going on in the query itself.

You do not have to repopulate the clustered index as data is modified in the
base tables. This happens automagically. =)

There is more to learn about this topic. Check out materialized views in
the Books Online.

HTH

Rick Sawtell
MCT, MCSD, MCDBA

"ChrisR" <anonymous@discussions.microsoft.com> wrote in message
news:799d01c49512$2e6bc060$a301280a@phx.gbl...
> sql2k sp3
>
> Whats the difference between these two? Isn't a
> materialized view nothing more than a denormalized table?
> Dont you need to update/ repopulate them both if data in
> the underlying table changes?
>
>
> TIA, ChrisR

Relevant Pages

  • Re: Massive Large Query Issues
    ... Project Budgets: Entered at the Capital Request level, ... installed (One install is typically $100,000 plus the cost of the equpment, ... install SQL Server. ... Often, a collection of queries ...
    (microsoft.public.access.queries)
  • Re: FTS Performance in SQL 2005
    ... FTS returns all the keys that match the criteria it will then join to your main table for each key returned, if the indexing is not right then you will end up reading all the data from the main table. ... I know that the upper queries are mostly extremes, ... query on FTS, almost no CPU/Memory is used by the service at all - I ... window will then give you a percentage query cost relative to the ...
    (microsoft.public.sqlserver.fulltext)
  • Re: FTS Performance in SQL 2005
    ... Because what really buggers me is that if I execute a big query on FTS, ... To compare the queries, put them all into Query Analyser and turn on Show ... then give you a percentage query cost relative to the whole batch, ... select ID from STK where CONTAINS ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Index Performance
    ... What is true for SQL-Server 7.0 and above is especially true for 6.5: ... the clustered index is very useful for ... queries you describe). ... > StartDate datetime NULL, ...
    (microsoft.public.sqlserver.server)
  • Re: Need to tune a table for performance gains
    ... it didnot have any pks and hence no clustered index. ... IX_4 on ColA, ColB and ColC put together. ... Queries performed in this table are very slow. ...
    (comp.databases.ms-sqlserver)