Make SQL SERVER evaluate clauses in a certain order

Question

Take the following table as an instance:

CREATE TABLE TBL_Names(Name VARCHAR(32))

INSERT INTO TBL_Names
VALUES ('Ken'),('1965'),('Karen'),('2541')

sqlfiddle

Executing following query throws an exception:

SELECT  [name]
FROM    dbo.tblNames AS tn
WHERE   [name] IN ( SELECT  [name]
                    FROM    dbo.tblNames
                    WHERE   ISNUMERIC([name]) = 1 )
        AND [name] = 2541

Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the varchar value 'Ken' to data type int.

While the following query executes without error:

SELECT  [name]
FROM    dbo.tblNames AS tn
WHERE   ISNUMERIC([name]) = 1
        AND [name] = 2541

I know that this is because of SQL Server Query Optimizer's decision. but I am wondering if there is any way to make sql server evaluate clauses in a certain order. this way, in the first query,the first clause filters out those Names that are not numeric so that the second clause will not fail at converting to a number.

Update: As you may noticed, the above query is just an instance to exemplify the problem. I know the risks of that implicit conversion and appreciate those who tried to warn me of that. However my main question is how to change Optimizer's behavior of evaluating clauses in a certain order.

Answer 1

There is no "direct" way of telling the engine to perform operations in order. SQL isn't an imperative language where you have complete control of how to do things, you simply tell what you need and the server decides how to do it itself.

For this particular case, as long as you have [name] = 2541, you are risking a potential conversion failure since you are comparing a VARCHAR column against an INT. Even if you use a subquery/CTE there is still room for the optimizer to evaluate this expression first and try to convert all varchar values to int (thus failing).

You can evade this with workarounds:

• Correctly comparing matching data types:

  [name] = '2541'
  

• Casting [name] to INT beforehand and only whenever possible and on a different statement, do the comparison.

  DECLARE @tblNamesInt TABLE (nameInt INT)
  
  INSERT INTO @tblNamesInt (
      nameInt)
  SELECT
      [nameInt] = CONVERT(INT, [name])
  FROM    
      dbo.tblNames
  WHERE   
      TRY_CAST([name] AS INT) IS NOT NULL -- TRY_CAST better than ISNUMERIC for INT
  
  
  SELECT
      *
  FROM
      @tblNamesInt AS T
  WHERE
      T.nameInt = 2351 -- data types match
  

Even an index hint won't force the optimizer to use an index (that's why it's called a hint), so we have little control on how it gets stuff done.

---

There are a few mechanics that we know are evaluated in order and we can use to our advantage, such as the HAVING expressions will always be computed after grouping values, and the grouping always after WHERE conditions. So we can "safely" do the following grouping:

DECLARE @Table TABLE (IntsAsVarchar VARCHAR(100))

INSERT INTO @Table (IntsAsVarchar)
VALUES
    ('1'), 
    ('2'),
    ('20'),
    ('25'),
    ('30'),

    ('A') -- Not an INT!

SELECT
    CASE WHEN T.IntsAsVarchar < 15 THEN 15 ELSE 30 END,
    COUNT(*)
FROM
    @Table AS T
WHERE
    TRY_CAST(T.IntsAsVarchar AS INT) IS NOT NULL -- Will filter out non-INT values first
GROUP BY
    CASE WHEN T.IntsAsVarchar < 15 THEN 15 ELSE 30 END

But you should always avoid writing code that implies implicit conversions (like T.IntsAsVarchar < 15).