Search code examples
sqlsql-servercalculated-columns

Computed column at creation of table gets evaluated every time

I have the following script that creates a table:

CREATE TABLE [dbo].[Persons] 
(
    [Id]            INT IDENTITY(1,1) NOT NULL,
    [Name]          NVARCHAR(250)     NOT NULL,
    [Surname]       NVARCHAR(250)     NOT NULL,
    [NumberOfNotes] INT               NOT NULL,
    [TotalCash]     FLOAT             NOT NULL,
    [Result] AS ([NumberOfNotes] * [TotalCash] * ROUND(RAND() * 2, 0)),

    CONSTRAINT [PK_Persons] PRIMARY KEY ([Id] ASC)
);

The table gets created correctly and whenever I insert a new person the Result gets calculated. Problem is that it gets re-evaluated every time I do a select. I would like the computed value to stay the same for that record. How do I achieve this? Thanks in advance!

Solution

  • I simple trick is to seed rand():

    [Result] AS ([NumberOfNotes] * [TotalCash] * ROUND(RAND(id) * 2, 0)),

    Basically, this is using a "deterministic" random number generator. You can do that in other ways as well.

    Alternatively, you could just assign it a value when you insert new rows into the table.