December 12, 2007 at 8:18 am
Nice link! I'm adding your feed to my Database Weekly list!
December 13, 2007 at 7:12 am
R.P.Rozema (12/12/2007)Hi Scot, you are right, this doesn't help much in performance, though I don't see it hurt performance either like you suggested. Looking at it now, I maybe should have removed this suggestion from the list but this one has slipped through. This construct was included in my original list more to help less experienced T-SQL programmers avoiding the situation where the optional column is null and thus "t.mandatory = t.optionalcol" could result in true for any value in the mandatory column.
The expression "t.mandatory = t.optionalcol" will not result in true for any comparison involving a NULL value. It may also benefit from indexes or statistics available for the columns.
The expression "NULLIF(t.mandatory, t.optionalcol) IS NULL" will always force a scan and cannot use any indexes. Therefore it may cause a performance hit.
Pointing out the impact of using OR in queries is a good thing, but don't recommend replacing it with something even worse.
December 13, 2007 at 8:50 am
Thank you Scott.
I've tried to undo my wrong in the list. Any better suggestions?
It's been wrong in my list for a long time already. Luckily not many people in my company followed this hint :).
December 14, 2007 at 4:41 am
I've added 2 more hints in the list:
- Check for existence(2)
- Check for non-existence
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply