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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy