Common Error in case of Materialized View

Introduction

In this article we are trying to discuss about some common error related to materialize view. To understand the nature of the Error we are going to take an simple example. Hope it will be informative.

Example

Step-1 [ Creating the Base Table ]

IF OBJECT_ID(N'tbl_StudentDetails', N'U') IS NOT NULL

   DROP TABLE tbl_StudentDetails;

CREATE TABLE tbl_StudentDetails

       (

          StdRoll   INT           NOT NULL IDENTITY PRIMARY KEY,

          StdName   VARCHAR(50)   NOT NULL

       );

GO

Step-2 [ Insert some records in the Base Table ]

INSERT INTO tbl_StudentDetails

       (StdName)

VALUES ('Joydeep Das'), ('Santinath Mondal'), ('Avijit da');

GO

Step-3 [ Now Create a Simple View from the Base table ]

CREATE VIEW view_StudentDetails

AS

SELECT * FROM tbl_StudentDetails;

We find that the view is Created Successfully and there is no problem in case of simple view creation.

Step-4 [ Now Create a Materialized View with above Example ]

CREATE VIEW view_StudentDetails WITH SCHEMABINDING

AS

SELECT * FROM tbl_StudentDetails;

The error occurred:

Msg 1054, Level 15, State 6, Procedure view_StudentDetails, Line 26

Syntax '*' is not allowed in schema-bound objects.

So we are not providing star (*) in Materialized view and we must provide the column name instead of star (*).

Try to fix it by providing the column name in SELECT statement of materialized view.

CREATE VIEW view_StudentDetails WITH SCHEMABINDING

AS

SELECT StdRoll, StdName FROM tbl_StudentDetails;

Again another Error occurs:

Msg 4512, Level 16, State 3, Procedure view_StudentDetails, Line 33

Cannot schema bind view 'view_StudentDetails' because name 'tbl_StudentDetails' is

invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

Now the above error says that we must provide the Schema name with Table Name in case of materialized view.

CREATE VIEW view_StudentDetails WITH SCHEMABINDING

AS

SELECT StdRoll, StdName FROM dbo.tbl_StudentDetails;

Now it’s perfect and complied successfully.

Summary:

Form the above example we find that the in case of materialized view we must provide the column name within the SELECT statement and also provide the schema name in the Table object.

SELECT StdRoll, StdName FROM dbo.tbl_StudentDetails;

Hope you like it.

Posted by: MR. JOYDEEP DAS