Databases
StackOverflow2013
Postsdbo
POHow to make a user function deterministic
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to make a user function deterministic
    text
    copied!<p>I'm trying to achieve optimization based on deterministic behavior of a user defined function in SQL Server 2008.</p> <p>In my test code, i'm expecting no extra function calls dbo.expensive, since it's deterministic and called with same argument value.</p> <p>My concept does not work, please explain why. What could be done to achieve the expected optimization?</p> <pre><code>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 &lt; @N union all select m.num + 1 as num from multiplicity m where m.num &lt; @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 &lt; 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'); </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload