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

Index of Materialized View Not Working

Introduction

I got a mail from one of my closed friends related to "Materialized view Index not working". I am little bit confused after getting this mail. When I am getting closer look at the problem that I understand what he mean by "Materialized view Index not working" This article is related to Why in Materialized view Index is not working.

Point to focus

1.    Understanding the case scenario

2.    Finding the rood of the problem

3.    How we fix it.

4.    Related Tropics

In this article I am not going to describe related to Materialize View as we all know that how important it is in case of performance boost up. If have you any confusion visit my previous article. You can find it in related tropic options of this article.

Understanding the case scenario

Here in this article, I cannot provide the exact scenario due to some restrictions but I can create a scenario related to it. So let's see what the scenario is.

Step-1 [ Creating the Base Table ]

IF OBJECT_ID('my_BaseTable') IS NOT NULL

   BEGIN

        DROP TABLE my_BaseTable

   END

GO

CREATE TABLE my_BaseTable

       (ID      INT         NOT NULL IDENTITY(1,1) PRIMARY KEY,

        EMPNAME VARCHAR(50) NOT NULL,

        GRADE   VARCHAR(1)  NOT NULL)

GO

Step-2 [ Now Insert Some records on our Base Table ]

INSERT INTO my_BaseTable

       (EMPNAME, GRADE)  

VALUES ('Mr. Sudip Das', 'A'),

       ('Mr. Joydeep Das', 'B'),

       ('Mr. Sukamal Jana', 'B'),           

       ('Mr. Tuhin Kr. Shinah', 'C'),

       ('Mr. Sangram Jit', 'C'),

       ('Mr. Sourav Sarkar', 'D');

GO    

Step-3 [ Display the Index Information ]

sp_helpindex my_BaseTable

index_name	index_description	index_keys
PK__my_BaseT__3214EC2766979D65	clustered, unique, primary key located on PRIMARY	ID

Here the UNIQUE CLUSTERED index is created.

Step-4 [ Optional -- Display the Records of the Base Table ]

ID    EMPNAME                 GRADE

1     Mr. Sudip Das           A

2     Mr. Joydeep Das         B

3     Mr. Sukamal Jana        B

4     Mr. Tuhin Kr. Shinah    C

5     Mr. Sangram Jit         C

6     Mr. Sourav Sarkar       D

Step-5 [ Create the Materialize View ]

IF OBJECT_ID('View_my_BaseTable') IS NOT NULL

   BEGIN

        DROP VIEW View_my_BaseTable

   END

  

GO

CREATE VIEW View_my_BaseTable

WITH SCHEMABINDING

AS

SELECT ID, EMPNAME, GRADE

FROM   dbo.my_BaseTable;

Step-6 [ Create the Clustered index on the View ]

CREATE UNIQUE CLUSTERED INDEX IX_View_my_BaseTable

ON View_my_BaseTable (ID);

Step-7 [ Display the Index Information ]

sp_helpindex View_my_BaseTable

index_name	index_description	index_keys
IX_View_my_BaseTable	clustered, unique located on PRIMARY	ID

Here the UNIQUE CLUSTERED index is created.

Step-7 [ Display the Actual Execution Plan ]

In this execution plan if we see it carefully we find that the Index named "PK__my_BaseT__3214EC2766979D65" is used which is the Base Table Index (The Index of the Primary Key). The Index that is created on the View is not used.

"And this is the Problem. Why the Index of the View is not used by the MS SQL Query optimizer? "

Finding the rood of the problem

The cost of using the indexed view may exceed the cost of getting the data from the base tables, or the query is so simple that a query against the base tables is fast and easy to find. This often happens when the indexed view is defined on small tables.

How we fix it

We can use the NOEXPAND hint if we want to force the query processor to use the indexed view.

SELECT * FROM View_my_BaseTable(NOEXPAND)

Related Tropics

1.    Materialize view and the performance issue

http://sqlknowledgebank.blogspot.in/2012/03/materialize-view-and-performance-issue.html

Hope you like it.

Posted by: MR. JOYDEEP DAS