Skip to content
Advertisement

mssql execution order guarantee when conversion in where clause

I have following scalar function

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.

Advertisement

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.

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.

And lose the WITH (NOLOCK)

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement