COMPUTED COLUMN needing to retain value

  • I need to restore a value in my travellog table by adding a computed column called confirmDate:

    TABLE dbo.travellog
    (
    travelID (pk,varchar(5), NOT NULL),

    userID varchar(5) NOT NULL,
    travelcode varchar(5) NULL,
    departdate datetime NULL,
    expectarrrivedate datetime NULL,
    CONSTRAINT PK_travelID PRIMARY KEY CLUSTERED (travelID ASC) 
    );

    USE mydb
    ALTER TABLE dbo.travellog
    ADD [confirmDate] AS (CASE [travelcode] WHEN 'A' THEN cast(GetDate() as Date) END)

    Even if the value in confirmDate changes after it gets inserted with the computed column, I need to keep that value in that column. I first used "Persisted" attribute with the column, but i dropped it and re-created it without "Persisted" , and still when the travelcode value changes from 'A' the confirmDate value gets erased.
    ??

    Thanks in advance

  • Create a trigger that updates the value of confirmDate

  • The value of a persisted column is calculated at select time, not at insert or update time - consider the code below and you'll see that.  That's why the persisted column expression must be deterministic, so I'm not sure how you managed to persist the column.  Use a trigger instead, as Des suggested.
    CREATE TABLE #travellog (
        travelID int NOT NULL IDENTITY PRIMARY KEY CLUSTERED
    ,    travelcode varchar(5)
    ,    confirmDate AS CASE travelcode WHEN 'A' THEN GETDATE() END
        )

    INSERT INTO #travellog
    VALUES ('A'), ('B')

    SELECT * FROM #travellog
    WAITFOR DELAY '00:00:10'
    SELECT * FROM #travellog

    John

  • Zososql - Wednesday, May 3, 2017 10:37 PM

    I need to restore a value in my travellog table by adding a computed column called confirmDate:

    TABLE dbo.travellog
    (
    travelID (pk,varchar(5), NOT NULL),

    userID varchar(5) NOT NULL,
    travelcode varchar(5) NULL,
    departdate datetime NULL,
    expectarrrivedate datetime NULL,
    CONSTRAINT PK_travelID PRIMARY KEY CLUSTERED (travelID ASC) 
    );

    USE mydb
    ALTER TABLE dbo.travellog
    ADD [confirmDate] AS (CASE [travelcode] WHEN 'A' THEN cast(GetDate() as Date) END)

    Even if the value in confirmDate changes after it gets inserted with the computed column, I need to keep that value in that column. I first used "Persisted" attribute with the column, but i dropped it and re-created it without "Persisted" , and still when the travelcode value changes from 'A' the confirmDate value gets erased.
    ??

    Thanks in advance

    Assuming it is possible, the best way would be to modify the code that inserts/updates this table to simply put in the correct value. You would need some logic for this, and potentially some data too. 

    Otherwise I don't know why you need a computed column. A trigger could set it/reset it as appropriate. Look up triggers in Books Online.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • John Mitchell-245523 - Thursday, May 4, 2017 2:34 AM

    The value of a persisted column is calculated at select time, not at insert or update time - consider the code below and you'll see that.  

    If I'm reading that correctly, that's not quite right.  The definition you have above is for non-persisted computed columns.  Persisted computed columns are materialized and physically stored in the table.  Think of them as a "micro indexed view for a single column". 

    Please see the following MS documentation, particularly the section titled "Persisted".
    https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-table-computed-column-definition-transact-sql

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Zososql - Wednesday, May 3, 2017 10:37 PM

    Even if the value in confirmDate changes after it gets inserted with the computed column, I need to keep that value in that column. I first used "Persisted" attribute with the column, but i dropped it and re-created it without "Persisted" , and still when the travelcode value changes from 'A' the confirmDate value gets erased.
    ??

    Thanks in advance

    How did you ever do that? GETDATE() is non-deterministic, so it can't be used for a PERSISTED computed column.
    In this case, you might want to include a trigger for insert and update to get the moment when the value is defined as 'A'.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Jeff Moden - Thursday, May 4, 2017 12:08 PM

    John Mitchell-245523 - Thursday, May 4, 2017 2:34 AM

    The value of a persisted column is calculated at select time, not at insert or update time - consider the code below and you'll see that.  

    If I'm reading that correctly, that's not quite right.  The definition you have above is for non-persisted computed columns.  Persisted computed columns are materialized and physically stored in the table.  Think of them as a "micro indexed view for a single column". 

    Please see the following MS documentation, particularly the section titled "Persisted".
    https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-table-computed-column-definition-transact-sql

    Sorry, slip of the pen.  I did indeed mean a computed column, and a non-persisted one at that.

    John

  • John Mitchell-245523 - Friday, May 5, 2017 2:01 AM

    Jeff Moden - Thursday, May 4, 2017 12:08 PM

    John Mitchell-245523 - Thursday, May 4, 2017 2:34 AM

    The value of a persisted column is calculated at select time, not at insert or update time - consider the code below and you'll see that.  

    If I'm reading that correctly, that's not quite right.  The definition you have above is for non-persisted computed columns.  Persisted computed columns are materialized and physically stored in the table.  Think of them as a "micro indexed view for a single column". 

    Please see the following MS documentation, particularly the section titled "Persisted".
    https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-table-computed-column-definition-transact-sql

    Sorry, slip of the pen.  I did indeed mean a computed column, and a non-persisted one at that.

    John

    NP.  You should see what I sometimes come up with before coffee. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Is "Approval" the only thing which happens to the travels?
    Do they get cancelled, rescheduled, etc.?
    Such events related to travels must be recorded in a separate TravelEventLog table with a EventDate assigned for each record.

    By using a correct data model you avoid tricky problems.

    _____________
    Code for TallyGenerator

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply