Tuesday 28 July 2015

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

Tuesday 21 July 2015

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


Sunday 5 July 2015

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

Thursday 2 July 2015

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