In what direction SQL Server WHERE clause conditions are checked?

Question

I have this query:

SELECT RCodeID as 'ListItemID',
       RCode as 'ListItemName' 
FROM RCode    
WHERE RCode not like '%,%' 
      AND ISNUMERIC(RCode) = 1 
      AND CAST(RCode as int) >= CAST(@From as int) 
      AND CAST(RCode as int) <= CAST(@To as int)
ORDER BY ListItemName;

So in what order (Right to left/left to right), these conditions are checked by the SQL Server?

Because I am getting those rows which have a , comma too, but I want to exclude them before ISNUMERIC(Rcode) is executed.

Data in the table:

+---------+--------+
| RCodeID | RCode  |
+---------+--------+
| 1       | 342    |
+---------+--------+
| 2       | 232,23 |
+---------+--------+
| 3       | 323xs  |
+---------+--------+

Input: @From=300 and @To=400

Expected Output:

+---------+--------+
| RCodeID | RCode  |
+---------+--------+
| 1       | 342    |
+---------+--------+

UPDATE

I moved the code with RCode not like '%,%' AND ISNUMERIC(RCode) = 1 in a view. The view is showing the correct data. But when I use that view in above query, the cast error for comma-seperated values is coming. If view is filtering that data then why those rows are coming?

Answer 1

There's not such thing as a order of execution in the WHERE clause. You are thinking in the imperative and procedural paradigm like most development languages (Java, C++, VB, JavaScript).

SQL is (almost) functional and set based. If in any pratical terms there’s an order of execution it's buried inside the engine black box.

There's not even a join order. Things are done as the engines seems better and believe when I say theres a lot of performance checks, statistics, heuritics being doing by the engine.

If things are not happening like you expects there's a error in some place.

Just a guess but that comma is possible a decimal representation of your numeric data.

EDIT

I see, you are getting a error like:

Menssage 245, Level 16, State 1, Line 19 Conversion failed when converting the varchar value '232,23' to data type int.

You will got the error even if you put the part you use to get rid of the undesired rows in a subquery (again because the engine do stuff in the way and order it seems most optmized).

One way to acomplish it is to get rid of all undesirable rows and put it on another table. You can use a #temporary table if you wish and its a huge table but for the mall example I used a most dynamic table variable.

create table RCode
(
 RCodeID int not null
,RCode varchar(10) not null
)
GO

insert into dbo.RCode
(RCodeID, RCode)
values
 (1,'342')
,(2,'232,23')
,(3,'323xs')
GO

declare @from int  = 300, @to int = 400


declare @temp as table 
(
 [ListItemID] int not null
,[ListItemName] varchar(10) not null
)

insert into @temp
    SELECT RCodeID as [ListItemID],
           RCode as [ListItemName] 
    FROM RCode    
    WHERE RCode not like '%,%' 
          AND ISNUMERIC(RCode) = 1 


select [ListItemID], [ListItemName]
from @temp as x
WHERE CAST(x.[ListItemName] as int) >= CAST(@From as int) 
      AND CAST(x.[ListItemName] as int) <= CAST(@To as int)
ORDER BY x.[ListItemName];

A NON MATERIALIZED VIEW DON'T HELPS

As OP commented using a view does not work. It's equivalent as using the below subquery.

select [ListItemID], [ListItemName]
from (
    SELECT RCodeID as [ListItemID],
           RCode as [ListItemName] 
    FROM RCode    
    WHERE RCode not like '%,%' 
          AND ISNUMERIC(RCode) = 1 
) as x
WHERE CAST(x.[ListItemName] as int) >= CAST(@From as int) 
      AND CAST(x.[ListItemName] as int) <= CAST(@To as int)
ORDER BY x.[ListItemName];

OP will get the same error because a view is just an alias to a query, unless it's materialized. It's means the engine will just evaluate it to a sub-query like above and it will be evaluated to a simple query like OP first example. In other words, the engine will see it's semanticaly the very same query and will run it the very same way.

ATTENTION FOR CORRECTNESS

Your query will not show a row with the value 350,5. Is it correct? Remeber 0,5 can be head as one half depending of your language settings (yes it swaps comma and point). So be sure if getting rid of the comma and working with integers is what you wants.

Answer 2

I don't think there are any guarantees for evaluation order. The optimizer will try to find the most efficient way to execute the query using available information and each invocation of the query may change depending on what the optimizer thinks is the best plan right now.

If you MUST exclude some rows before evaluating other rows, I think you'll have to materialize the first result set (possibly in a temp table) and then run additional queries against that materialized result set.

To get around your CASTing error, you could use TRY_CAST

DROP TABLE IF EXISTS Table1
GO
CREATE TABLE Table1 (
    RcodeID INT
    ,RCode VARCHAR(6)
    );

INSERT INTO Table1 (RcodeID,RCode)
VALUES (1,'342'),(2,'232,23'),(3,'323xs');

DECLARE @FROM INT = 300
DECLARE @TO INT = 400

SELECT RCodeID AS 'ListItemID'
    ,RCode AS 'ListItemName'
FROM table1
WHERE RCode NOT LIKE '%,%'
    AND ISNUMERIC(RCode) = 1
    AND TRY_CAST(RCode as INT) >= CAST(@From AS INT)
    AND TRY_CAST(RCode as INT) <= CAST(@To AS INT)
ORDER BY ListItemName;

---

| ListItemID | ListItemName |
|------------|--------------|
| 1          | 342          |