Filter records using CAST() and sub query in SQL Server

Question

I am stuck with a scenario where I need to cast a particular column as BIGINT and check whether the number is not greater than X, but, the column will not always have numeric values.

I tried the following approach but it is throwing an error.

    DECLARE @RowType TABLE
      (
         RowTypeID INT IDENTITY,
         RowType   VARCHAR(10)
      );

    INSERT @RowType VALUES('Numeric');
    INSERT @RowType VALUES('NonNumeric');

    DECLARE @TempTable TABLE
      (
         ID        INT IDENTITY,
         RowTypeID INT,
         Value     VARCHAR(10)
      );

    INSERT @TempTable VALUES(1, '10');
    INSERT @TempTable VALUES(1, '20');
    INSERT @TempTable VALUES(2, '$10'); -- Non Numeric value

    -- This select throws error however ever I feel the behaviour 
    -- to be odd since the innser select should return only records of type 'NUMERIC'
    SELECT *
    FROM   (SELECT T.*
            FROM   @TempTable T
                   JOIN @RowType RT
                     ON RT.RowTypeID = T.RowTypeID
            WHERE  RT.RowType = 'Numeric') A -- With this sub query I expect only records of type 'NUMERIC' to be returned to the outer select
    WHERE  CAST(A.Value AS BIGINT) > 10

    -- Alternate approach which I can not use since 
    -- there are already lot of temp tables involved in procedure 
    --SELECT T.*
    --INTO   #Temp
    --FROM   @TempTable T
    --       JOIN @RowType RT
    --         ON RT.RowTypeID = T.RowTypeID
    --WHERE  RT.RowType = 'Numeric';

    --SELECT * 
    --FROM   #Temp
    --WHERE  CAST(Value AS BIGINT) > 10

    --DROP TABLE #Temp;

Is this the default behaviour? Or am I missing something over here?

user_1570000 · Answer

Since you are on 2012+, I would suggest try_convert() or even try_cast().

try_convert(BIGINT,A.Value AS BIGINT) >  10

IF the conversion fails, a null value would be returned

user_2975396 · Answer

Is this the default behaviour?

SQLSERVER is free to rearrange expressions,if the final result stays the same...you should not rely on the behaviour you are expecting..

below is the plan for the query

here a.value is applied before the join,so the cast fails..below screen shot confirms the same

This has been described by Itzik Ben-Gan here..Logical Query Processing Part 6: The WHERE Clause

below is some code which has same issue as yours and this also fails with same issue

WITH C AS
 (
SELECT name, datatype, val
FROM dbo.Properties
 WHERE datatype IN ('TINYINT', 'SMALLINT', 'INT', 'BIGINT')
)
SELECT *
 FROM C
WHERE CAST(val AS BIGINT) > 10;

below is an explanation from Itzik

From a logical query processing perspective, such code should not fail. However, for performance reasons, the SQL Server parser unnests, or inlines, the inner query’s code in the outer query, resulting in code that is equivalent to the original query without the table expression. Consequently, the code fails with the same error.

You could use try cast/convert to overcome conversion errors..

below is one good series for more internals:
Query Optimizer Deep Dive - Part 1

Filter records using CAST() and sub query in SQL Server

2 Answers