Re: getdate() in UDF

From: Erland Sommarskog (sommar_at_algonet.se)
Date: 03/25/04 

Date: Thu, 25 Mar 2004 00:01:31 +0000 (UTC)

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: How to initialice a default value for a column in a typed DataSet
    ... my approach has always been to create a DEFAULT in SQL Server and bind ... The DEFAULT can be an expression like GETDATE(). ... > save (the client app sees a "nothing" while the database has a datetime). ... >> I have a typed dataset that corresponds to a SQL Server database model. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: getdate() in UDF
    ... > As long as noofyears is just a plain computed column, ... > SQL Server must materialize the values. ... I see why a computed column with an index must be deterministic. ... Yes, getdate() changes constantly, but so can data. ...
    (microsoft.public.sqlserver.programming)
  • Re: insert date in column using FORMULA
    ... Use GETDATE() system function. ... > I have a table in an sql server 2000 database. ...
    (microsoft.public.sqlserver.server)
  • Re: getdate() in UDF
    ... Or it is classified as imprecise rather than non-deterministic? ... return from Erland) ... > Here noofyears is a computed column which tells us how long a person ... > SQL Server must materialize the values. ...
    (microsoft.public.sqlserver.programming)
  • Re: Index View
    ... I also have Ent. ... Indexed views are supported by SQL Server 2000. ... can't use this for your query. ... GETDATE()" prohibits this view from being indexed. ...
    (microsoft.public.sqlserver.programming)