Random value in computed columns

Question

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             NULL,
    [Result] AS ([NumberOfNotes] * [TotalCash] * (RAND()*(100-30)+30)),

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

The only 2 possible scenarios in my situation when inserting a new record are:

inserting a new record with [Name], [Surname], [NumberOfNotes], [Result]
inserting a new record with [Name], [Surname], [NumberOfNotes], [TotalCash]

So I have to satisfy the following 2 criteria during creation

I should be able to insert a new record where [Result] gets computed in case I am not inserting it. The computation formula is [NumberOfNotes] * [TotalCash] * SomeRandomNumber and I know for sure in this case the [TotalCash] will always be there for the formula an will never be null. But if instead I am inserting [Result] manually then it will insert my value and not the computed one ([TotalCash] can be null in this case).
The SomeRandomNumber is fixed per record. I tried so far the (RAND()*(100-30)+30)) because I want a random number between 30 and 100 but it keeps changing for every select I run on the records. If instead I seed it as (RAND(Id)*(100-30)+30)) it's the same for multiple records.

So for the possibility that you don't add the result, you could implement an "after insert" trigger that would modify the Result column if it is NULL. And for the RAND thing, I don't think I understand you. What seems to be the problem with it changing? — Anastas.R, Apr 10 '20 at 08:48
@Tarta . . . This looks just like your previous question and the answer is the same . . . add a seed to `rand()`. — Gordon Linoff, Apr 10 '20 at 10:51

score 0 · Answer 1 · answered Apr 10 '20 at 08:24

If you want to be able to actually write to the Result column, you can't use a computed column.

I would recommend having a regular column for Result, and using a view to implement the display logic, like:

create view PersonsView as
select
    Id,
    Name,
    Surname,
    NumberOfNotes,
    TotalCash,
    coalesce(Result, NumberOfNotes * TotalCash * rand(Id) * (100 - 30) + 30) Result
from [dbo].[Persons]

score 0 · Answer 2 · answered Apr 10 '20 at 09:12

The second condition can't be done using a computed column, unless the computed column is declared persisted.
But for the computed column to be persisted it must be based on a deterministic function - and Rand is non-deterministic.

What you can do, however, is to use a trigger to compute the column:

CREATE TRIGGER tr_Persons_InsteadOfInsert
ON dbo.Persons
INSTEAD OF INSERT
AS

INSERT INTO dbo.Persons (Name, SurName, NumberOfNotes, TotalCach, Result)
SELECT  Name
      , SurName
      , NumberOfNotes
      , TotalCach
      , CASE WHEN TotalCach IS NOT NULL AND Result IS NULL THEN  
          [NumberOfNotes] * [TotalCash] * (RAND()*(100-30)+30)
        ELSE 
            Result
        END)
FROM Inserted

GO

Of course, that would mean that the Result column must be a regular column, not a computed column, I'm guessing you want it as float - so you have to change the definition of this column.

Random value in computed columns

2 Answers2