Computed Column for AGE fails because it is non-deterministic

Posted on 2009-02-23
Last Modified: 2012-06-22

I am trying to create two computed columns in two tables which are, more or less, performing the same function and both failing for the same reason.

In one table I am trying to create a persisted computed AGE column from DOB, in the same table.  I am using this as the formula which I took from another question off here:

(datediff(year,[dob],getdate())-case when CONVERT([char](5),[dob],(1))>CONVERT([char](5),getdate(),(1)) then (1) else (0) end)

When I go to exit SSMS and save the changes to the table an error occurs which reads "Computed column 'Age' in table 'Tmp_Respondents' cannot be persisted because the column is non-deterministic."

The other computed column in another table is to work out the number of years a person has been working by deducting "YearsofPractice" column from GETDATE.  But the same problem occurs when I try to save the table.

There is no option to alter the column to be deterministic.  I am new to MSSQL2008 and can't find anything about this is the books I am  using.  Any help would be appreciated.
Question by:gd1982
    LVL 1

    Expert Comment

    You receive a similar error when you attempt to create a user defined function containing GetDate().  You might try to plug in a static date and see if that works.  If it does, you will know the problem is with the GetDate() function.
    LVL 23

    Expert Comment


    Yes it is because you are using GETDATE() which is a non-deterministic function and therefore causes the error you are seeing.


    Author Comment

    Is it possible to get around this?  Surely people have used computed columns to determine Age from a DOB column using the GETDATE function before.  Or is there an alternative to GETDATE?
    LVL 23

    Expert Comment

    I think you can do it if it's not a *persisted* computed column - give that a try
    LVL 23

    Accepted Solution

    Here's an example, which confirms that if you choose to persist the computed column when using GETDATE(), you get the error. So, by choosing not to persist it you should be OK:

    About half way down this article:

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now