Consistency in non-persistent computed columns within transactions Is there a way to prevent a non-persistent computed column from updating within a transaction? 

I have thrown together an example which shows that these columns can update when using non-deterministic functions (in this instance getutcdate()). Does this not break the consistency requirements of a transaction? I have tried using transaction levels to prevent this (serializable and snapshot) which has not helped.

Query:

set nocount on;

create table dbo.TransactionTest (
    [Timestamp] datetime,
    IsUpdated as case when datediff(second, [Timestamp], getutcdate()) &gt;= 1 then 1 else 0 end
);

insert into dbo.TransactionTest ([Timestamp]) values (getutcdate());

begin tran

select  *
from    dbo.TransactionTest;

waitfor delay '00:00:01';

select  *
from    dbo.TransactionTest;

rollback

drop table dbo.TransactionTest;


Results:

Timestamp               IsUpdated
----------------------- -----------
2017-03-19 00:44:28.130 0

Timestamp               IsUpdated
----------------------- -----------
2017-03-19 00:44:28.130 1
There's a few different questions here.

Q: Can I force GETUTCDATE() to stay the same during a transaction?

No. If you have a long-running transaction, the time is going to change while you work. This has nothing to do with your isolation level, and more to do with the fact that life doesn't have a Pause button. (Yet. I've tried pointing Dr. Horrible's Freeze Ray at my computer, but that just made my solid state drives become even more solid.)

Q: Can I force non-deterministic functions to return the same result every time?

By definition, no. If you could, they would be deterministic.

Q: If I need dates/times to stay static during a transaction, what do I do?

If you only need a single value, create a variable, and set its value at the beginning of your transaction. In sp_BlitzFirst, for example, I set a @StartSampleTime variable, and then keep using that throughout the code.

If you need multiple rows, select the data into a permanent object like user table or a temp table, and repeatedly refer to them during the life of the transaction.