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:

1. Will the query analyzer optimize aUserDefinedScalarFunction(4) As ScalarValue or does it get evaluated for each record?

2. 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).

3. Does the result of SELECT [value] FROM STRING_SPLIT(@X,',') get optimized or is it evaluated for each comparison?

4. Does the result of SELECT Value FROM dbo.MySplitterFunction(@X) get optimized or is it evaluated during each comparison?