How to make a user function deterministic

Question

I'm trying to achieve optimization based on deterministic behavior of a user defined function in SQL Server 2008.

In my test code, i'm expecting no extra function calls dbo.expensive, since it's deterministic and called with same argument value.

My concept does not work, please explain why. What could be done to achieve the expected optimization?

use tempdb;
go
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[expensive]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[expensive]

go
-- used to model expensive user defined function.
-- expecting to take about 1 second to execute
create function dbo.expensive(@i int)
returns int with schemabinding
as
begin
 declare @N bigint
 declare @ret bigint
 set @N = 16; -- will generate a set of 2^N
 declare @tab table(num int);

 with multiplicity as
 (
  select 1 as num
  union all
  select m.num + 1 as num from multiplicity m
  where m.num < @N
  union all
  select m.num + 1 as num from multiplicity m
  where m.num < @N
 )
 select @ret = count(num) + @i from multiplicity;
 return @ret;
end

go

declare @tab table(x int);

with manyItems as
(
 select 1 as iterator
 union all
 select iterator + 1 from manyItems
 where iterator < 5
)
insert into @tab select 1 from manyItems;

select CURRENT_TIMESTAMP;
-- expected to take about 1 second
select dbo.expensive(1)

select CURRENT_TIMESTAMP;

-- i want to make this one execute in 1 second too
select x, dbo.expensive(x) as y from @tab;

select CURRENT_TIMESTAMP;

SELECT OBJECTPROPERTY(OBJECT_ID('dbo.expensive'), 'IsDeterministic');

Answer 1

if possible, convert your function into an inline, table-valued UDF (http://msdn.microsoft.com/en-us/library/ms189294.aspx).

e.g.

create function dbo.expensive2(@N bigint, @i int)
returns table with schemabinding
as
 return
 with multiplicity as
 (
  select 1 as num
  union all
  select m.num + 1 as num from multiplicity m
  where m.num < @N
  union all
  select m.num + 1 as num from multiplicity m
  where m.num < @N
 )
 select count(num) + @i as ret from multiplicity

and then invoke it like so:

select x, (select ret from dbo.expensive2(16, x)) as y from @tab;