How to control position of startup predicate in the execution plan For two last statements of the code below produce actual execution plan. You can see that startup predicate on @Par1 is placed in a different position that completely changes the actual number of rows that come from test_fn1 function. I need to control this behaviour.

create or alter function dbo.test_fn1(@Par1 varchar(100), @Par2 varchar(1))
returns @t table(item varchar(100))
as
begin
  insert into @t (item) select value from STRING_SPLIT(@Par1, @Par2);
  return
end
GO
create or alter function dbo.test_fn(@Par1 varchar(100), @Par2 varchar(100))
returns table
as
return (
  select s.*
    from dbo.test_fn1(@Par2,';') x
    inner join sys.objects s on s.name = x.item
    where @Par1 = 'CASE1'
)
GO
create or alter function dbo.test_fnx(@Par1 varchar(100), @Par2 
   varchar(100))
returns table
as
return (
  select s.*
    from sys.objects s
    inner join dbo.test_fn1(@Par2,';') x ON s.name = x.item
    where @Par1 = 'CASE1'
)
GO
declare @Par1 varchar(100), @Par2 varchar(100)
select @Par1 = 'CASE2', @Par2 = 'test1;test2'

select * from dbo.test_fn(@Par1, @Par2)
select * from dbo.test_fnx(@Par1, @Par2)


Below is the proper plan where startup predicate makes the query does not work on any data.


Here is the plan that show wrong behaviour of placing startup predicate. In both cases we start from the same function and only order in T-SQL is changed.


Tested on SQL Server 2016 SP2.

Is there any whitepaper or documentation on how SQL Server is placing startup predicates?
Is there any whitepaper or documentation on how SQL Server is placing startup predicates?


No, not that I've ever seen. They don't get written about all that much. The one time I blogged about them, I did a lot of head scratching.

Now, I have an answer -- it's not guaranteed to always work. It's playing some tricks on the optimizer that you can learn more about by watching Query Tuning Mastery: Clash of the Row Goals, a talk by Adam Machanic.

With that out of the way, we can control to some degree where the optimizer decides to stick predicates by using TOP.

Function rewrite 1:

In this one, the WHERE clause appears inside the CROSS APPLY.

CREATE OR ALTER FUNCTION dbo.test_erik ( @Par1 VARCHAR(100), @Par2 VARCHAR(100))
RETURNS TABLE
AS
    RETURN (   SELECT s.*
               FROM   sys.objects AS s
               CROSS APPLY
                      (   SELECT TOP (2147483647) *
                          FROM   dbo.test_fn1(@Par2, ';') AS x
                          WHERE  s.name = x.item
                          AND    @Par1 = 'CASE1' ) AS ca );
GO


Function rewrite 2:

In this one, the WHERE clause appears outside the CROSS APPLY.

CREATE OR ALTER FUNCTION dbo.test_erikx ( @Par1 VARCHAR(100), @Par2 VARCHAR(100))
RETURNS TABLE
AS
    RETURN (   SELECT s.*
               FROM   sys.objects AS s
               CROSS APPLY
                      (   SELECT TOP (2147483647) *
                          FROM   dbo.test_fn1(@Par2, ';') AS x
                          WHERE  s.name = x.item ) AS ca
               WHERE  @Par1 = 'CASE1' );
GO


You can see the resulting plans here.

In plan 1, the startup expression predicate appears inside by the TOP:



In plan 2, it appears outside.



If you want the Startup Expression Predicate to act as a constant scan, try this:

CREATE OR ALTER FUNCTION dbo.test_erik_filter ( @Par1 VARCHAR(100), @Par2 VARCHAR(100))
RETURNS TABLE
AS
    RETURN (   
               SELECT ca.*
               FROM (
                     SELECT TOP (1) 1 AS n 
                     WHERE @Par1 = 'CASE1'
                    ) AS x
               CROSS APPLY 
                (
               SELECT     s.*
               FROM       sys.objects AS s
               INNER JOIN dbo.test_fn1(@Par2, ';') AS x
                   ON s.name = x.item                  
               ) AS ca

         );
GO


If we run two different test cases, the plans will be slightly different:

DECLARE @Par1 VARCHAR(100), @Par2 VARCHAR(100);
SELECT @Par1 = 'CASE2', @Par2 = 'test1;test2';

select * from dbo.test_erik_filter(@Par1, @Par2)

GO 

DECLARE @Par1 VARCHAR(100), @Par2 VARCHAR(100);
SELECT @Par1 = 'CASE1', @Par2 = 'test1;test2';

select * from dbo.test_erik_filter(@Par1, @Par2)

GO


Looking at the live query plans (here are the regular ones), there are a couple early important differences.

No rows come out of this, which means the start up expression isn't true.


Rows will flow out of this one, because it does.


Remember that data flows from right to left in a query plan. Putting the filter further towards the left side of the plan has no advantage for doing less work.

Aaron Bertrand discusses a similar issue with regular filters here: 


Should I use NOT IN, OUTER APPLY, LEFT OUTER JOIN, EXCEPT, or NOT EXISTS?


As an aside, if your real-life code doesn't just select from system tables/views, you should consider adding the SCHEMABINDING attribute to it, which could aid in filter placement.

Hope this helps!