COMPUTED COLUMN needing to retain value

Question

Post reply

COMPUTED COLUMN needing to retain value

Zososql

Ten Centuries

Points: 1215
More actions
May 3, 2017 at 10:38 pm

#323277

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

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

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

DesNorton SSC-Insane Points: 24350 More actions · Answer 1

Create a trigger that updates the value of confirmDate

How to post data/code on a forum to get the best help.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

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

Jeff Moden SSC Guru Points: 1004177 More actions · Answer 4

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)

Luis Cazares SSC Guru Points: 183700 More actions · Answer 5

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 6

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

Jeff Moden SSC Guru Points: 1004177 More actions · Answer 7

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)

Sergiy SSC Guru Points: 110208 More actions · Answer 8

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