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
> 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
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
