CODEWITHSUNDEEP
sqlsql-server
jrowe88
jrowe88

Reputation: 21

SqlServer Short-Circuit Optimization in Queries

Does SQL Server use short-circuits in query logic, similar to most programming languages? I have a procedure with many possible filters that default to '%' and then are used in a aField LIKE @filter clause. If the user uses the default, I'd ideally like to short-circuit that particular filter, like (@filter = '%' OR aField LIKE @filter)

If I read other short-circuit questions and answers correctly, the query processor can choose to process in any order or both. (To preempt, for maintenance/clarity reasons, I don't want to write dynamic SQL).

Is there a way to force a short-circuit?

Upvotes: 0

Views: 69

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89091

Is there a way to force a short-circuit?

Yes. For the special case of expressions like (@filter = '%' OR aField LIKE @filter) you can enable them to be simplified before the query plan is optimized with OPTION RECOMPILE query hint.

Without OPTION RECOMPILE SQL Server will create a query plan that will be reused for any value of @filter, which requires the expression to be evaluated to filter rows.

Upvotes: 1

Related Questions