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

1. 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).
2. 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.

Answer 1

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] 

Answer 2

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.