Case Sensitivity In SQL Server

Introduction

By default the Microsoft SQL Server is Case in sensitive. Some time it is good and some time it is problematic also. This article is related to it. Hope it will be informative and you enjoy it.

To understand it properly we take an Example

We have a table objects in the name of tbl_EmployeeLogin, like this

Tbl_EmployeeLogin

EmployID	LoginName	PassWord
1	Joydeep Das	joydeepdas
2	Rajesh Mishra	JOYDEEP DAS
3	Deepasree Das	jOyDeEpDaS

So this table have user name (Login Name) and Pass word. If we make a closer look on this table all the password is in the name of “JoydeepDas” but in different Case. Some have small case, some have Upper case and Some have Small and Upper case mixture.

So when we make a simple Select statement with Login name and Password, the password must check the case sensitivity before fetching data from table objects.

Step-1 [ Create the Table Objects First ]

IF OBJECT_ID(N'[dbo].[tbl_EmployeeLogin]', N'U')IS NOT NULL

   BEGIN

      DROP TABLE [dbo].[tbl_EmployeeLogin];

   END

GO

CREATE TABLE [dbo].[tbl_EmployeeLogin]

    (

       EmployID   INT                NOT NULL IDENTITY PRIMARY KEY,

       LoginName  VARCHAR(50)        NOT NULL,

       [PassWord] VARCHAR(50)        NOT NULL

     );

GO

INSERT INTO [dbo].[tbl_EmployeeLogin]

    (LoginName, [PassWord])

VALUES('Joydeep Das', 'joydeepdas'),

      ('Rajesh Mishra', 'JOYDEEPDAS'),

      ('Deepasree Das', 'jOyDeEpDaS');

GO

SELECT * FROM [dbo].[tbl_EmployeeLogin]

GO

EmployID    LoginName                                   PassWord

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

1           Joydeep Das                                         joydeepdas

2           Rajesh Mishra                                      JOYDEEPDAS

3           Deepasree Das                                      jOyDeEpDaS

(3 row(s) affected)

Step-2 [ Now make a Simple Select Statement to Retrieve Records ]

SELECT *

FROM   [dbo].[tbl_EmployeeLogin]

WHERE  LoginName = 'Joydeep Das'

       AND [PassWord] = 'JOYDEEPDAS';

Output:

EmployID    LoginName                                          PassWord

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

1           Joydeep Das                                        joydeepdas

(1 row(s) affected)

If we take the closer look in the SELECT statement we find that the WHERE clause of the statement not checking the case sensitivity in case of Password.

How We fix it

We can fix it in number of ways

Step-1 [ Using COLLATE ]

SELECT *

FROM   [dbo].[tbl_EmployeeLogin]

WHERE  LoginName = 'Joydeep Das'

       AND [PassWord] = 'JOYDEEPDAS'

           COLLATE SQL_Latin1_General_CP1_CS_AS;

Output:

EmployID    LoginName                                          PassWord

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

(0 row(s) affected)

Step-2 [ Using VERBINARY ]

SELECT *

FROM   [dbo].[tbl_EmployeeLogin]

WHERE  LoginName = 'Joydeep Das'

       AND CONVERT(VARBINARY(MAX), [PassWord]) =   

           CONVERT(VARBINARY(MAX),'JOYDEEPDAS');

Output:

EmployID    LoginName                                          PassWord

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

(0 row(s) affected)

Step-3 [ ALTERING TABLE]

ALTER TABLE [dbo].[tbl_EmployeeLogin]

ALTER COLUMN [PassWord] VARCHAR(50)

COLLATE SQL_Latin1_General_CP1_CS_AS;

GO

SELECT *

FROM   [dbo].[tbl_EmployeeLogin]

WHERE  LoginName = 'Joydeep Das'

       AND [PassWord] = 'JOYDEEPDAS';

Output:

EmployID    LoginName                                          PassWord

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

(0 row(s) affected)

Hope you like it.

Posted by: MR. JOYDEEP DAS

SSIS Bulk Insert and How to Handel Error

Introduction

The SSIS Bulk Insert task used to copy data from large Flat File to Microsoft SQL Server Table. This is generally used to handle the Large Flat file copy to Table.

We have to remember:

1.    The flat file should be PURE. That means we have to check the flat file very well before use.

2.    It can copy one flat file data to a single table only

What we needed:

1.    Flat file connection manager for retrieving data from flat file

2.    OLEDB Connection manager to save the data in destination

3.    Bulk Insert Task in SSIS control flow tab

Here is the Video demo for it…

https://youtu.be/nMRSzl_gvnI

Hope you like it

Posted by: MR. JOYDEEP DAS

Pictorial Diagram of SSIS, SSAS and SSRS in MS BI Platform

Pictorial Diagram of SSIS, SSAS and SSRS in MS BI Platform:

Hope you like it.

Posted by: MR. JOYDEEP DAS

Efficient Use of Grid Data When Saving In DB

Introduction

A data grid is a fundamental element for every developer. We have to save records from Grid to our Backend SQL Server Table object.

Here in this article we are trying to save the records from Data Grid to our Table object in efficient way. That is the entire data grid records all in together. Hope it will be interesting.

What we find in some development

Suppose we have data grid like this

Student Roll	Student Name	Student Class
1	Joydeep Das	1
2	Shipra Roy Chowdhury	1
3	Deepasree Das	1

To save the records in the Database we have a Stored procedure like this

CREATE PROCEDURE [dbo].[proc_SaveRecord]

         (

               @p_idRoll        INT,

               @p_cStdName      VARCHAR(50),

               @p_iStdClass     INT

         )

   AS

   BEGIN

      ......

       .....

   END

This type of common practice stored procedure have parameters for all the columns in the table and the developer pass the value from grid to the parameters one by one by some sort of looping and save the data in the database.

Bad practice as the frontend frequently communicates with backend and the cost is so high. This type of practice make or increasing BLOACKING and the result we find the TIME OUT in case of Web Application.

What the Suggested Method to do that

We can pass the entire data table into our stored procedure and save the records in our database table

Example of Best Practice

Step – 1 [ Create the Base Table to Save Records from Grid ]

CREATE TABLE tbl_Student

   (

      idRoll    INT         NOT NULL IDENTITY PRIMARY KEY,

      cStdName  VARCHAR(50) NOT NULL,

      iStdClass INT         NOT NULL

  );

Step – 2 [ Create the Stored Procedure to Accept XML String ]

CREATE PROCEDURE [dbo].[proc_SaveRecord]

         (

             @p_GridData   XML

         )

   AS

BEGIN

     DECLARE @Handle AS INT;

      CREATE TABLE #tmpStudent

           (

                  idRoll     INT           NOT NULL,

                  cStdName   VARCHAR(50)   NOT NULL,

                  iStdClass  INT           NOT NULL

           )

      EXEC sp_xml_preparedocument

            @Handle OUTPUT,

             @p_GridData;

     INSERT INTO #tmpStudent

     SELECT *

     FROM   OPENXML (@Handle, '/Dataset/tbl_Student', 1)

            WITH

            (

              idRoll       INT,

              cStdName     VARCHAR(50),

              iStdClass    INT

            );

     EXEC sp_xml_removedocument @Handle;

     -- Actual Save --

     INSERT INTO tbl_Student

            (cStdName, iStdClass)

     SELECT cStdName, iStdClass

     FROM   #tmpStudent;

END

Step – 3 [ Now we have to Concert Grid Data into XML String ]

<Dataset>

<tbl_Student idRoll="1" cStdName="Joydeep Das" iStdClass="1" />

<tbl_Student idRoll="2" cStdName="Shipra Roy Chowdhury" iStdClass="1" />

<tbl_Student idRoll="3" cStdName="Deepasree Das" iStdClass="1" />

</Dataset>

Step-4 [ Calling the Stored Procedure with XML ]

BEGIN

     DECLARE @p_GridData AS XML;

     SET @p_GridData =

'<Dataset>

<tbl_Student idRoll="1" cStdName="Joydeep Das" iStdClass="1" />

<tbl_Student idRoll="2" cStdName="Shipra Roy Chowdhury" iStdClass="1" />

<tbl_Student idRoll="3" cStdName="Deepasree Das" iStdClass="1" />

</Dataset>

';

      

      EXEC [dbo].[proc_SaveRecord]

           @p_GridData = @p_GridData;

END

Step-5 [ Observe the Output ]

SELECT * FROM tbl_Student;

idRoll  cStdName                              iStdClass

1          Joydeep Das                           1

2          Shipra Roy Chowdhury           1

3          Deepasree Das                       1

There are other alternate way for that. If have you nay other best way to Save grid records in a table, please share your knowledge with us.

Hope you like it.

Posted by: JOYDEEP DAS

Is UNIQUE and NOT NULL makes PRIMARY KEY

Introduction

In this article we are trying to establish a case of UNIQUE constraint with NOT NULL clause and try to understand it as a logical Primary key. Hope it will be informative.

So What is the Technical Definition of a Primary Key

Primary Key  = UNIQUE  +  NOT NULL + One Index (Default is Clustered Index).

If a table has no clustered index when we create the Primary key the Clustered Index is created automatically. But if the table has the Clustered Index in the other columns when we create the Primary key the NON CLUSTERED index is created in that column.

Try to take an Example with Clustered/Non Clustered Primary Key

IF OBJECT_ID(N'dbo.tbl_Example', N'U') IS NOT NULL

   DROP TABLE dbo.tbl_Example;

GO

CREATE TABLE dbo.tbl_Example

   (

     idProduct          INT          NOT NULL,

       ProductName      VARCHAR(50)  NOT NULL

   );

GO

-- Now we create a Clustered Index on tbl_Example.ProductName Columns

CREATE CLUSTERED INDEX IX_CLUS_ProductName ON dbo.tbl_Example(ProductName);

GO

-- Now we Create a PK on tbl_Example.idProduct

ALTER TABLE dbo.tbl_Example

ADD CONSTRAINT PK_idProduct PRIMARY KEY(idProduct);

GO

sp_helpindex tbl_Example

index_name	index_description	index_keys
IX_CLUS_ProductName	clustered located on PRIMARY	ProductName
PK_idProduct	nonclustered, unique, primary key located on PRIMARY	idProduct

If we look at the example we find that in the column ProductName we already create the Clustered index before and after that we make the primary key on    idProduct.

If we make the closer look, we find that the Primary key can Exists with Non Clustered Index.

Now Take another Example

Unique Key / Primary Key

IF OBJECT_ID(N'dbo.tbl_Example', N'U') IS NOT NULL

   DROP TABLE dbo.tbl_Example;

GO

CREATE TABLE dbo.tbl_Example

   (

     idProduct    INT          NOT NULL PRIMARY KEY,

     ProductName  VARCHAR(50)  NOT NULL UNIQUE

   );

GO

GO

sp_helpindex tbl_Example

index_name	index_description	index_keys
PK__tbl_Exam__5EEC79D1CCF2E1D6	clustered, unique, primary key located on PRIMARY	idProduct
UQ__tbl_Exam__DD5A978AC582F645	nonclustered, unique, unique key located on PRIMARY	ProductName

If we look at the example we find that a non-Clustered Index is created in the columns ProductName where we make the UNIQUE constraint.

If we look at the table definition we find that ProductName column have Unique and NOT NULL constraint.

So in this case

ProductName = UNIQUE + NOT NULL + Non Clustered Index

If the Primary key is removed form that table can we use the ProductName columns as primary key for that Table?

What is your opinion related to it.

Posted By: JOYDEEP DAS