URGENT: Why is SQL Server evaluating all "AND" expressions?

[usenet]

Why is SQL Server evaluating all AND expressions?
When I have "EXPRESSION1 AND EXPRESSION2 AND ...." if EXPRESSION1
evaluates to false, shouldn't SQL stop processing ??? (as all
programming languages do).

The following statements raises the exception "Null or empty full-text
predicate.", meaning that SQL is still evaluating the AND statement:

declare @freeTextOR varchar(200)
set @freeTextOR = ''


SELECT * FROM MyTable
WHERE (@freeTextOR <> '') AND CONTAINS((Somefield1, somefield2),
@freeTextOR)


Any ideas? Other options?
Thanks!

[usenet]

Here's a general answer:

Imagine below:

WHERE col1 BETWEEN 10 AND 20
AND col2 BETWEEN 10 AND 20

Now, say you have an index on col2 and selectivity is 10 rows. Say you have 20 million rows in the
table. Also, say you have no index on col1.

Obviously, you don't want SQL Server to first check col1 and only evaluate col2 if col1 is true.
That would mean that SQL Server need to check (and for that access) every row in the table, i.e. 20
million rows. So, the optimizer will probably "single out" the rows based on the col2 predicate
using the index (only 10 rows) and for these 10 rows check the col1 predicate.

The SQL language is not procedural (first do this, then do that), as you understand from above
reasoning. So, you cannot rely on any short circuit in the SQL language. Or rather, a vendor *could*
build an implementation that optimized the queries so that short circuit applies, but that wouldn't
give acceptable performance.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"Gaspar" wrote in message news:eZRWHjrzHHA.1184@TK2MSFTNGP04.phx.gbl...
> Why is SQL Server evaluating all AND expressions?
> When I have "EXPRESSION1 AND EXPRESSION2 AND ...." if EXPRESSION1 evaluates to false, shouldn't
> SQL stop processing ??? (as all programming languages do).
>
> The following statements raises the exception "Null or empty full-text predicate.", meaning that
> SQL is still evaluating the AND statement:
>
> declare @freeTextOR varchar(200)
> set @freeTextOR = ''
>
>
> SELECT * FROM MyTable
> WHERE (@freeTextOR <> '') AND CONTAINS((Somefield1, somefield2), @freeTextOR)
>
>
> Any ideas? Other options?
> Thanks!

[usenet]

Thanks Tibor for your time.
I'll rewrite my query to avoid this problem.
Cheers,
Gaspar



Tibor Karaszi wrote:
> Here's a general answer:
>
> Imagine below:
>
> WHERE col1 BETWEEN 10 AND 20
> AND col2 BETWEEN 10 AND 20
>
> Now, say you have an index on col2 and selectivity is 10 rows. Say you
> have 20 million rows in the table. Also, say you have no index on col1.
>
> Obviously, you don't want SQL Server to first check col1 and only
> evaluate col2 if col1 is true. That would mean that SQL Server need to
> check (and for that access) every row in the table, i.e. 20 million
> rows. So, the optimizer will probably "single out" the rows based on the
> col2 predicate using the index (only 10 rows) and for these 10 rows
> check the col1 predicate.
>
> The SQL language is not procedural (first do this, then do that), as you
> understand from above reasoning. So, you cannot rely on any short
> circuit in the SQL language. Or rather, a vendor *could* build an
> implementation that optimized the queries so that short circuit applies,
> but that wouldn't give acceptable performance.
>