Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Roll Your Own Materialized Views

By Mark Stockwell,

This procedure is called usp_mview, it will create and maintain a set of tables based on a view. In reporting application (Cognos, BusinessObjects) views are often created to simplify SQL or perform some business logic. Often the views are complex, resulting in poor performance. An indexed view can be created, but the schema binding and update frequency often presents problems. This procedure will create and update a set of materialized views similar to Oracle's dbms_mview package.

The procedure depends on the mview_config table, DDL in comments section of procedure. Create this table and add the views you wish to materialize. A primary key (single column only) should be specified. Note that the primary key specified need not be unique, only high enough cardinality to ensure that multiple rows with the same key return different CHECKSUM values. Procedure can be scheduled from any scheduling tool that can execute an SQL command.


  • The procedure uses a configuration table called mview_config. This table lists all the tables/views that you wish to materialize.
  • The mview_config table has a column called primary_key. This can only be a single column name. It does not need to be unique, but should have high cardinality (i.e. material_number).
  • The procedure takes a parameter @pAction. This can be 'update' or 'create'. It defaults to update.
  • If create is specified, the procedure cycles through the config table and creates an identical snapshot of the base table/view with the prefix "mv_". So the view Plan_Sales is materialized as mv_ Plan_Sales.
  • For the create routine, a single nonclustered index is created on the primary_key column from config table. You will probably want to add more.
  • For the update routine, a delete/insert strategy is used. First a hash table is contructed with primary key and checksum values from both tables, using a full outer join. Most rows in the two tables are probably the same, so this hash table will only have a few thousand rows.
  • The update routine looks at the combination of PK+checksum for uniqueness. Based on this, there will be some rows in the mv table that are stale, these are not updated but deleted. Rows that are deleted from base table also fall into this group.
  • The Insert routine adds all rows that have actually been updated or inserted in base table.
  • The mview_config table is updated at end of procedure with last run data. You may want a running log table at some point.

Hope you find this useful-

Mark Stockwell


Total article views: 1294 | Views in the last 30 days: 2
Related Articles

view and mview

what is difference b/w view and mview


create Procedure within Procedure

create Procedure within Procedure


Store Procedure to update

Store Procedure to update


Create standard CreateUpdate Procedure in T-SQL

This stored procedure saves time by creating a standard procedure to create or update a row.


Single config file for multiple SSIS packages

Single config file for multiple SSIS packages


Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones