Thanks Mike. Although I have around 12+ yrs of experience as DBA, I have only 3 yrs of experience in MS SQL. I was an Oracle DBA. So I am a newbie. I am learning every day. Especially when I hang around in the forums.:-) Lots of good suggetions and ideas.

This is a new filed for me. ie writing articles. 🙂 I hope to learn more from this discussion we are having here.

I dont remember who stated it, But even if you give a NO LOCK hint, it still has to look at the table to find out if there is a row or not. So it will have the addtional read. And Yes, you are right, if it is an Insert, I will have the same number of reads like I have for If Exists.

One more thing, Maybe I am mistaken, but when you are tyrying to do an Update, the engine will not do any exclusive locks till it finds a record that matches the criteria (Shared Lock might be what the engine does till then) and if it finds the record/records then it decided if it should escalate the lock. Am I right here? Can somebody could clarify please..