materialized view vs. denormalized table

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, ChrisRChrisR,
A materialized view (i.e. Indexed View) is a denormalized table that the
system keeps up-to-date for you. So, no you would not have to repopulate
it.
Having said that, there are several rules that you have to follow to get an
Indexed View created, so you should carefully read the restrictions in the
Books Online.
Russell Fields
"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|||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|||The cost of SQL Serve maintaining an indexed view is fairly steep, compared
to you doing the work yourself. However if you denormalize you may have to
change apps as well, which would not be required for an indexed view... Also
the optimizer will automatically choose the indexed view ONLY if you are
running the Enterprise edition. In the standard edition you may create an
indexed view, but it will only be used when someone references the view
name...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"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