Are boolean clauses executed always in the provided order?

Question

Are boolean clauses executed always in the provided order?

amns

SSCrazy

Points: 2628
More actions
February 3, 2016 at 8:11 am

#324475

Hi!
I have a query which needs to filter table according provided filters.
I need to filter just by the provided filters.
Example:
SELECT
Col1,
Col2,
Col3,
Col4
FROM
MyTable
WHERE
((@FilterCol1 IS NULL) OR (Col1 like @FilterCol1))
AND
((@FilterCol2 IS NULL) OR (Col2 like @FilterCol2))
AND
((@FilterCol3 IS NULL) OR (Col3 like @FilterCol3))
Is this a good option to filter the table?
Will Sql Server always execute the clauses by the provided order, checking always first if the @FilterCol1, @FilterCol2 and @FilterCol3 are NULL?
Thanks in advance!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

sql.queries (2/3/2016)
Hi!
I have a query which needs to filter table according provided filters.
I need to filter just by the provided filters.
Example:
SELECT
Col1,
Col2,
Col3,
Col4
FROM
MyTable
WHERE
((@FilterCol1 IS NULL) OR (Col1 like @FilterCol1))
AND
((@FilterCol2 IS NULL) OR (Col2 like @FilterCol2))
AND
((@FilterCol3 IS NULL) OR (Col3 like @FilterCol3))
Is this a good option to filter the table?
Will Sql Server always execute the clauses by the provided order, checking always first if the @FilterCol1, @FilterCol2 and @FilterCol3 are NULL?
Thanks in advance!

No. SQL Server will apply the clauses in whichever order results in the lowest-cost plan.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 2

Chris addressed the question in the title. I'll address the question in the body, whether this is a good idea. Usually it isn't, but sometimes it is the least of evils.

Some "light reading" on the subject can be found here: http://www.sommarskog.se/dyn-search.html

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

sql.queries (2/3/2016)
Is this a good option to filter the table?

It'll work. The performance will be erratic and horrible, but it'll work.

https://www.simple-talk.com/sql/t-sql-programming/how-to-confuse-the-sql-server-query-optimizer/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

amns SSCrazy Points: 2628 More actions · Answer 4

Thanks for all your answers!

Besides it would work, I will use dynamic sql and read the link recommended!

Regards