Changing column definition in Hist table

MarkusPoehler

Is there a way to change the column definition of a column in a hist table?
I have some historised tables where columns are set to be NULLABLE by the AC, but i would like to have them as NOT NULLABLE.
Before the historisation there is a transformation on the column (i.e. CONVERT(varchar(7), startTime, 23) changed from datetime2 to varchar).
I found a solution with a table pre-script, but hoped there is a better way.

Related question: Can i change the primary key of a hist table, without changing the primary key of the table that gets historised? I haven't found a way to change those definitions on only the automatically generated hist table

Germo

MarkusPoehler

I have some historised tables where columns are set to be NULLABLE by the AC, but i would like to have them as NOT NULLABLE

I would not use a pre- or post script because this will not solve the issue. The automatically generated code of the historicization depends on the properties of the source column. I think the best way is to change the properties of the source column.

The structure of the historicization table and the automatically generated code of the historicization procedure depend on the data source of the historicization. You can use a transformation as a data source and change the definition of the specific column to make it not nullable. Have a look in the SSMS if the source column is NULLABLE or not. If the column is marked as nullable then you can use a column transformation to make it explicitly "not nullable", for example COALESCE(@this, 0) in case the column source is a number data type. Create the transformation and check if it is still marked as NULLABLE or not.

If you need the opposite way, you can use some NULLIF(@this, 0) which will make this column as NULLABLE. (@this is a placeholder for the current columns source.)

In your case:
You convert into varchar(7) and you will truncate the right part: '2020-09-30' will be '2020-09'.
You could try COALESCE(CONVERT(varchar(7), startTime, 23),'') or some other default like COALESCE(CONVERT(varchar(7), startTime, 23),'1900-01') depending on your required default.

In General: If your source is a table but not a transformation then first create a transformation which exactly repeats the tables structure, then you can switch the source of the historicization (AC will check if the columns and maybe data types are the same and will let you select only these new sources. But there are other ways to change).

Can i change the primary key of a hist table

Do you want to change the name of the default Satz_ID? It is possible to change, for example my default is Hist_ID. But I change this always at the beginning of a project. I don't know what happens in an existing project. Maybe it's also possible but all historicizations needs to be recompiled.
Look at this article in the wiki
https://dev.azure.com/AnalyticsCreator/AnalyticsCreator/_wiki/wikis/AnalyticsCreator.wiki/71/hidden-parameter-configuration

Or do you mean something different?

DimitriSorokin

I woudn't replace the NULLs in the historizized tables by the default values.
If you do it, the difference between the historized table and source table (between NULLs and defaults) will be detected during next historization.
It is better to create a transformation between source table and historized table and replace the NULL values by defaults in this transdormation.