Non deterministic function?

Gentlemen What is "Non_Deterministic" about the function below?

I pass DATETIME Column and a DECIMAL column ti the function. It keeps yelling at me saying it is a non-deterministic function.

I am using this function to PERSIST a Computed Column.

I have tried converting all NVARCHARs to VARCHARs.

Tredi returning a VARCHAR instead of a DATETIME, but still did not succeed.

Am I doing something wrong, I must be.....

CREATE FUNCTION [dbo].[udf_GetDateTime](@.Date DATETIME, @.TimeDecimal DECIMAL)

RETURNS DATETIME

AS

BEGIN

DECLARE @.DateStr NVARCHAR(23)

DECLARE @.TimeStr NVARCHAR(12)

DECLARE @.DateTimeResult DATETIME

SET @.TimeStr = RIGHT('000000' + CONVERT(NVARCHAR(6), @.TimeDecimal), 6)

SET @.DateStr = CONVERT(NVARCHAR(10), @.Date, 120) + ' ' +

SUBSTRING(@.TimeStr, 1, 2) + ':' +

SUBSTRING(@.TimeStr, 3, 2) + ':' +

SUBSTRING(@.TimeStr, 5, 2)

RETURN CONVERT(DATETIME, @.DateStr, 120)

END

I've tried on my sql servers, 2000 and 2005, and worked correctly on both of them.
The code I used to test your udf (with Management studio) is the following:

declare @.d datetime
set @.d = getdate()
print dbo.[udf_GetDateTime] (@.d, 1)|||

Could you please explain Carlop -- what you are trying to convey.

So you think the above function is Deterministic Or Non-Deterministic.

I know it works, but when I use the function to create a Computed Column (persisted), it yells saying I cannot do that -- since the function is non-deterministic.

|||

It is not deterministic because the UDF is not created with the SCHEMABINDING option. Without this option, the database engine has no way of knowing if the definition of the module has changed after it was created and so on. You can check deterministic property of modules by using OBJECTPROPERTY(OBJECT_ID('<your_module>'), 'IsDeterministic'). So create the function with SCHEMABINDING to make it deterministic.

Also, it appears that the use of scalar UDF in this case is unnecessary. You can simply use an expression to get the result you need in a computed column. Note that using scalar UDFs for simple operations has lot of overhead and hurts performance. So you will be better off using expression in computed column directly.

|||If you have to wrap the function up you are best to use a SQLCLR function. These perform better that TSQL UDFs, however as has been stated, bot perform worse than using an expression directly.|||Sorry but I didn't noticed that you used it to persist a column, so I've misunderstood the usage...