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

Creating Index within Stored Procedure

Introduction

One of my friends asking a question is it better to create Index within Stored Procedure depends on the columns used in stored procedure to improve the Join performance and Drop the stored procedure after getting the desired output and just before the ending of Stored Procedure.

Technical Answer

Yes we can create Index within the Stored Procedure

Now we have to think what we answer it

Technically it is possible what my friend is mentioned in the scenario. But performance wise it’s again a problematic solution. That’s means to solve something we again create another problem. To understand it properly let’s take an example of such kind of Stored Procedure.

IF OBJECT_ID(N'[dbo].[sproc_StudentDetails]', N'P')IS NOT NULL

   DROP PROCEDURE [dbo].[sproc_StudentDetails];

GO

CREATE PROCEDURE [dbo].[sproc_StudentDetails]

     (

         @p_StdClass   INT

      )

AS

BEGIN

      -- Creating Non Clustered Index on IDNO

      CREATE NONCLUSTERED INDEX IX_NC_IDNO_StdDtl

              ON tbl_StudentDetails(IDNO);

       CREATE NONCLUSTERED INDEX IX_NC_IDNO_StdMarks

              ON tbl_StudentMarks(IDNO);

      -- Making JOIN on IDNO for Performance Reason

       SELECT a.Roll, a.StdName, b.Marks1, b.Marks2

       FROM   tbl_StudentDetails AS a

              INNER JOIN tbl_StudentMarks AS b ON a.IDNO = b.IDNO;

      -- Droping the Index

       DROP INDEX IX_NC_IDNO_StdDtl ON tbl_StudentDetails;

       DROP INDEX IX_NC_IDNO_StdMarks ON tbl_StudentMarks;

END

GO

Here in this example if we look carefully the No clustered Index is created and after successful joining it again drops.

It is technically perfect.

So what the Problem is

SELECT a.Roll, a.StdName, b.Marks1, b.Marks2

       FROM   tbl_StudentDetails AS a

              INNER JOIN tbl_StudentMarks AS b ON a.IDNO = b.IDNO;

The performance of the JOIN condition is increased due to non clustered index. So we think that we got it.

NO it is NOT.  We have to understand something before implemented it.

When the Index Created the Index table and the Statistical table both are updated, so making index within stored procedure again takes a long time to create index.

By the index creation we solve the Join performance but Index creation time is again a pain faction and slowdown the performance of Stored procedure.

Hope you like it.

Posted by: JOYDEEP DAS

PARSE() Conversion Function

Introduction 

We all know that to convert data type (form one data type to another), we must use the CONVERT or CAST data conversion function. Microsoft SQL Server 2012 gives us some new data type conversion function that we have to understand.  

Here in this article we are trying to discuss about PARSE()  data conversion function came from Microsoft SQL Sever 2012 onwards. Hope all of you find it informative.

PARSE()

PARSE() function convert any string values to Numeric or Date/Time format. If PARSE() cannot convert, it will returns a zero. PARSE() will result to an error. PARSE() function relies on Common Language Runtime (CLR) to convert the string value. If there is no CLR installed on the server, PARSE() function will return an error. 

Syntax: 

PARSE ( String_Value AS data_type   [ USING culture ] )

Parameter Details:

String_Value	String expression which needs to be parsed.
data_type	Output data type, e.g. INT, NUMERIC, DATETIME etc.
Culture	Optional string that identifies the culture in which String_Value is              formatted. If it is not specified, then it takes the language of the current session

Example

-- PARSE String to INT

SELECT PARSE('1000' AS INT) AS 'String to INT'

GO

String to INT

-------------

1000

-- PARSE String to Numeric

SELECT PARSE('1000.06' AS NUMERIC(8,2)) AS 'String to Numeric'

GO

String to Numeric

---------------------------------------

1000.06

-- PARSE String to DateTime

SELECT PARSE('05-18-2013' as DATETIME) AS 'String to DATETIME'

GO

String to DATETIME

-----------------------

2013-05-18 00:00:00.000

-- PARSE String to DateTime

SELECT PARSE('2013/05/18' as DATETIME) AS 'String to DATETIME'

GO

String to DATETIME

-----------------------

2013-05-18 00:00:00.000

-- PARSE string value in the India date format to DateTime

SELECT PARSE('18-05-2013' as DATETIME using 'en-in') AS 'Indian DateTime Format'

GO

Indian DateTime Format

-----------------------

2013-05-18 00:00:00.000

-- PARSE string value is in the US currency format to Money

SELECT PARSE('$2500' as MONEY using 'en-US') AS 'US Currency Format to MONEY'

GO

US Currency Format to MONEY

---------------------------

2500.00

-- Best Conversion by PARSE

SELECT PARSE('08-JUNE-2013' AS DATETIME)

GO

SELECT PARSE('08-JUN-2013' AS DATETIME)

GO

SELECT PARSE('2013JUNE08' AS DATETIME)

GO

SELECT PARSE('08/JUN/2013' AS DATETIME)

GO

Output is

-----------------------

2013-06-08 00:00:00.000

Difference Between PARSE() and CONVERT()

Here we are providing an example where PARSE() is converting the Value to Date/Time but CONVERT() fails to do.

SELECT PARSE('Saturday, 08 June 2013' AS DATETIME) AS 'PARSE Result'

GO

PARSE Result

-----------------------

2013-06-08 00:00:00.000

SELECT CONVERT(DATETIME, 'Saturday, 08 June 2013') AS 'CONVERT Result'

GO

CONVERT Result

-----------------------

Msg 241, Level 16, State 1, Line 1

Conversion failed when converting date and/or time from character string.

In next several article we are trying to discuss about others datra Conversion Function of Microsoft SQL Server 2012.

Hope you like it.

Posted by: JOYDEEP DAS

Finding Dependency In Stored Procedure

Introduction

Here in this article we are trying to discuss about the finding reference object within stored procedure and also finding the calling procedure references. Hope you like it and it will be informative.

What we Want

Developers are writing several stored procedure almost every day. Sometimes developers need to know about the information such as what object is used within the stored procedure or from where (SP) the specified stored procedure call. This is the vital information for the developer before working on a particular stored procedure.

Here we are representing a pictorial diagram to understand the nature of implementation.

Now we have to answer some question

1.    What are the DB Object used in Stored Procedure1 and there type.

2.    In case of Store Procedure3 which procedure calls the Store Procedure3

So we are not going to read the Stored Procedure to find the answer. Suppose the each procedure have more than 3000 line.

How we solve the answer

To solve the answer first we take the example and create an example scenario to understand it.

-- Base Table

CREATE TABLE T1

          (EMPID  INT, EMPNAME  VARCHAR(50));

GO

 CREATE TABLE T2

          (EMPID  INT, EMPNAME  VARCHAR(50));

GO

--1

CREATE PROCEDURE [dbo].[Procedure1]

AS

BEGIN

    SELECT * FROM T1;

      SELECT * FROM T2;

      EXEC [dbo].[Procedure3];

END

GO

--2

CREATE PROCEDURE [dbo].[Procedure2]

AS

BEGIN

    EXEC [dbo].[Procedure3];

END

GO

--3

CREATE PROCEDURE [dbo].[Procedure3]

AS

BEGIN

    SELECT * FROM T1;

END

GO

Now we are going to solve the question

What are the DB Object used in Stored Procedure1 and there type.

sp_depends Procedure1

In case of Store Procedure3 which procedure calls the Store Procedure3

SELECT OBJECT_NAME(id) AS [Calling SP]

FROM   syscomments

WHERE  [text] LIKE '%Procedure3%'

GROUP BY OBJECT_NAME(id);

Hope you like it.

Posted by: MR. JOYDEEP DAS