@ Kevin,
You can't leave a statement like that hanging 🙂 You need to explain WHY it is a bad idea.
Nah. Everyone should know who I am and just push the "I Believe" button!! 😎
Functions around columns in the WHERE clause carry several often horrifically bad performance implications. Among them are preventing acquisition of valid statistics, and when the optimizer is guessing BAD things can happen with your query plan. They also void the use of an index seek where it is appropriate, and the corresponding index scan (sometimes even a full table scan when combined with bad estimated row counts) carry several BAD things such as hammering the IO system, flushing useful hot pages out of the buffer pool (potentially crushing the entire SQL Server's performance - not just your database application), and the scan taking a shared read lock on the entire index thus dropping your concurrency in the toilet for that object. Add in the CPU burn from actually computing said function(s) on ALL rows, often combined with large HASH joins and your CPU takes a pounding as well.
Hopefully that is enough for those few that DON'T know who I am and push the "I Believe" button to jump through any hoops necessary to avoid functions around columns in the WHERE clause. Just in case it isn't, I will add that in my almost 25 years of consulting on SQL Server (and ONLY SQL Server) this issue is the #2 performance problem I have seen in aggregate (with mismatched datatypes being #1 and suboptimal indexing being a close #3).
Agreed. This is called a non-SARGable predicate and can occur in any predicate - WHERE or JOIN. If there's an NCI that would be available for use on the column, it's rendered unusable because the function's return value isn't stored in the index - the column's value is. The NCI could be used to save reads, but it must be a scan to read the entire leaf level before performing the function. The normally simple switching of logic can some with a huge savings in reads. Kevin already mentioned the additional CPU and flushing of the buffer pool.