Using indexed views in SQL Server (materialized view)

Materialized view: The normal view used before, the normal view is a logic statement, the performance does not have any elevation, and can not create indexes, and materialized view will be the view of the data queried in the database to create a snapshot, it and the physical table, can be created index, PRIMARY key constraints and so on, performance will have a qualitative improvement, But it has shortcomings, will occupy, you can set it to automatically update a time, can also be manually updated, of course, can be set up in time to update, but will slow down the base table of additions and deletions to check the operation, here I only talk about ideas, specific words we can study on their own.

+ View Code?

--创建物化视图，每天晚上22:00:00自动更新 create materialized view VM_PSNPERSONINFO refresh force on demand start with sysdate next to_date( concat( to_char( sysdate+1,‘dd-mm-yyyy‘),‘ 22:00:00‘),‘dd-mm-yyyy hh24:mi:ss‘) as SELECT dt.personid, c.employeeid,d.unitcode,d.unitname,d.label,        dt.unitid,dt.startdate,dt.enddate   FROM (          SELECT DISTINCT b.personnelid personid,SUBSTR (a.effectdate, 1, 10) startdate,          MIN (CASE WHEN a.effectdate < b.effectdate THEN b.effectdate ELSE N‘9999-99-99‘ END) AS enddate,          MIN (a.adjustresult) unitid          FROM psnadjust a LEFT JOIN psnadjust b          ON a.personnelid = b.personnelid          WHERE a.adjusttype = ‘2‘ AND b.adjusttype = ‘2‘          GROUP BY b.personnelid, a.effectdate         ) dt        INNER JOIN psnaccessioninfo c            ON c.personid = dt.personid        INNER JOIN orgstdstruct d            ON d.unitid = dt.unitid ORDER BY employeeid, unitcode, startdate;

　　

Http://www.cnblogs.com/zhxhdean/archive/2011/05/16/2047795.html

http://blog.163.com/[email protected]/blog/static/561082842007235010598/

Http://www.cnblogs.com/liuzhendong/archive/2011/10/10/2205744.html