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?

It would have been nice if they provided this out of the box with temporal tables. — Rigerta, Mar 08 '18 at 08:11
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. — Abdul Azeez, Aug 16 '18 at 22:04

score 6 · Accepted Answer · answered Mar 08 '18 at 08:08

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

score 3 · Answer 2 · answered Sep 30 '20 at 18:40

3

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;

answered Sep 30 '20 at 18:40

gotqn

42,737
46
157
243

How do you remove the HIDDEN column attribute? REMOVE HIDDEN does not seem to work. – WillC Nov 06 '22 at 19:08

score 0 · Answer 3 · answered Dec 06 '22 at 05:26

0

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.

answered Dec 06 '22 at 05:26

user2708351

121
7

How to add HIDDEN property on column?

3 Answers3

Linked