INDEX not implemented when a Materialized view is created from another

Introduction

There is a limitation of view that we must understand is if a materialized view is created by another materialized view, in this case we cannot configured index on Second Materialized view

Please try to understand the bellow pictorial diagram.

Materialized View 1  à

                              Used to Create  à  

                                                Materialized View 2  ß Cannot Configure Index

This article is related to it.

Example to understand

Step - 1 [ Create the Base Table ]

IF OBJECT_ID(N'dbo.tbl_EMPLOYEE', N'U') IS NOT NULL

   BEGIN

      DROP TABLE [dbo].[tbl_EMPLOYEE];

   END

GO

CREATE TABLE [dbo].[tbl_EMPLOYEE]

       (

         EMPID    INT         NOT NULL,

         EMPNAME  VARCHAR(50) NOT NULL,

         EMPGRADE CHAR(1)

       );

GO

Step – 2 [ Insert Some Records ]

INSERT INTO  [dbo].[tbl_EMPLOYEE]

      (EMPID, EMPNAME, EMPGRADE)

VALUES(1, 'Joydeep Das', 'A'),

      (2, 'Sukamal Jana', 'A'),

      (3, 'Sangram jit', 'B'),

      (4, 'Souman Bhowmik', 'C');

GO

Step – 3 [ Create First VIEW ]

IF OBJECT_ID(N'dbo.view_EMPLOYEE_1', N'V') IS NOT NULL

   BEGIN

      DROP VIEW [dbo].[view_EMPLOYEE_1];

   END                        

GO

CREATE VIEW [dbo].[view_EMPLOYEE_1]

WITH SCHEMABINDING

AS

SELECT  EMPID, EMPNAME, EMPGRADE

FROM    [dbo].[tbl_EMPLOYEE];

GO 

Step – 4 [ Create Second VIEW by Using First VIEW ]

IF OBJECT_ID(N'dbo.view_EMPLOYEE_2', N'V') IS NOT NULL

   BEGIN

      DROP VIEW [dbo].[view_EMPLOYEE_2];

   END                        

GO

CREATE VIEW [dbo].[view_EMPLOYEE_2]

WITH SCHEMABINDING

AS

SELECT  EMPID, EMPNAME, EMPGRADE

FROM    [dbo].[view_EMPLOYEE_1];

GO

Step – 5 [ Creating the UNIQUE CLUSTERED Index on Second VIEW and Error occurs]

CREATE UNIQUE CLUSTERED INDEX IX_view_EMPLOYEE_2

ON [dbo].[view_EMPLOYEE_2](EMPID);

Msg 1937, Level 16, State 1, Line 2

Cannot create index on view 'MATRIXSYSDB.dbo.view_EMPLOYEE_2'

because it references another view 'dbo.view_EMPLOYEE_1'.

Consider expanding referenced view's

definition by hand in indexed view definition.

Reason for That

The reason for this is that another view over a view is difficult to maintain

What to do to solve it

Use the SELECT statement of first view within the second view.

Hope you like it.

Posted by: MR. JOYDEEP DAS