Re: getdate() in UDF

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Delbert Glass (delbert_at_noincoming.com)
Date: 03/25/04 

Date: Wed, 24 Mar 2004 18:42:43 -0600

Having read my last previous post to the thread,
one can say:

No need to use a non-deterministic system function
to get the message:
Server: Msg 1933, Level 16, State 1, Line 1
Cannot create index because the key column 'DaysBehind' is non-deterministic
or imprecise.

a non-deterministic select statement will do just fine.

(
Note:
That "non-deterministic select statement"
is inside a user defined function!
)

(
Or it is classified as imprecise rather than non-deterministic?
)

--drop table Erland
--drop function GetMinStartDate

   create function GetMinStartDate()
   returns datetime
   as
   begin
     return (select min(startdate) from Erland)
   end
go

   create table Erland (
     empid int NOT NULL,
     startdate datetime NOT NULL,
     DaysBehind as datediff(day,dbo.GetMinStartDate(),startdate),
     constraint pk_Erland primary key (empid)
   )

   select * from Erland
--that is fine.

   create index DaysBehind_ix on Erland (DaysBehind)
--oops:
Server: Msg 1933, Level 16, State 1, Line 9
Cannot create index because the key column 'DaysBehind' is non-deterministic
or imprecise.

Bye,
Delbert Glass

"Erland Sommarskog" <sommar@algonet.se> wrote in message
news:Xns94B7A2B24B62Yazorman@127.0.0.1...
> Kris Kirk (kriskirk@hotmail.com) writes:
> > But the same problem would exist if getdate() was replaced by an input
> > parameter, wouldn't it? How is getdate() any more 'unknown' than an
> > input parameter which can change each time the function is called?
>
> The point is that a deterministic function will always give the same
result
> for the same input, that is data in the database and input parameters.
>
> > Which begs the question why bother insisting on something being
> > deterministic in the first place? What advantage does it give knowing
> > that the output will be the same for a specific database state and input
> > parameter(s), when these two things are unknown until runtime anyway?
>
> Because you are materializing derived data.
>
> I another posting tonight I had this table:
>
> create table Steve (empid int NOT NULL,
> startdate datetime NOT NULL,
> noofyears as datediff(year, startdate, getdate()),
> constraint pk_steve primary key (empid))
>
> Here noofyears is a computed column which tells us how long a person
> has been employed in the company. So far so good. Say now that for some
> reason, we find many searches on this column, and find it important that
> they are fast, so we want to put an index on the column
>
> create index empid_ix on Steve (noofyears)
>
> But this gives us:
>
> Server: Msg 1933, Level 16, State 1, Line 1
> Cannot create index because the key column 'noofyears' is non-
> deterministic or imprecise.
>
> As long as noofyears is just a plain computed column, it does not
> take up any space. It is just a forumula. But to create an index on it,
> SQL Server must materialize the values. It is not a problem if the
> underlying data changes, because SQL Server can update the index
> in such case. But the definition of noofyears is such that even if
> nothing happens in the database, at regular points in time, SQL Server
> would need to change the index because the anniversary of a start
> date is passed.
>
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@algonet.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Relevant Pages

  • Re: getdate() in UDF
    ... It depends on what is in [Erland] and the values of the ... >Cannot create index because the key column 'DaysBehind' is non-deterministic ... >Or it is classified as imprecise rather than non-deterministic? ... >>SQL Server must materialize the values. ...
    (microsoft.public.sqlserver.programming)
  • Re: getdate() in UDF
    ... > But the same problem would exist if getdate() was replaced by an input ... that is data in the database and input parameters. ... Here noofyears is a computed column which tells us how long a person ... SQL Server must materialize the values. ...
    (microsoft.public.sqlserver.programming)
  • Re: more than 8192 characters in text file output
    ... Thanks Erland. ... results that are "export as grid" you can't choose the delimiter. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (microsoft.public.sqlserver.programming)
  • Re: stored procedure question
    ... Erland, ... regarding computing and large insert be done directly in the database. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: Syntax coloring: SERVICE
    ... If so, Erland, it is only because I have failed in my attempts to ... any version of Outlook. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (microsoft.public.sqlserver.tools)