Take a look at the following SQL.

SELECT firstName, lastName
FROM tUsers
WHERE LOWER(firstName) = 'john'

The above SQL is Syntactically correct. But, and this is a big but, unless you are using a case sensitive collation, you are wasting a lot of resources.

The following code will work fine when using the default (case INsensitive) collation.

SELECT firstName, lastName
FROM tUsers
WHERE LOWER(firstName) = 'JoHn' --Mixed case to prove a point

If you don’t believe me, look at the query plan for both queries while using a case insensitive collation.