CODEWITHSUNDEEP
sqlsql-server
Mostafa Armandi
Mostafa Armandi

Reputation: 939

Make SQL SERVER evaluate clauses in a certain order

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.

Upvotes: 4

Views: 204

Answers (5)

EzLo
EzLo

Reputation: 14189

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).

Upvotes: 4

Thom A
Thom A

Reputation: 95564

Honestly, I wouldn't apply the implicit cast to your column [name], it'll make the query non-SARGable. Instead, convert the value of your input (or pass it as a string)

SELECT [name]
FROM dbo.TBL_Names tn
WHERE [name] = CONVERT(varchar(32),2541);

If you "must", however, wrap [name] (and suffer performance degradation) then use TRY_CONVERT:

SELECT [name]
FROM dbo.TBL_Names tn
WHERE TRY_CONVERT(int,[name]) = 2541;

Upvotes: 0

Suraj Kumar
Suraj Kumar

Reputation: 5643

You can try this

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

You need to just [name] = 2541 to [name] = '2541'. You are missing ' (single quote) with name in where condition.

You can find the live demo Here.

Upvotes: 0

forpas
forpas

Reputation: 164089

What about:

SELECT *
FROM dbo.tblNames AS tn
WHERE [name] = convert(varchar, 2541)

Why do you need ISNUMERIC([name]) = 1) since you only care about the value '2541'?

Upvotes: 2

Chanukya
Chanukya

Reputation: 5893

Try like this 

  SELECT  [name]
    FROM    #TBL_Names AS tn
    WHERE   [name] IN ( SELECT  [name]
                        FROM    #TBL_Names
                        WHERE   ISNUMERIC([name]) = 1 )
            AND  [name] = '2541'

2)

AND  [name] =  convert(varchar,2541 )

Since You are storing name as varchar(32) varchar will accept integer datatype values also called precedence value

Upvotes: 1

Related Questions