MS SQL Server :: What Is Deterministic?

Determine That This Is Deterministic

May 3, 2008

Hi all... This is the definition on the M/S site:

"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."

Good... straight forward, right? Ok.... try entering thses command seperately:

Create Table Readings (ReadingDate DateTime Not Null);

Create Function [dbo].[funct_SameDate](@datReadingDate Datetime)

Returns DateTime

As

Begin

return @datReadingDate;

End

Alter table Readings add

[TempColumn] as (dbo.[funct_SameDate](ReadingDate)) PERSISTED NOT NULL;

Error on last command returns:

Computed column 'TempColumn' in table 'Readings' cannot be persisted because the column is non-deterministic.

Can someone please explain this to me? The same value is always being returned.

This does work:

Create Table Readings (ReadingDate DateTime Not Null);

Alter table Readings add [TempColumn] as (ReadingDate) PERSISTED NOT NULL;

I obviously want to do more things inside the function, but I can't get by the first step.

Any suggestions?

Thanks!

Forch

View 6 Replies View Related

Non Deterministic Function?

Oct 9, 2006

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

View 6 Replies View Related

Deterministic - Column Property

Sep 25, 2007

Shows whether the data type of the selected column can be determined with certainty. (Applies only to Microsoft SQL Server 2000 or later.)
This is what Microsoft documentation says for this column property. How I can use his feature for database application development? What is the practical use of this property?

SQL Server 2005.

Thank you,
Smith

View 1 Replies View Related

Non-deterministic System Function Suser_sname

Dec 8, 2007

Hi,
I am using Sql Server 2005 as the database management and Access 2003 as the front-end. In the database, I intend to give different views of tables to different users. That's why I used suser_sname system function, which returns the windows login id and authenticates users to see different records in the same view. What I want to do in Access is, allowing some specific users to be able to do update, insert and delete operations through a form based on this view (which only depends on 1 table). However, Access tells me that "the recordset is not updateable". In order to be able to change records, I tried to create new index for the view in Sql Server, which failed giving "Sql Server, Error number:1949" and telling me that it fails since suser_sname yields non-deterministic results. The strange thing is that when I open VB Editor in Access and write a simple update code within this form, it updates both the view and the table in the database. My question is: How can I do update, delete and insert operations on the form directly? Is there a way to do the authentication without using a nondeterministic function in Sql Server or using the front-end Access 2003? Maybe a function similar to the current_user function in Access can do that, I don't know.

It's been a long question but I desperately need the answer. Any thanks will be appreciated.

View 6 Replies View Related

Problems With Non Deterministic Errors On Calculated Fields

May 22, 2008

Hi

I am new to SQL Server and am migrating another database

In my original database I have a default(constant) type field and a calculated field both of which call the same user defined function: GetMyUID()

My Function GetMyUID() returns the current date, time and users initials, i.e. "20080522T09:31:15.250LSG"

When a record is first created both fields have identical values

As the record is updated over time my constant field stays constant and my calculated field reflects the time the record was last updated and the initials of that person. So my first field is called 'Created' and my second is called 'Updated'

I would have thought that something like this would be a pretty bog standard and very straightforward requirement in any database

However in SQL I am getting error messages about the return value being non deterministic

I searched the web and found advice that to sort the problem I need to use WITH SCHEMABINDING in my function definition

Unfortunately I am still getting the same 'non deterministic' error

I wonder if (in the quest to not have an overlong field) by looking up the persons initials from a 'STAFF' file rather than leaving the username in full tacked on to the end that this is causing the problem?

I can't imagine that what I am trying to achieve is rocket science but unfortunately have not been able to find any resource on the web that solves this issue for me

In desperation I turn to you

Please help (preferably by letting me have a few lines of code that return the current date/time followed by the username lookup of a Username's initials, here is a snippet of my code...

RETURN (Convert(VarChar(8),@DateTimeNow,112)+ Right(Convert(VarChar(30),@DateTimeNow,126),13)+dbo.myInitials())

Where the dbo.myInitials() calls:

RETURN (SELECT STAFF.Code from dbo.STAFF where STAFF.Login = dbo.myLogin())

and dbo.myLogin() calls

return UPPER(Right(System_User,PATINDEX('%\%',System_User)))

View 7 Replies View Related

Non-deterministic System Function Suser_sname-I Think Here Is The Right Place For My Question

Dec 8, 2007

Hi,

I am using Sql Server 2005 as the database management and Access 2003 as the front-end. In the database, I intend to give different views of tables to different users. That's why I used suser_sname system function, which returns the windows login id and authenticates users to see different records in the same view. What I want to do in Access is, allowing some specific users to be able to do update, insert and delete operations through a form based on this view (which only depends on 1 table). However, Access tells me that "the recordset is not updateable". In order to be able to change records, I tried to create new index for the view in Sql Server, which failed giving "Sql Server, Error number:1949" and telling me that it fails since suser_sname yields non-deterministic results. The strange thing is that when I open VB Editor in Access and write a simple update code within this form, it updates both the view and the table in the database. My question is: How can I do update, delete and insert operations on the form directly? Is there a way to do the authentication without using a nondeterministic function in Sql Server or using the front-end Access 2003? Maybe a function similar to the current_user function in Access can do that, I don't know.

It's been a long question but I desperately need the answer. Any thanks will be appreciated.

View 1 Replies View Related