Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Computed Column for AGE fails because it is non-deterministic

Posted on 2009-02-23
5
Medium Priority
?
1,425 Views
Last Modified: 2012-06-22
Hi,

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.
0
Comment
Question by:gd1982
  • 3
5 Comments
 
LVL 1

Expert Comment

by:TrueGeekSGF
ID: 23712406
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.
0
 
LVL 23

Expert Comment

by:adathelad
ID: 23712475
Hi,

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

Per:
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=330066
0
 

Author Comment

by:gd1982
ID: 23712535
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?
0
 
LVL 23

Expert Comment

by:adathelad
ID: 23712546
I think you can do it if it's not a *persisted* computed column - give that a try
0
 
LVL 23

Accepted Solution

by:
adathelad earned 1000 total points
ID: 23712577
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:
http://www.blackwasp.co.uk/SQLComputedColumns.aspx
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Among the most obnoxious of Exchange errors is error 1216 – Attached Database Mismatch error of the Jet Database Engine. When faced with this error, users may have to suffer from mailbox inaccessibility and in worst situations, permanent data loss.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

572 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