[SOLVED] How to add HIDDEN property on column?

How to add HIDDEN property on column?

When temporal table is created, we need to defined start and end date time columns which can be hidden - not visible in SELECT * or INSERT without columns. I want to add one more column, which will contain information about the user who has commit the change.

The issue is, I am getting the following error:

Msg 13735, Level 16, State 1, Line 10
Cannot alter HIDDEN attribute on column 'UserID' in table 'GK' because this column is not a generated always column.

Here is the code:

DROP TABLE IF EXISTS GK;

CREATE TABLE GK
(
    [ID] INT
   ,[UserID] BIGINT DEFAULT (CONVERT(BIGINT, SESSION_CONTEXT(N'user_id')))  
)

ALTER TABLE GK
ALTER COLUMN [UserID] ADD HIDDEN;

Why I am not allowed to add this attribute on such column?

Solution

FOR this you need to use like below

[ GENERATED ALWAYS AS ROW { START | END } [ HIDDEN ] ]

GENERATED ALWAYS AS ROW START/END is compulsory. and

Also note that System-versioned table cannot have more than one 'GENERATED ALWAYS AS ROW END' column

Also note that System-versioned table cannot have more than one 'GENERATED ALWAYS AS ROW START' column

So if you are already using 2 dates column then it will not be possible. I think we can just use a normal column with default value.