or
sql-server sql-server-2016
Grzegorz Łyp imported from SE
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.
[![enter image description here][1]][1]

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.
[![enter image description here][2]][2]

Tested on SQL Server 2016 SP2.

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


  [1]: https://i.stack.imgur.com/QxJAJ.png
  [2]: https://i.stack.imgur.com/6QA8C.png
Top Answer
Erik Darling
> 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][1], 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][2].

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

[![NUTS][3]][3]

In plan 2, it appears outside.

[![NUTS][4]][4]

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][5]), there are a couple _early_ important differences.

No rows come out of this, which means the start up expression isn't true.
[![NUTS][6]][6]

Rows will flow out of this one, because it does.
[![NUTS][7]][7]

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?][8]

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

Hope this helps!


  [1]: https://sqlbits.com/Sessions/Event14/Query_Tuning_Mastery_Clash_of_the_Row_Goals
  [2]: https://www.brentozar.com/pastetheplan/?id=H17Exh1RG
  [3]: https://i.stack.imgur.com/Pv15m.jpg
  [4]: https://i.stack.imgur.com/xcPsS.jpg
  [5]: https://www.brentozar.com/pastetheplan/?id=rysZBSeRM
  [6]: https://i.stack.imgur.com/muafR.jpg
  [7]: https://i.stack.imgur.com/9PBwE.jpg
  [8]: https://sqlperformance.com/2012/12/t-sql-queries/left-anti-semi-join
  [9]: https://dba.stackexchange.com/questions/140381/is-there-any-benefit-to-schemabinding-a-function-beyond-halloween-protection
Answer #2
Grzegorz Łyp imported from SE
A good friend of mine Grzegorz (Stasiek) Domański found the solution that makes the query check startup predicate at first. Here is the code (similar to Erik solution).

     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 sx.*
       from (select top 1 null x) a
       cross apply(
         select s.*
           from dbo.test_fn1(@Par2,';') x
           inner join sys.objects s on s.name = x.item
       ) sx
       where @Par1 = 'CASE1'
     )
     GO
     create or alter function dbo.test_fnx(@Par1 varchar(100), @Par2 
        varchar(100))
     returns table
     as
     return (
       select sx.*
       from (select top 1 null x) a
       cross apply(
         select s.*
           from sys.objects s
           inner join dbo.test_fn1(@Par2,';') x ON s.name = x.item
       ) sx
       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)

Here are the execution plans. Marked places are the moments of startup predicate checks. And these are actual plans so no records are considered as long as startup predicate is false. And that is we expected to have.

[![Execution plan for first statement and the position of startup predicate][1]][1]


[![Execution plan for second statement and the position of startup predicate][2]][2]


  [1]: https://i.stack.imgur.com/fmKHM.png
  [2]: https://i.stack.imgur.com/kVijh.png
How to control position of startup predicate in the execution plan

This is a dedicated room for discussion about this question.

Once logged in you can direct comments to the question poster (or any answer poster) here.