Why is my T-SQL function considered non-deterministic?

michal kreslik

Hello, I am attempting to create an autocomputed column with persisted values. The column's value is defined by this function:

CREATE FUNCTION [dbo].[GetMinsFromSStart]
(
@DayOfWeekID tinyint,
@Hour tinyint,
@Minute tinyint,
@Second tinyint
)
returns smallint
as
begin
declare @MinsFromSessionStart smallint;

set @MinsFromSessionStart = 420 + (@DayOfWeekID - 1) \* 1440 + @Hour \* 60 + @Minute;

if (@Second > 0)
	set @MinsFromSessionStart = @MinsFromSessionStart + 1;

if (@MinsFromSessionStart > 0)
	return @MinsFromSessionStart;
else if (@MinsFromSessionStart < 0)
	begin
		set @MinsFromSessionStart = 10080 + @MinsFromSessionStart;
        return @MinsFromSessionStart;
	end

return 10080;

end

Unfortunately, I am getting this error:

'Tick' table

Unable to modify table.
Computed column 'MinsFromSStart' in table 'Tmp_Tick' cannot be persisted because the column is non-deterministic.

Why is the above function considered non-deterministic? Thank you for any input, Michal Kreslik

Colin Angus Mackay

Your table has a computed column. You have not included the code for the computed column so I can't say. Some functions are non-deterministic. There is a list of non-deterministic functions in the Books-on-line. For example GETDATE() is non-deterministic because, given the same inputs, the output can be different.

Upcoming events: * Glasgow Geek Dinner (5th March) * Glasgow: Tell us what you want to see in 2007 My: Website | Blog | Photos

michal kreslik

The code for the computed column is simply using the above function: dbo.GetMinsFromSStart([DayOfWeekID], [Hour], [Minute], [Second]) I think there's nothing non-deterministic in that. Michal

Chris Meech

This link[^] might help. I'm going out on a limb here and guessing that since your function has three different return statements in it, that is making it non-deterministic. Also I'm more used to using 'end if;' to close off any if conditions, but your code has none. Are you sure it is logically what you are after?

Chris Meech I am Canadian. [heard in a local bar] I agree with you that my argument is useless. [Red Stateler] Hey, I am part of a special bread, we are called smart people [Captain See Sharp] The zen of the soapbox is hard to attain...[Jörgen Sigvardsson] I wish I could remember what it was like to only have a short term memory.[David Kentley]

michal kreslik

Hello, Chris, thanks for the link - but I have already read these guidelines before posting a question here. Also, quite naturally, I have been testing my function whether it's returning the correct results according to the intended logic: and it does. Just for info, the logic behind this function is to return the number of minutes elapsed from the start of Forex trading session. The first minute of the forex trading session starts every week on Sunday at 17:00:01 EST. The inputs are DayOfWeek index (0 for Sunday thru 6 for Saturday) and hour, minute & second values. The three different return statements are there because it makes the function work faster (which is a concern as I'm running that on an OLAP db with 255 million+ rows). The first condition is true for most input sets, so the function doesn't have to go thru all of the other logical branches. I'm certain my function complies to the Microsoft definition of a "deterministic function":

Determinism

Deterministic functions always return the same result any time they are called with a
specific set of input values and given the same state of the database. Nondeterministic
functions may return different results each time they are called with a specific set of input
values even if the database state that they access remains the same.

Also, there's no trace of "endif" keyword in the T-SQL reference for IF..ELSE: http://msdn2.microsoft.com/en-us/library/ms182717.aspx So where might be the problem? Thanks for help, Michal

michal kreslik

I have now written a simple function that returns the higher value of the two:

CREATE FUNCTION [dbo].[GetHigherValue]
(
@Value1 int,
@Value2 int
)
RETURNS int
AS
BEGIN
declare @ReturnVal int;

if (@Value1 > @Value2)
	set @ReturnVal =  @Value1;
else
	set @ReturnVal = @Value2;

return @ReturnVal;
END

There's only one return statement in the above function and it's by all means deterministic, but I'm stil getting the same "non-deterministic" error when I attempt to use it in the computed column specification. Any ideas? Thanks, Michal

michal kreslik

It seems like every UDF (user-defined funcion) is by default non-deterministic. Is there any way to change this? Thanks, Michal

michal kreslik

Ok, guys, so the correct answer is that it was not created with the "WITH SCHEMABINDING" option. Now it works ok. Michal