Create Materialized View

This blog is about how to create a materialized view in SQL server database. By creating a materialized view, you can dramatically improve query performance. I learned this by my experience. Here are some key notes about the process.

Create a View

The first step you have to create a view. The first requirement for materialized view in SQL server is that a special WITH SCHEMABINDING has to be used for the view.

CREATE VIEW [dbo].[vMyView] WITH SCHEMABINDING
AS
SELECT rawid,
  readingdate,
  CAST(VALUE AS FLOAT) AS VALUE,
  name
FROM   dbo.myRawDataTable

WHERE  ( VALUE IS NOT NULL )
  AND ( Isnumeric(VALUE) = 1 )
  AND Name LIKE '%my_pattern%'

The second requirement is to make sure there are any keys in the view. For example, you can include key column or combination of columns as a unique identifier. This key will be used to create INDEX for the view.

Add a Index

The materialized view actually holds a block of data with Index. This makes view fast for retrieving data. For example, for above view, a INDEX is created:

-- Create UNIQUE CLUSTERED index on the view
CREATE UNIQUE CLUSTERED  INDEX IVW_vMyViewUniqueIndex
  ON [vMyView] (rowid, name, readingdate)

The Index is created depending on your query. With this Index, you will see great performance improvement, specially again huge amount of data.

Use (NOEXPAND) in SQL Query

With above preparation, the view is ready for use. However, I noticed that a special option has to be used in SQL query in order to take advantage of the Indexed view, for example:

SELECT *
FROM [vMyView] (NOEXPAND);

This NOEXPAND has to be used in SQL Standard Server. In SQL Enterprise Edition Server, this is not required (but you have to pay big bucks).

Conclusion

Materialized View can improve SQL query performance dramatically, just like adding property Index to tables. The disadvantage is that the MV will take some disk spaces. You have to balance the weight in your real cases.