Materialized view or table function in SQL 2005

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Lynn - 11 May 2007 19:27 GMT

Hi,

Please advise whether SQL 2005 has the smiliar function as belows:

Materialized View in Oracle
Materialized Query Tables in DB2

Thank you.

Signature

Best Regards,

Lynn

Reply to this Message

MC - 12 May 2007 08:02 GMT

Im not sure since I dont work with either Oracle or DB2 but I believe
indexed view would be something like that.

MC

> Hi,
>
[quoted text clipped - 4 lines]
>
> Thank you.

Reply to this Message

Lynn - 14 May 2007 22:24 GMT

The purpose of this function is that the view or table is not repopulated the
data when it's queried. Usually, the data is already stored in that kind of
table when the original table is updated. When this kind of view/table is
queried, the data already exists without parsing the query to original table
and repopulated the table again. Indexed view in SQL 2005 don't have this
function.

Anyway, thanks.

Signature

Best Regards,

Lynn

> Im not sure since I dont work with either Oracle or DB2 but I believe
> indexed view would be something like that.
[quoted text clipped - 9 lines]
> >
> > Thank you.

Reply to this Message

Jeje - 14 May 2007 23:45 GMT

indexed views store the aggregate result of a query (group by something
queries) and when the source table change, the view content is updated too.
like a table, you can create an index on it.

for example, if you always want to sum the sales by product, the indexed
view will contains the result of this grouping with an index on the product
column. when the source table is updated the view is updated too at the same
time so the total by product contains the new total.
when a user ask for the total of sales by product (or the sales for a group
of products or all the products) SQL server will use the indexed views
instead of scanning the big source table.

so its exactly the result you looking for.

> The purpose of this function is that the view or table is not repopulated
> the
[quoted text clipped - 21 lines]
>> >
>> > Thank you.

Reply to this Message

Luke - 22 May 2007 03:36 GMT

Lynn,

"On commit" materialized views in Oracle are conceptually the same as schema
bound views in SQL Server which have had a unique clustered index applied
prior to any other index. The Oracle materialized view grew out of the
snapshot functionality and has abilities like stale tolerance that do not
appear to be part of SQL Server Indexed Views.

The usage of indexed views can drastically increase the performace of select
queries at the cost insert, update, and delete. The following is a
reasonable introductory article which covers the concept:
http://www.microsoft.com/technet/prodtechnol/sql/2005/impprfiv.mspx

Luke

> Hi,
>
[quoted text clipped - 4 lines]
>
> Thank you.

Reply to this Message