I have got a request to write a select query to get data by matching a column with case sensitive and I should not suppose to use any conversion function.
Then I came to know that there is a way to accomplish this using “COLLATE”
There are mainly two collations in SQL Server:
COLLATE Latin1_General_CS_AS – Case Sensitive
SQL_Latin1_General_CP1_CI_AS – Not Case Sensitive – Default Collation
I’ll give you an example
-- Not Case Sensitive - 'ABC' is euuals to 'abc'
DECLARE @a VARCHAR(100)
SET @a='ABC'
IF(@a COLLATESQL_Latin1_General_CP1_CI_AS='abc')
PRINT 'Strings are equal'
ELSE
PRINT 'Not equal'
-- Case Sensitive - 'ABC' is not euuals to 'abc'
DECLARE @a VARCHAR(100)
SET @a='ABC'
IF(@a COLLATELatin1_General_CS_AS ='abc')
PRINT 'Strings are equal'
ELSE
PRINT 'Not equal'
It would be more useful in cross collated environment and also as per the performance point of view using collation while comparing boost performance when compares with convert functions in where clause.
