IMO , the worst problem with scalar UDF's is that it will turn the operation into a nested loop as the engine is unable to optimize the logic within into a hash (or merge) join if deemed appropriate. When used in a join condition matters can be even ...

I believe the answer to this lies in the contradiction removal of the optimizer. Consider these two queries based on the same data SELECT PeopleID, Low, High FROM dbo.Guesses WHERE High NOT IN(SELECT Value FROM dbo.Res...