Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

INDEX Not Implemented when a Materialized View is Created from Another

DZone's Guide to

INDEX Not Implemented when a Materialized View is Created from Another

· Java Zone
Free Resource

What every Java engineer should know about microservices: Reactive Microservices Architecture.  Brought to you in partnership with Lightbend.

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.

Microservices for Java, explained. Revitalize your legacy systems (and your career) with Reactive Microservices Architecture, a free O'Reilly book. Brought to you in partnership with Lightbend.

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 DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

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

X

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

{{ parent.tldr }}

{{ parent.urlSource.name }}