use tempdb;
set ansi_nulls, quoted_identifier, xact_abort on;
go

alter database scoped configuration set last_query_plan_stats = on;
go

create table dbo.t
(
 id int identity primary key,
 v int,
 rls_off__flag as case when object_id(N'tempdb..#t__stop_rls', 'U') is not null then 1 else 0 end
);
go

declare @c int = 1000000;

insert into dbo.t
 (v)
 select top (@c)
  rand(checksum(newid())) * 4096 + 1
 from
  master.dbo.spt_values a cross join
  master.dbo.spt_values b;
go

create function dbo.fnTestNumber
(
 @n int
)
returns int
as
begin
 return case when exists(select 1 from master.dbo.spt_values where number = @n and type = 'A') then 1 else 0 end
end;
go

create function dbo.fnFilter
(
 @n bit,
 @rls_off__flag bit
)
returns table
as
return (
 select
  1 as dummy
 where
  exists(select 1 from master.dbo.spt_values where @rls_off__flag = 1 or (@rls_off__flag = 0 and number = @n and type = 'A'))
);
go

create security policy dbo.t__Filtering
add filter predicate dbo.fnFilter(v, rls_off__flag) on dbo.t
with (state = off, schemabinding = off);
go

declare @c int; select /*RLS off*/ @c = count(*) from dbo.t option (maxdop 1);
go

alter security policy dbo.t__Filtering with (state = on);
go

drop table if exists #t__stop_rls;
go
declare @c int; select /*RLS on, rls_off__flag = 0, subquery*/ @c = count(*) from dbo.t option (maxdop 1);
go

create table #t__stop_rls (dummy int);
go
declare @c int; select /*RLS on, rls_off__flag = 1, subquery*/ @c = count(*) from dbo.t option (maxdop 1);
go

alter function dbo.fnFilter
(
 @n bit,
 @rls_off__flag bit
)
returns table
as
return (
 select
  1 as dummy
 where
  @rls_off__flag = 1 or (@rls_off__flag = 0 and dbo.fnTestNumber(@n) = 1)
);
go

drop table if exists #t__stop_rls;
go
declare @c int; select /*RLS on, rls_off__flag = 0, scalar function*/ @c = count(*) from dbo.t option (maxdop 1);
go

create table #t__stop_rls (dummy int);
go
declare @c int; select /*RLS on, rls_off__flag = 1, scalar function*/ @c = count(*) from dbo.t option (maxdop 1);
go

with d as
(
 select
  a.descr
 from
  (
   values
    (N'RLS off'), (N'RLS on, rls_off__flag = 0, subquery'), (N'RLS on, rls_off__flag = 1, subquery'), (N'RLS on, rls_off__flag = 0, scalar function'), (N'RLS on, rls_off__flag = 1, scalar function')
  ) a(descr)
),
stat as
(
 select
  d.descr, qs.last_worker_time, qp.query_plan, aqp.query_plan as last_actual_query_plan,
  row_number() over (partition by d.descr order by qs.creation_time desc) as rn
 from
  sys.dm_exec_query_stats qs cross apply
  sys.dm_exec_query_plan(qs.plan_handle) qp cross apply
  sys.dm_exec_sql_text(qs.sql_handle) st join
  d on st.text like N'%/*' + d.descr + N'*/%' outer apply
  sys.dm_exec_query_plan_stats(qs.plan_handle) aqp
)
select
 descr, last_worker_time, query_plan, last_actual_query_plan
from
 stat
where
 rn = 1
order by
 last_worker_time;
go

drop security policy dbo.t__Filtering;
drop function dbo.fnTestNumber, dbo.fnFilter;
drop table dbo.t;
go