Temporal Table Support

Thank you for good idea, Jordan

Do you have suggestions how to implement this?
Just add the "generate as temporary table" property in the table properties? Should Temporal Tables differ from other tables on the diagram?

Thank you for your assistance,

SqlDBM Team

Hi. I am actually talking about temporal tables, not temporary. They are system versioned tables now offered in sql server, as of Sql Server 2016. A simple “generate as temporal table” could tell the generate sql functionality to add the extra system-versioned syntax on the end of the table.

When working with temporal tables on SQL Server, SQL Server require to have 2 columns of type datetime2, which the engine will control. 

From a physical model perspective, it is a change to the DDL, and SQL Server will create two physical tables.

From a logical perspective however, all that is needed is to indicate that the table is a temporal table, and this can be done using a different icon or other visualization.

Thanks very much for the reply, i noticed there is another thread regarding support for temporal tables which i forgot about, sorry to raise it again. Some of the code is listed below :

CREATE TABLE [Dim].[Address](
[AddressSK] [int] NOT NULL,
[address_line_1] [varchar](40) NOT NULL,
[address_line_2] [varchar](40) NOT NULL,
[address_line_3] [varchar](40) NOT NULL,
[address_line_4] [varchar](40) NOT NULL,
[postcode] [varchar](25) NOT NULL,
[IsAddressOnHold] [varchar](3) NOT NULL,
[AddressHeldReason] [varchar](30) NOT NULL,
[DataSource] [varchar](20) NOT NULL,
[StartTime] [datetime2](0) GENERATED ALWAYS AS ROW START NOT NULL,
[EndTime] [datetime2](0) GENERATED ALWAYS AS ROW END NOT NULL,
[IsDeliveryExcluded] [varchar](3) NOT NULL,
CONSTRAINT [PK_DimAddress] PRIMARY KEY NONCLUSTERED
(
[AddressSK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Dimensions],
PERIOD FOR SYSTEM_TIME ([StartTime], [EndTime])
) ON [Dimensions]
WITH
(
SYSTEM_VERSIONING = ON ( HISTORY_TABLE = [History].[Address] )
)

just to chime in, I'd be almost as happy if it just ignored them for the time being.  I have a multi-step process to clean out the temporality before reverse engineering, but it'd be much nicer if I could export from the database and import into sqldbm

I agree on keeping them hidden but identify the table as being versioned.  Another option would be to identify that a table is under Change Tracking, etc.

I'd also appreciate if the forward-engineering statement would be able to ignore the options. It choked on the GENERATED keyword this time around so the forward-engineer dropped all tables in my diagram. :(

Any progress on support for temporal tables? Would be great for both forward and reverse engineering.

Lack of support for temporal tables prevents my team from using this product.  Our current DM tool doesn't support it either.  I believe SQLDBM could gain some market share from the established products by implementing temporal table support.  The design interface is very nice and intuitive.

Same here. I just had to stop a purchase order for business licenses for our shop because of this. Too bad too, since otherwise this software seems like a perfect fit.

As for implementation, just a checkbox in table settings to indicate it's a versioned table, and the ability to set the versioned table name would be outstanding. Pretty simple from a syntax point of view too, just add to the CONSTRAINT clause:

CONSTRAINT ...
  ,SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL
  ,SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL
  ,PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime))
WITH (SYSTEM_VERSIONING = ON
  (HISTORY_TABLE = dbo.TemporalTableName, DATA_CONSISTENCY_CHECK = ON))