Determinism of UDF

I'm having a problem getting SQL to recognize a UDF as deterministic so I can use it in a persisted, computed column. I'm using INFORMATION_SCHEMA.ROUTINES to check the IS_DETERMINISTIC value and it is always NO. Why isn't the following UDF deterministic?

ALTER FUNCTION [dbo].[SecondsToTimePart2] (
@.part CHAR, @.seconds INT
) RETURNS INT
AS BEGIN
DECLARE @.hours INT, @.minutes INT

SET @.hours = @.seconds / 3600
IF @.part = 'H'
RETURN @.hours

SET @.seconds = @.seconds - (@.hours * 3600)
SET @.minutes = @.seconds / 60

IF @.part = 'M'
RETURN @.minutes

SET @.seconds = @.seconds - (@.minutes * 60)
RETURN @.seconds
END

It does not use any DB objects, floating point values, sprocs, SQL functions or non-POD types. It is strictly taking the input and returning a calculated output. I even went so far as to simply it to this with no effect.

ALTER FUNCTION [dbo].[SecondsToTimePart2] (
@.seconds INT
) RETURNS INT
AS BEGIN
RETURN 0
END

I've tried mucking around with some of the ANSI options like NUMERIC_ROUNDABORT and ARITHABORT and friends but it had no effect. Anybody know how I can make the function deterministic?

Michael Taylor - 2/7/07
http://p3net.mvps.org

I have identified the issue. The function must be schema bound. I added the WITH SCHEMABINDING to the declaration and it is now deterministic. Just in case anybody else runs into this problem.

Michael Taylor - 2/7/07
http://p3net.mvps.org

|||Additionally, if you are using a CLR UDF then you can explicitly specify these properties using custom attributes on the udf