Limitation of index (materialized) view

An indexed view is a view whose result set is physically stored as index structures and maintained synchronously with base tables. Its limitations exist because SQL Server must guarantee deterministic, transactionally consistent maintenance of that stored data during every base table modification.

We can not used below in the Materialized view

Ø  TOP

Ø  Text, ntext or image columns

Ø  DISTINCT

Ø  MIN, MAX, COUNT, STDEV, VARIANCE, AVG

Ø  SUM on a nullable expression

Ø  A derived table

Ø  Row set function

Ø  Another sample view

Ø  UNION

Ø  Subqueries, outer joins, self joins

Ø  Full-text predicates like CONTAIN or FREETEXT

Ø  COMPUTE or COMPUTE BY

Ø  Cannot include order by in view definition

Let’s see the demo

Creating a table and inserting few records.

CREATE TABLE dbo.Salesdemo (     SaleID INT IDENTITY PRIMARY KEY,     CustomerID INT NOT NULL,     Amount MONEY NOT NULL );  INSERT INTO dbo.Salesdemo (CustomerID, Amount) VALUES (1, 100), (1, 200), (2, 300), (2, 400);

Now creating a view using distinct keyword.

CREATE VIEW dbo.vw_SalesAgg with schemabinding AS SELECT DISTINCT     CustomerID FROM dbo.Salesdemo;

View created successfully. Now creating index on this view.

CREATE UNIQUE CLUSTERED INDEX IX_vw_SalesAgg ON dbo.vw_SalesAgg (CustomerID);

Index is not created getting the below error.

 

Let’s see with Aggregate function

Creating view

View created successfully

Now creating index on this view

Not able to create index on this view.

Let’s try with UNION or UNION ALL

Creating view with union

CREATE VIEW dbo.vw_Sales_union_demo with schemabinding AS SELECT     CustomerID,               Amount FROM dbo.Salesdemo union SELECT     CustomerID,               Amount FROM dbo.Salesdemo;

View created successfully. Now creating index on this view

CREATE UNIQUE CLUSTERED INDEX IX_vw_SalesAgg ON dbo.vw_Sales_union_demo (CustomerID);