Add computed columns support to System-versioned temporal tables

[ssreerama]

• SqlPackage or DacFx Version: Any
• .NET Framework (Windows-only) or .NET Core: Any
• Environment (local platform and source/target platforms):

Steps to Reproduce:

1. Create a temporal system versioning table with computed column
2. Try to deploy it.
3. You will see the "System-versioned temporal tables with computed columns are not supported." even though this is supported in SQL server.

Did this occur in prior versions? If not - which version(s) did it work in?

(DacFx/SqlPackage/SSMS/Azure Data Studio)

[ssreerama]

Table Syntax:

CREATE TABLE [dbo].[Product] ( [ProductID] INT IDENTITY(1,1) NOT NULL, [QtyAvailable] smallint NULL, [UnitPrice] money, [InventoryValue_rename] AS QtyAvailable * UnitPrice, [SysStart] DATETIME2 (7) GENERATED ALWAYS AS ROW START NOT NULL, [SysEnd] DATETIME2 (7) GENERATED ALWAYS AS ROW END NOT NULL, CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED ([ProductID]), PERIOD FOR SYSTEM_TIME ([SysStart], [SysEnd]) ) WITH (SYSTEM_VERSIONING = ON(HISTORY_TABLE=[dbo].[Product_HISTORY], DATA_CONSISTENCY_CHECK=ON))

[NJLangley]

I have the same issue, only using a schema bound scalar function for the calculation. The code runs fine in SQL server and the calculated column works, but compiling/deploying the database project using Visual Studio or VSCode gives the error above.

[Dash]

This is quite frustrating as this is supported by SQL Server - doesn't really feel like an enhancement, more just nobody has got round to implementing it in VS.

[tony-donley]

I've hit the same snag also and had to work around with a different solution. Not ideal.

[mikegaziotis]

Just fyi for anyone that ended up here... The workaround involves pre-calculating the column (be it in your code and passing it as parameter, or in your SQL) and then also placing a CHECK CONSTRAINT on the table so that it validates the input to ensure it matches the desired formula. Far from ideal. Come on lads, sort this out

[daiplusplus]

The underlying error is because SSDT seems to think the History Table also contains the computed column as a computed-column - but if you manually define the History table with the computed-column as a normal column then it works fine.

For example:

Tables\Foo.sql

CREATE TABLE dbo.Foo_History (

    FooId int NOT NULL,
    MyComputedColumn int NOT NULL,

    Modified datetime2(7) NOT NULL,
    ValidTo datetime2(7) NOT NULL
);

GO
/* This clustering index def here is commented-out because SSDT complains about duplicate clustering index because SSDT seems to always use its own internal clustering index for history tables which uses only `ValidTo, Modified`.
CREATE CLUSTERED INDEX CX_Foo_History ON dbo.Foo_History ( ValidTo , Modified );
 */

GO

CREATE TABLE dbo.Foo (

    FooId int NOT NULL IDENTITY,
    MyComputedColumn AS ( 123 ) PERSISTED NOT NULL,

    Modified datetime2(7) GENERATED ALWAYS AS ROW START NOT NULL,
    ValidTo datetime2(7) GENERATED ALWAYS AS ROW END NOT NULL,

    PERIOD FOR SYSTEM_TIME ( Modified, ValidTo ),

    CONSTRAINT PK_Foo PRIMARY KEY ( FooId )
)
WITH (
    SYSTEM_VERSIONING = ON (
        HISTORY_TABLE = dbo.Foo_History,
        DATA_CONSISTENCY_CHECK = ON
    )
)

...this builds, compares, and deploys without any errors or warnings in SSDT for VS2019 and VS2022 (when the .sqlproj compatibility level is 150 or higher).

There is one gotcha: you can't also manually define the History table's clustering index as SSDT seems to always define that itself internally (as being over ( ValidTo, Modified ) (or whichever cols correspond to SYSTEM_TIME), hence why I commented it out in my code above. This won't be a problem unless you actually need a non-default clustered index.

I note that SQL Server does allow you to use your own clustered index for history tables - so this is an SSDT limitation.

[marlonfal]

Hi @daiplusplus I've tried your solution but the ValidTo (computed) value copies the '9999-12-31 23:59:59' value instead of the update date. This is what I did:

CREATE TABLE [dbo].[Example_HISTORY] (
    [Id] UNIQUEIDENTIFIER NOT NULL,
    [ValidFromDate] datetime2 (0) NOT NULL,
    [ValidToDate] datetime2 (0) NOT NULL,
    [ValidFrom] datetime2 (0) NOT NULL,
    [ValidTo] datetime2 (0) NOT NULL
)

CREATE TABLE [dbo].[Example] (
    [Id] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
    [ValidFromDate] AS [ValidFrom] PERSISTED NOT NULL,
    [ValidToDate] AS [ValidTo] PERSISTED NOT NULL,
    [ValidFrom] datetime2 (0) GENERATED ALWAYS AS ROW START NOT NULL,
    [ValidTo] datetime2 (0) GENERATED ALWAYS AS ROW END NOT NULL, 
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo),
)
WITH (SYSTEM_VERSIONING = ON(HISTORY_TABLE=[dbo].[Example_HISTORY], DATA_CONSISTENCY_CHECK=ON))

This is the query I am running:

select * from dc.Example FOR SYSTEM_TIME ALL where id = 'b134a1fa-2a6f-47c6-913d-afa353e974aa'

And these are the results I'm getting:

Do you know how to fix this?

[daiplusplus]

@marlonfal Your example doesn't build for me because you're creating the Current table before the History table.

[marlonfal]

@daiplusplus I've just updated my comment, thanks