Collate is a clause that can be applied to a database definition or a column definition to define the collation, or to a character string expression to apply a collation cast.

How to check case sensitive password in SQL Server using collate?

What problem will occur, in the case of without using collates clause statement during checking case sensitive password?

Suppose that we have a table structure like,

This table contains the values as follows:

On the basis of this table user’s can login into your panel. To do this, you have to write the following SQL query within the stored procedure to authenticate user.

-- ================================================
  
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Arun Kumar Singh>
-- Create date: <Create Date,, September 11, 2012>
-- Description:   <Description,, Check user login credential from tblLogin table>
-- =============================================
CREATE PROCEDURE sp_CheckLoginCredential 
      -- Add the parameters for the stored procedure here
      @EmailId varchar(60),
      @Pwd varchar(50)
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
 
    -- Select User Details from tblLogin Table
      select [Name] from tblLogin where [EmailId] = @EmailId and [Password] = @Pwd 
      
END
GO

 In this case, you will pass parameter (Email Id and password) into stored procedure and stored procedure will return the authentic user name.

Now execute the stored procedure with passing parameter.

exec sp_CheckLoginCredential 'xyzpqr@gmail.com' , 'ARUN@123'

Since SQL server is not case sensitive (i.e. capital letter character and small letter character both are treated as same) so when you will pass: 

exec sp_CheckLoginCredential 'xyzpqr@gmail.com' , 'arun@123'

It will return the right result while it should not. This is happened because SQL Server is not case sensitive.

In this case, you can use collate clause. After using the collate clause statement your stored procedure will be:

-- ================================================
  
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Arun Kumar Singh>
-- Create date: <Create Date,, September 11, 2012>
-- Description:   <Description,, Check user login credential from tblLogin table>
-- =============================================
ALTER PROCEDURE sp_CheckLoginCredential 
      -- Add the parameters for the stored procedure here
      @EmailId varchar(60),
      @Pwd varchar(50)
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
 
    -- Select User Details from tblLogin Table
      select [Name] from tblLogin where [EmailId] = @EmailId and [Password] = @Pwd COLLATE SQL_Latin1_General_CP1_CS_AS
      
END
GO

Now execute the stored procedure with following statement:

exec sp_CheckLoginCredential 'xyzpqr@gmail.com' , 'ARUN@123'

Output will be:

Now pass password field in small character letter:

exec sp_CheckLoginCredential 'xyzpqr@gmail.com' , 'arun@123'

Output will be:

 

I hope this blog will help you in understanding collate clause statement.