How to add HIDDEN property on column?

Question

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?

Interlink

It would have been nice if they provided this out of the box with temporal tables. – Dissipate

For temporal table, we cannot add hidden column type other than DateTime ans as simple solution create view from this table and exclude the column UserID and use this view. – Hoicks

Answer 1

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.

Refer more from Microsoft - https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql

Swarth

Answer 2

Th correct answer here is that we can add this property only for temporal tables date columns (currently).

If the versioning is stopped and the columns are not hidden, the property is added like this:

ALTER TABLE dbo.Department
    ALTER COLUMN SysStartTime ADD HIDDEN;

ALTER TABLE dbo.Department
    ALTER COLUMN SysEndTime ADD HIDDEN;

Interlink

How do you remove the HIDDEN column attribute? REMOVE HIDDEN does not seem to work. – Lita

Answer 3

There is a commercial product called DBDefence. It can mask and also completely hide columns in tables for certain logins. It is available for all SQL Servers starting from SQL Server R2.

Disclaimer: I'm associated with the vendor.

Truncate