Materialized view in sql server

Question

Post reply

Materialized view in sql server

karthikaug18

Say Hey Kid

Points: 662
More actions
January 31, 2012 at 2:59 am

#249958

Can we create materialized view in sql server.?
Is materialized view avialable in sql server like oracle.?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

poojari045 SSChasing Mays Points: 654 More actions · Answer 1

Pls check this.

http://msdn.microsoft.com/en-us/library/ms191432.aspx

karthikaug18 Say Hey Kid Points: 662 More actions · Answer 2

Thanks.

I have seen the difference between materialized view and Views. and i have question in it.?

Materialized views are disk based and update periodically base upon the query definition, Views are virtual only and run the query definition each time they are accessed.

Does the indexed view will update periodically on commit like in oracle.?

Paul White SSC Guru Points: 150467 More actions · Answer 3

karthikaug18 (1/31/2012)
Does the indexed view will update periodically on commit like in oracle.?

Any data modification query that affects an indexed view maintains the view directly in the query plan that performs the modification, in much the same way non-clustered indexes are maintained when changing data in the table they are defined on. Indexed view maintenance is synchronous and performed internally at the SERIALIZABLE isolation level to guarantee consistency. Other transactions can read the changes before commit if they are running at READ UNCOMMITTED.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

karthikaug18 Say Hey Kid Points: 662 More actions · Answer 4

Thanks

so if i am doing select * from (indexed_view) does it touches the base tables to retrieve the data.?

Paul White SSC Guru Points: 150467 More actions · Answer 5

karthikaug18 (1/31/2012)
so if i am doing select * from (indexed_view) does it touches the base tables to retrieve the data.?

It might do. In Developer and Enterprise Edition the optimizer will choose between accessing the indexed view directly or expanding the definition and querying the base table(s). It might even choose to access the indexed view even if you don't reference it directly in the query. In other editions (e.g. Standard) you have to use the WITH (NOEXPAND) table hint on the indexed view to use it directly.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi