Short circuiting in sql server with full text search predicates

Question

I am a novice in t-sql and sql server, and I have a question about short circuiting and full text search. Let's assume I have a statement like this:

SELECT * 
FROM t1 
LEFT JOIN t2
ON t1.Id = t2.Id
WHERE contains(t1.Field1,@word) or contains(t2.Field2,@word)

My idea here is to check if the t1.Field1 contains @word, if it is true - no need to check second conditon, if it is false - check the second contains. But I have already understood, that this doesn't works and this query performs both contains and spends time for an unnecessary work. So I want to ask how can I avoid performing of these unnecessary predicates.

Answer 1

I once had a similar question and came across an insightful article by Mladen Prajdić:

http://weblogs.sqlteam.com/mladenp/archive/2008/02/25/How-SQL-Server-short-circuits-WHERE-condition-evaluation.aspx

In short, T-SQL does not support short-circuiting of booleans in the way imperative languages such as C# do, as the order in which predicates are evaluated is not guaranteed.

The only way to achieve ordered evaluation of predicates would be to use a CASE WHEN construct, which evaluates predicates left to right and stops at the first one which is true. If the potential performance boost is justified, you could rewrite your query as:

SELECT * 
FROM t1 
LEFT JOIN t2
ON t1.Id = t2.Id
WHERE 
   Case
   When contains(t1.Field1,@word) Then 1
   When contains(t2.Field2,@word) Then 1
   Else 0
   = 1

There are certain situations where this approach may be useful. However, I would not recommend it as common practice, as it makes the code less readable and could even slow down performance.