Temporal Table support in PowerDesigner 16.6 SQL Server 2016 XDB

[laurawachs]

Hi all,

I have only just started testing my shiny new version of PD 16.6, so I've only had a quick look for this feature, but not luck so far.

Microsoft has added support for system-versioned temporal tables to SQL Server 2016 (apparently part of ANSI SQL 2011), and my users have been asking for this feature. Like I said, I didn't see anything obvious that would allow me to generate the required DDL syntax, but I'm hoping I missed it. Does anyone know if the PD 16.6 SQL Server 2016 XDB has support for temporal tables?

Thanks,

Laura

[laurawachs]

Hi George,

Thanks for responding. Sorry for the delay in getting back to you...

For the record, I downloaded PD 16.6 SP2 (which contains the SQL Server 2016 XDB), and it doesn't appear that there is support for system-versioned temporal tables. At least, I didn't get any hits when I searched sqlsv2016.xdb for the string "SYSTEM_VERSIONING" (required as part of the CREATE TABLE syntax).

That being said, the syntax isn't all that complicated. Two columns of datetime2 data type have to be specified to be used as the row’s Valid From and Valid To dates, a PERIOD FOR SYSTEM_TIME argument that references the from\to dates, and a SYSTEM_VERSIONING table option (the history table can optionally be specified). For example (from MS documentation):

CREATE TABLE Department   
(  
    DepartmentNumber char(10) NOT NULL PRIMARY KEY CLUSTERED,   
    DepartmentName varchar(50) NOT NULL,   
    ManagerID INT  NULL,   
    ParentDepartmentNumber char(10) NULL,   
    SysStartTime datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,   
    SysEndTime datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,     
    PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)    
)  
WITH   
    (SYSTEM_VERSIONING = ON   
        (HISTORY_TABLE = dbo.Department_History, DATA_CONSISTENCY_CHECK = ON )  
    );

I did a little googling and found SAP KB article 2251401 about temporal table support in DB2 that states "an Enhancement Request has been logged under Change Request (CR) 673349"., and apparently PostgresQL 9.2 has temporal table support as well, so perhaps this is being worked on? Though evein if it was, that doesn't really help me right now

It seems to me that I should be able to customize the XDB and\or create an extension to get this DDL to generate, right? I don't find the documentation terribly helpful when it comes to this stuff, but I've been reviewing how the data compression options are implemented and I think I'm starting to catch on. Though by all means if you have any advice on how to implement this, I would love to hear it.

Thanks again for the reply. I feel honored that such an illustrious figure answered two of my questions, though perhaps everyone finds the new forum design as meh as I do that only PD stalwarts are still monitoring them on a regular basis.

Regards,

Laura

[GeorgeMcGeachie]

Hello Laura

How different is the DDL required to create one of these tables?