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

Build vs Buy a Data Quality Solution: Which is Best for You? Gain insights on a hybrid approach. Download white paper now!

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.

Build vs Buy a Data Quality Solution: Which is Best for You? Maintaining high quality data is essential for operational efficiency, meaningful analytics and good long-term customer relationships. But, when dealing with multiple sources of data, data quality becomes complex, so you need to know when you should build a custom data quality tools effort over canned solutions. Download our whitepaper for more insights into a hybrid approach.

Topics:

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

Opinions expressed by DZone contributors are their own.

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

{{ parent.tldr }}

{{ parent.urlSource.name }}