Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
computed table cannot be persisted because the column is non-deterministic.

Answered computed table cannot be persisted because the column is non-deterministic.

  • Saturday, September 08, 2012 10:56 AM
     
     
    Sign In to Vote
    1

    i am creating creating a computed column in a table but i am getting the error as:

    Computed column 'date_2' in table 'computed_table' cannot be persisted because the column is non-deterministic.

    here is my script:

    create table computed_table(id int not null identity,date_1 date)
    alter table computed_table add date_2 as DATEADD(day,30,GETDATE()) persisted

     

All Replies

  • Saturday, September 08, 2012 11:12 AM
     
     Answered Has Code
    Sign In to Vote
    1

    That is correct and not possible. Consider using a normal column using your DATEADD() expression as DEFAULT. E.g.


    CREATE TABLE Sample 
(
  id INT IDENTITY NOT NULL ,
  Date_1 DATE ,
  Date_2 DATE DEFAULT ( DATEADD(day,30,GETDATE()) )
) ;

     
  • Saturday, September 08, 2012 11:13 AM
     
     
    Sign In to Vote
    0

    A computed column can only be created if it will result in the same value whenever it is run.  The value in your computed column will change whenever column is queried, as the current value of GETDATE() will be used.

    Is date_2 supposed to be 30 days on from date_1?  If so try using:

    alter table computed_table add date_2 as DATEADD(day,30,date_2) persisted

    A (messy) alternative is to create a new column which is always set to GETDATE() and reference this instead.  The first line creates a new column which will be set to the current date when inserted into and the second adds the 30 days on.

    alter table computed_table add currdate datetime default GETDATE()

    alter table computed_table add date_2 as DATEADD(day,30,currdate) persisted

    If you really want the column to always reference 30 days on from right now, it is non-deterministic and you can't persist the column.

    Hope this helps

    James

     
  • Saturday, September 08, 2012 1:15 PM
     
     Answered Has Code
    Sign In to Vote
    0

    create table computed_table(id int not null identity,date_1 date)
    alter table computed_table add date_2 as DATEADD(day,30,GETDATE()) persisted

    Why do you need to persist this computed column?  What is its purpose?  The value isn't derived from other columns in the row so it isn't correlated with the data in any way.  If this value were persisted, all rows in the table would need to be updated contunuously to reflect the constantly changing value.

    If your intention is to persist the computed value to reflect the time of insertition, consider a default constraint on a normal column.  Extending James's example:

    ALTER TABLE dbo.computed_table add date_2 datetime NOT NULL
	CONSTRAINT DF_computed_table_date_2 DEFAULT (DATEADD(day,30,GETDATE()));

    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

     
  • Monday, September 10, 2012 6:27 AM
     
     
    Sign In to Vote
    0
    thanks

    deepak kumar

     
  • Monday, September 10, 2012 6:27 AM
     
     
    Sign In to Vote
    0

    thanks

    deepak kumar

     
  • Monday, September 10, 2012 7:29 AM
     
     
    Sign In to Vote
    0

    i have a table in which there are 9 columns as

    premium_id, customer_id, plan_id, premium_payment_date, due_date, premium_amount ,IsActive, surrender_date, fine.

    there will be so many customers in this table.I want my due_date column automatically calculate date at the time of premium payment.

    In the premium_payment_date column 1st entry for each customer is same as policy purchase date,hence the due date will be next 30 days from the policy purchase date.

    please me out.

    deepak kumar

     
  • Sunday, September 16, 2012 9:48 AM
     
     
    Sign In to Vote
    0

    In order to make other communities searching for the thread more conveniently, could you please post a new thread for the second question? It is also good for others help you.