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