0

I am trying to create a persisted computed column in a SYSTEM_VERSIONING table dbo.Users as follows:

ALTER TABLE dbo.Users 
    ADD SessionId AS usr.GetSession() PERSISTED 
        CONSTRAINT FK_dboUsers_IdSession 
            FOREIGN KEY REFERENCES dbo.Sessions(IdSession)

Where usr.GetSession() is just retrieving the value stored as BIGINT in SESSION_CONTEXT('IdSession') and converting it to again to BIGINT.

CREATE OR ALTER FUNCTION usr.GetSession()
RETURNS BIGINT WITH SCHEMABINDING
AS
BEGIN
    RETURN CONVERT(BIGINT, SESSION_CONTEXT(N'IdSession'))
END

But getting the following error:

Computed column 'SessionId' in table 'Users' cannot be persisted because the column is non-deterministic.

It is obviously because:

SELECT OBJECTPROPERTY(OBJECT_ID('usr.GetSession'), 'IsDeterministic') AS IsDeterministic;

Is returning 0

A little bit of searching found this about Deterministic and nondeterministic functions

CONVERT
Deterministic unless one of these conditions exists:

  • Source type is sql_variant.
  • Target type is sql_variant and its source type is nondeterministic.

So, I am understanding that there is no way to make my computed column persisted with a user defined scalar function as sql_variant cannot be handled as deterministic value.

Or there can be some walk around to solve my problem? Or some other solution? Any idea?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Max Buzyak
  • 25
  • 4
  • 3
    No, there is no work around to make the conversion of a `sql_variant` to a different data type deterministic. – Thom A Aug 09 '22 at 21:37
  • 2
    Even if there was, it would also depend on the function, and the function *cannot* be deterministic because it depends on the `SESSION_CONTEXT`. Honestly this feels like an XY problem: why do you want to persist something which depends on the session that's connected, and what do you expect to happen if another session connects? – Charlieface Aug 09 '22 at 23:25
  • I want to persist it to create a Foreign Key to the table of sessions dbo.sessions(IdSession). In order to indentify "IdSession" who modifies or deletes a row and store it in the history table of dbo.usersHistory, this way I ensure that Session who modifies or deletes a row is identified and cannot be corrupted by deleting the session in the future. – Max Buzyak Aug 10 '22 at 06:18
  • Sounds like you need it as a *default*, not as a computed column. It makes no sense to use it as a computed column, it would recalculate every time you *select* it, not just modify it. – Charlieface Aug 10 '22 at 08:28
  • 9 out of 10 times, when people think a persisted computed column is going to do what they want, they are wrong. This is because, despite what the name might seem to imply, `PERSISTED` does *not* mean "set once on creation". The value of a computed column is always based on the *current* state of the row, persisted or not. `PERSISTED` just removes the need for computing the value on the fly every time, and is necessary only if the result involves floating-point calculations -- at other times it's just a performance optimization/tradeoff (I/O for CPU) and does not offer different semantics. – Jeroen Mostert Aug 10 '22 at 08:54

1 Answers1

0

No, there is no workaround. You cannot do anything with sql_variant unless you convert it (even implicitly), and as you mention, that is not deterministic.

Be that as it may, it seems you are going down the wrong road anyway.

A computed column is the wrong thing here, as in this case it would change every time it was read, whereas it seems you want it changed every time the row is inserted.

Instead you need a DEFAULT

ALTER TABLE dbo.Users 
    ADD SessionId bigint DEFAULT (usr.GetSession())
Charlieface
  • 52,284
  • 6
  • 19
  • 43
  • Thank for your answer @Charlieface , but this will not work for my purpose. I do not really care about dbo.Users(SessionId) but more about dbo.UsersHistory(SessionId), which was automatically generated using option of system_versioning, where SessionId will be calculated every time the table is being UPDATED or DELATED and saved in dbo.UsersHistory, this way I will have a track on what changes the table suffer and WHO made it. – Max Buzyak Aug 10 '22 at 09:08
  • You can't have different columns on one as opposed to the other, the history table must match exactly. I still think you should create a `DEFAULT` rather than a computed column, otherwise it will change every time you select – Charlieface Aug 10 '22 at 10:41
  • No, there are not going to have different columns. Columns are identical. The thing is with computed column, history table saves the IdSession of WHO was updating/deleting. I already implemented Default constraint you are suggesting in another column called "CreatedBy" as constraint will only work for inserting new records. See link below for better understanding: https://stackoverflow.com/questions/41602579/sql-server-2016-temporal-table-how-to-identify-the-user The main difference is that he is using SUSER_SNAME() but I will like to use self table of session dbo.session(Id) – Max Buzyak Aug 10 '22 at 11:01