SQL Server - The CLR stores proc in a scalar function, since the filter is not weighted correctly in the query optimizer ==> PLAN UPGRADE PLAN

Question

I have a query that looks like this:

SELECT someString  FROM
(
    SELECT someString FROM someTable
    WHERE someField = 1
) X
WHERE dbo.fnMyClrScalarFunction(X.someString) = 0

The problem is that the query optimizer moves the UDF inside the subquery, where it is applied to the rather restrictive condition "someField = 1". Unfortunately, UDF is not entirely fast, and this leads to terrible performance. Is there a way to prevent this (other than using a temporary table) or install on sql server that UDF is expensive?

Thanks in advance

Answer 1

" " someField = 1 ' "

. SQL , , . , "someField", , .

Answer 2

- :

declare @t table (SomeString varchar(100))
insert @t select someString from someTable where someField = 1
select someString from @t where  dbo.fnMyClrScalarFunction(someString) = 0

, . , :)