Take the following queries:
DECLARE @X VARCHAR(200) = '1,2,3,4' SELECT *, dbo.aUserDefinedScalarFunction(4) AS ScalarValue FROM MyTable T INNER JOIN dbo.aUserDefineTableFunction(@X) A ON T.SomeID=A.SomeID WHERE (T.ID1 IS NULL OR T.ID1 IN (SELECT [value] FROM STRING_SPLIT(@X,','))) AND (T.ID2 IS NULL OR T.ID2 IN (SELECT Value FROM dbo.MySplitterFunction(@X))
I normally create indexed #tempTables for the
WHERE conditions above, I have found that to perform better on large datasets. However, I still can't find definitive answers to the following questions:
Will the query analyzer optimize aUserDefinedScalarFunction(4) As ScalarValue or does it get evaluated for each record?
Will the INNER JOIN dbo.aUserDefineTableFunction(@X) get materialized into a temp table once or will it be executed for each record? The function returns table (not a table variable).
Does the result of SELECT [value] FROM STRING_SPLIT(@X,',') get optimized or is it evaluated for each comparison?
Does the result of SELECT Value FROM dbo.MySplitterFunction(@X) get optimized or is it evaluated during each comparison?