COLLATE keyword in sql with an example

Know about COLLATE:

- Normally we have 4 type of sensitivity on SQL Server (Case, Width, Accent, kanatype)
- If you want to force any one of the sensitivity as goven above then you have to use COLLATE.

Here, I would like to explain about "Case Sensitive"...

One small question for you..

How will you validate the data based on the Case sensitive on the column / expression ?

i.e: "A" is differs from "a" correct ?

If so, How will you do that ?

Here, you have one small workarround...

DECLARE @A VARCHAR(10)

SELECT @A = 'a'



IF (@A = 'A')

PRINT 'Match'

ELSE

PRINT 'No Match'

Normally the result should be No Match correct ?

But, you will get the result as Match . Because, The sql server engine conpares the expression as Case-Insensitive

So, How will you force to validate Case sensitive ?

DECLARE @A VARCHAR(10)

SELECT @A = 'a'



IF (@A = 'A' COLLATE SQL_Latin1_General_CP1_CS_AS)

PRINT 'Match'

ELSE

PRINT 'No Match'

Now, you will get the result as No Match

Here, I have validated the expression with Case sensitive collation (SQL_Latin1_General_CP1_CS_AS)