Wednesday 7 May 2014

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

2 comments: