mssql execution order guarantee when conversion in where clause

I have following scalar function

CREATE FUNCTION dbo.getOM
    (   @mskey INT,
        @category VARCHAR(2)
    )
RETURNS VARCHAR(11)
AS
BEGIN
    DECLARE @om VARCHAR(11)
    SELECT @om = o.aValue
    FROM dbo.idmv_value_basic o WITH (NOLOCK)
    WHERE o.MSKEY = @mskey and o.AttrName = 'OM'
        AND EXISTS (
            SELECT NULL
            FROM sys.sequences s WITH (NOLOCK)
            WHERE CONVERT(INT, replace(o.aValue, '1690', '')) BETWEEN s.minimum_value AND s.maximum_value AND s.name = concat('om_', @category)
        )   
    RETURN @om  
END

Problem with that is, that o.aValue could not only have numeric values, so that the convertion can fail, if it is executet on other rows of idmv_value_basic, where attrName is not ‘OM’.

For some unknown reason this morning, our MSSQL-Server changed the execution order of the where conditions and the convertion failed.

How could I define the selection, so that is guaranteed, that only the the selected lines of idmv_value_basic are used for the selection on sys.sequences?

I know, for SQL the execution order is not deterministic, but there must be a way, to guarantee, that the conversion would not fail.

Any ideas, how I could change the function or am I doing something fundamentally wrong?

By the way, when I execute the selection manualy, it does not fail, but when I execute the funtion it fails.

We could repair the function while changing something, save and then change it back and save again.

Answer

I’ll try answer the question: “Any way to guarantee execution-order?”

You can – to some extent. When you write an EXISTS, what sqlserver will actually do behind the scenes is a join. (check the execution plan)

Now, the way joins are evaluated depends on cardinalities. Say you’re joining tables A and B, with a predicate on both. SQL Server will prefer to start with the table producing the fewest rows.

In your case, SQL Server probably decided that a full scan of sys.sequences produces fewer rows than dbo.idmv_value_basic (WHERE o.MSKEY = @mskey and o.AttrName = ‘OM’) – maybe because the number of rows in idmv_value_basic increased recently?

You could help things by making an index on dbo.idmv_value_basic (MSKEY, AttrName) INCLUDE (aValue). I assume the predicate produces exactly one row pr. MSKey – or at least not very many – and an index would help SQL Server choose the “right way”, by giving it more accurate estimates of how many rows that part of the join produces.

CREATE INDEX IDVM_VALUE_BASIC_MSKEY_ATTRNAME_VALUE 
    ON dbo.idmv_value_basic (MSKey, AttrName) INCLUDE (aValue);

Rewriting an EXISTS as a JOIN can be done, but requires a bit of finesse. With a JOIN, you can specify which kind (Loop, Merge or Hash) and thus force SQL Server to acknowledge your knowing better and forcing the order of evaluation, ie.

SELECT ...
FROM dbo.idmv_value_basic o
INNER LOOP JOIN (SELECT name 
           FROM sys.sequences 
           WHERE (..between min and max) 
          ) AS B 
       ON (B.name= concat('om_',@category ))
WHERE o.MSKey=@mskey AND o.AttrName = 'OM'

And lose the WITH (NOLOCK)