In SQL Server when you compare strings sometimes 'A' equals 'a' and sometimes 'A' does not equal 'a'. This depends on collation setting. Collation decides how strings are compared; you can have many different collations but the most important factor is if collation you have is case sensitive or case insensitive. If one of the strings or fields is case sensitive than 'A' is not the same as 'a' but if both are case insensitive then 'A' equal 'a'.
Below are several examples explaining case sensitivity strings comparisons and results.
select CASE WHEN 'a'='a' THEN 'match' else 'doesn''t match' end
The result of the above sql is always 'match'
select CASE WHEN 'A'='a' THEN 'match' else 'doesn''t match' end
The result of the above sql is 'match' if database collation set to case insensitive and 'doesn't match if collation is case sensitive.
select CASE WHEN 'A' collate sql_latin1_General_CP1_cs_as ='a' THEN 'match' else 'doesn''t match' end
If this case we force case sensitive collation using collate command and the results of the above query is always 'doesn't match'
select CASE WHEN lower('A') collate sql_latin1_General_CP1_cs_as = lower('a') THEN 'match' else 'doesn''t match' end
To compare two strings and ensure that are case insensitive I often use lower function on both sides. In the example above I enforced case sensitivity using collate command but I used lower on both sides hence made is case insensitive so sql result is 'match'
You could use collate instead of lower function but I personally prefer lower function as it is easier to read.
NOTE: If you use different collation you may suffer performance issues as query is not able to use index seek (based on my current knowledge).
Hope that helps