use tempdb;
go

create function dbo.fn1
(
 @x int
)
returns varchar(200)
as
begin
 return (select name from sys.objects where object_id = @x);
end;
go

create function dbo.fn2
(
 @x int
)
returns varchar(200)
as
begin
 return 'x = ' + cast(@x as varchar(10));
end;
go

create function dbo.fn3
(
 @x int
)
returns varchar(200)
with schemabinding
as
begin
 return 'x = ' + cast(@x as varchar(10));
end;
go

select objectproperty(object_id, 'IsDeterministic'), name from sys.objects where name in (N'fn1', N'fn2', N'fn3') and schema_name(schema_id)  = N'dbo';
go

select top (5) * into #t from master.dbo.spt_values;
go

set statistics xml on;

/*5 раз*/select * from #t where name = dbo.fn1(1);
/*5 раз*/select * from #t where name = dbo.fn2(1);
/*1 раз*/select * from #t where name = dbo.fn3(1);
/*1 раз*/select * from #t where name = (select dbo.fn1(1));
/*1 раз*/select * from #t where name = (select dbo.fn2(1));

declare @x int = 1;

/*5 раз*/select * from #t where name = dbo.fn1(@x);
/*5 раз*/select * from #t where name = dbo.fn2(@x);
/*1 раз*/select * from #t where name = dbo.fn3(@x);
/*5 раз*/select * from #t where name = (select dbo.fn1(@x));
/*5 раз*/select * from #t where name = (select dbo.fn2(@x));
/*1 раз*/select * from #t where name = (select dbo.fn1(@x)) option (recompile);
/*1 раз*/select * from #t where name = (select dbo.fn2(@x)) option (recompile);

set statistics xml off;
go

drop table #t;
drop function dbo.fn1, dbo.fn2, dbo.fn3;
go