more quick actions
minimize quick actions
Topic starter
Hello,
Do you think SQL Server temporal tables are complient with a part of 21CFR ?
Here is a script to transform a trend tables to a temporal table.
CREATE SCHEMA History;
GO
ALTER TABLE [CyberSecurityDemo_Database].[dbo].[TrendTable]
ADD
SysStartTime datetime2(0) GENERATED ALWAYS AS ROW START HIDDEN
CONSTRAINT DF_SysStart DEFAULT SYSUTCDATETIME()
, SysEndTime datetime2(0) GENERATED ALWAYS AS ROW END HIDDEN
CONSTRAINT DF_SysEnd DEFAULT CONVERT(datetime2 (0), '9999-12-31 23:59:59'),
PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime);
GO
ALTER TABLE [CyberSecurityDemo_Database].[dbo].[TrendTable]
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = History.TrendTable))
;
Posted : 24/07/2018 2:20 pm
Thanks a lot Arnaud!
No idea about 21CFR, but I was not aware about this feature, which seems to be very interesting.
If there are other curious persons here an interesting description from Microsoft:
(It works only from SQL Server 2016).
🙂
Posted : 24/07/2018 2:38 pm
Topic starter
Unfortunatly we can't force value of SysStartTime and SysEndTime with PcVue timestamps otherwise we could imagine to use it for log tables.
That why I think we can use it for a more reliable 21CFR offer.
Posted : 24/07/2018 4:26 pm