Question about Where clause

Question

Post reply

Question about Where clause

Mark Shvarts 1

SSCertifiable

Points: 5702
More actions
January 19, 2007 at 7:53 am

#69574

Is there a way to specify which Where clause condition should be evaluated first ?
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

I'm sure we can figure something out. What is it you need to do exactly?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

From the Search Condition topic in Books Online (for SQL Server 2000, but I don't think it's changed in 2005):

The order of precedence for the logical operators is NOT (highest), followed by AND, followed by OR. The order of evaluation at the same precedence level is from left to right. Parentheses can be used to override this order in a search condition. For more information about how the logical operators operate on truth values, see AND, OR, and NOT.

If that doesn't answer your question, please post your query and explain what you're trying to do.

John

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 3

please be more specific.

by default the inner sub query is executed first and then the outer query.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

Mark Shvarts 1 SSCertifiable Points: 5702 More actions · Answer 4

Here is my WHERE clause :

where

(isnumeric(substring(data,1,8))=1)

and (convert(int, substring(data,1,8)) between @from and @to)

It copies in a loop records from a table having just one column which is varchar(max) into another table having multiple columns with proper data types, integers, etc. First, it has to evaluate wheter first 8 characters make integer, then copy a chunk of up to 1000 records which are between @from and @to.

What I suspect is that in some cases it evaluates the 2nd condition first, and after finding some chars that are not convertible to numbers, breaks with error before even checking for the 1st one. What I want is to make sure that 1st condition is always evaluated first. Probably I can try it with CASE when - then.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Or without the loop at all :

--create a tally table... make it as big as you need

IF EXISTS (Select * from dbo.SysObjects where name = 'Numbers' and XType = 'U' and user_name(uid) = 'dbo')

DROP TABLE Numbers

GO

CREATE TABLE dbo.Numbers (PkNumber int identity(1,1) primary key clustered, dude bit null)

GO

INSERT INTO dbo.Numbers (dude)

SELECT NULL FROM (SELECT TOP 100 NULL AS A FROM master.dbo.spt_values) dt100 cross join (SELECT TOP 80 null AS A FROM master.dbo.spt_values) dt80

GO

ALTER TABLE dbo.Numbers

DROP COLUMN dude

GO

--Solution

DECLARE @X AS VARCHAR(1000)

SET @X = 'ab12345698765432b1234567'

SELECT CONVERT(int, Number) AS Number FROM (

SELECT CASE WHEN SUBSTRING(@X, (PkNumber - 1) * 8 + 1, 8) NOT LIKE '%[^0-9]%' THEN SUBSTRING(@X, (PkNumber - 1) * 8 + 1, 8) ELSE NULL END AS Number

FROM dbo.Numbers WHERE LEN(@X) / 8 > PkNumber - 1) dtn

WHERE Number IS NOT NULL

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 6

Is there an INNER JOIN involved to arrive at the data being selected. Join criteria is evaluated prior to the WHERE clause and you can sometimes move things from the WHERE to the JOIN to either get a performance benefit (reduces the amount of data joined) or control the flow as you're outlining. Be careful when doing this in an OUTER JOIN situation because it will behave differently.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning