SQL Server Case Sensitive Search - COLLATION

In SQL Server, by default the search is case insensitive, which means that it will not differentiate between upper and lower case letters. So while comparing it will treat "SQL" and "sql" as same. Technically, this is called as "collation". Default collation for the SQL server is case insensitive collation. We can use the collation in the following ways

Database level

We can add the collation at database level. So all the columns will follow this collation

 
USE master;  
GO  
 
CREATE DATABASE GopiPortal_Test  
COLLATE Latin1_General_100_CS_AS_SC;  
GO 

We can alter the collation as

 
USE master;  
GO 
 
ALTER DATABASE MyOptionsTest  
COLLATE French_CI_AS ;  
GO 

Note: Collation cannot be changed after database has been created on Azure SQL Database.

Column Level

You can override the database collation for char, varchar, text, nchar, nvarchar, and ntext data by specifying a different collation for a specific column of a table and using one of the following

 
CREATE TABLE dbo.MyTable  
  (PrimaryKey   int PRIMARY KEY,  
   CharCol      varchar(10) COLLATE French_CI_AS NOT NULL  
  );  
GO  
ALTER TABLE dbo.MyTable ALTER COLUMN CharCol  
            varchar(10)COLLATE Latin1_General_CI_AS NOT NULL;  
GO  

Query Level

In Query level also we can change it temporarily

 
SELECT TOP 1 *
FROM dbo.MyTable
WHERE UserId = @UserId COLLATE SQL_Latin1_General_CP1_CS_AS
    AND Password = @Password COLLATE SQL_Latin1_General_CP1_CS_AS

Reference: https://docs.microsoft.com/en-us/sql/relational-databases/collations/collation-and-unicode-support?view=sql-server-2017

Gopikrishna