to coalesce or not

[timc]

I have inherited a db with slowness claims. Last week at a MS seminar where independent SQL Consultant gave presentation on performance gotchas. One of his top 5, do not use coalesce on joins and where clause. Of course it is all over this db. Looking at below was this a bad approach?



WHERE coalesce(PM.ALTKEYDOC,'x') = coalesce(@AK,PM.ALTKEYDOC,'x')
AND coalesce(PM.FIRSTNAME,'x') LIKE coalesce('%' + @FN + '%',PM.FIRSTNAME,'x')
AND coalesce(PM.LASTNAME,'x') LIKE coalesce('%' + @LN + '%',PM.LASTNAME,'x')
AND coalesce(PM.SEX,'x') = coalesce(@SX,PM.SEX,'x')
AND coalesce(PM.BIRTHDATE,'1/1/1900') = coalesce(@BD,PM.BIRTHDATE,'1/1/1900')
AND coalesce(PM.DIVISION,'x') = coalesce(@DI,PM.DIVISION,'x')
AND PM.STATE = @STATE
ORDER BY LASTNAME

[skhanal]

Using any function not only COALESCE in WHERE clause makes index available on that column unusable by the optimizer.

[timc]

that's what I thought