Over a million developers have joined DZone.

INDEX Not Implemented when a Materialized View is Created from Another

· Java Zone

Thrive in the application economy with an APM model that is strategic. Be E.P.I.C. with CA APM.  Brought to you in partnership with CA Technologies.

Introduction

There is a limitation of views that we must understand: if a materialized view is created by another materialized view, we cannot configure the index on the Second Materialized view.

Please try to understand the below pictorial diagram:

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.

Navigate the Maze of the End-User Experience and pick up this APM Essential guide, brought to you in partnership with CA Technologies

Topics:

Published at DZone with permission of Joydeep Das, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}