Order of execution in queries

[sanndeb]

I have a table with one column that contains blank(''), null, numeric & varchar data. So when I wanted to find values which are numeric & between a range I wrote something like

select Value from Table1 
where isnumeric(Value)=1 and Convert(numeric(10,2), Value) between 110.00 and 115.00

which works fine. But when I change the order of the 'where' it fails, i.e. the following SQL fails giving a conversion error...

select Value from Table1 
where Convert(numeric(10,2), Value) between 110.00 and 115.00 and isnumeric(Value)=1

Why? Does the order matter in where? I read somewhere in case of performance improve it doesn't matter at all.

Also just for testing when I write the same query as...

Select AA.Value from 
(select Convert(numeric(10,2), Value) as Value from Table1 where isnumeric(Value)=1) AA
where Value between 110.00 and 115.00

It again fails giving conversion error. I was in the impression that the subqueries are performed first then the outside sql executes. So why in this case it's giving error? Why the 'outside where' is not performed just on the dataset fetched by the inner query?

[ck9663]

Why? Does the order matter in where?

For the most part, the sequence of condition matters in WHERE clause and is evaluated from left to right. That's why your first query did not fail because if the value of your column is not numeric, there is no need to test the other conditions. Because your conditions are INCLUSIVE (AND) where all conditions must be satisfied in order to be TRUE, the first condition already failed, no need to test the rest. This is the same reason (in reverse) why your second query failed.

However, SQL Server optimizer may, sometime, change the sequence of the condition being tested based on the way it thinks your query will run faster.

So why in this case it's giving error? Why the 'outside where' is not performed just on the dataset fetched by the inner query?

Because your inner subquery will have to run first. Since the convert() function already failed, the entire query fails.

Hope that clear things up a bit.

Happy Coding!!!


~~ CK

[sanndeb]

@ck9663
Thanks for the clarrification mate.... The first two are clear now.

But for the last one I need a little bit more clearence

Select AA.Value from 
(select Convert(numeric(10,2), Value) as Value from Table1 where isnumeric(Value)=1) AA
where Value between 110.00 and 115.00
 

The bold subquery runs fine individually, i.e. 'AA' has all the rows where there's no varchar value at all, then I'm putting a where around that numeric column, still it fails. This one is not clear till now :(

Thanks for you help.

[ck9663]

Although SQL Server mostly access the table based on certain factor, ie. if there's index it will mostly use it, sometimes, the optimizer will not use the index and just access it sequentially. So if there's a record that will fail when it was access one way and not the other, it will.

Hope that helps...

Good Luck!!!


~~ CK

[sanndeb]

Thanks for the clarrification mate....