+21
Accepted

Temporal Table Support

jordan.beacham 2 years ago updated by jono.murray 11 months ago 12 1 duplicate

It would be nice to have support for Temporal Tables.

SQLServer

Answer

Answer
Under review

Dear user,

SqlDbm currently doesn't support temporal tables.

But  If you provide SQL Script you trying to upload - we can think what we can do for you.

Thanks,

Eugene

Duplicates 1

Accepted

Thank you for good idea, Jordan


+1

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


+2

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.

+1

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.

Answer
Under review

Dear user,

SqlDbm currently doesn't support temporal tables.

But  If you provide SQL Script you trying to upload - we can think what we can do for you.

Thanks,

Eugene

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] )
)

+2

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

+1

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.