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.