SQL Server Case Sensitivity

SQL Server is, by default, case insensitive, which means the word "SQL SERVER" or "sql server" will be same for database engine. however, it is possible to create a case-sensitive SQL Server database and even we can make specific columns as case sensitive. The way to determine if a database or database object case sensitive is to check its "COLLATION" property and look for "CI" or "CS" in the result.

CS means Case Sensitive
CI means Case Insensitive

If we need case sensitivity for a column then we need to alter table and need to change collation for  a column. Below is the example

--Alter table TableName  alter column Column Name NVARCHAR collate Latin1_General_CS_AS
ALTER TABLE ProductMaster
ALTER COLUMN ProductCode NVARCHAR collate Latin1_General_CS_AS

If we need case insensitivity then  we can also cast the collation to case insensitive by using explicit collate for the column used in the join to get the correct data.

SELECT *
FROM ProductMaster P1
INNER JOIN ProductDetail P2 ON P1.ProductCode collate Latin1_General_CI_AS = P2.ProductCode collate Latin1_General_CI_AS
	OR

SELECT *
FROM ProductMaster P1
INNER JOIN ProductDetail P2 ON P1.ProductCode = P2.ProductCode collate Latin1_General_CI_AS

We can also check if both the colunms used in the join having same collation or not. Below is the query for getting the collation name:

SELECT TABLE_NAME
	,COLUMN_NAME
	,COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME IN (
		'ProductMaster'
		,'ProductDetail'
		)
	AND COLUMN_NAME IN (
		'ProductCode'
		,'ProductCode'
		);