In my November 2015 post, An Introduction to Temporal Tables in SQL Server 2016 using a DeLorean, I wrote:
HIDDENproperty is optional and will hide these columns from a standard
SELECTstatement for backward compatibility with our application and queries. You cannot apply the
HIDDENproperty to an existing column.
It turns out that this is no longer true. You can apply the
HIDDEN property to an existing period column.
Let’s assume you have a temporal table containing two visible period columns,
EndDate, which you’d like to hide from a typical
ALTER TABLE ... ALTER COLUMN statement, simply place the
ADD HIDDEN syntax after the period column name(s).
ALTER TABLE [dbo].[Account] ALTER COLUMN [StartDate] ADD HIDDEN;
ALTER TABLE [dbo].[Account] ALTER COLUMN [EndDate] ADD HIDDEN;
You can also remove this flag if you wish, using
ALTER TABLE [dbo].[Account] ALTER COLUMN [StartDate] DROP HIDDEN;
ALTER TABLE [dbo].[Account] ALTER COLUMN [EndDate] DROP HIDDEN;
This is a great improvement to an already fantastic feature of SQL Server 2016. Thanks to Borko Novakovic for this tip.
If you have any more temporal table tricks you want to share, find me on Twitter at @bornsql.