I obtain a deployment error when I try to make my table historic.
Computed column 'SCD Surrogate Hash Key' in table ' <mytable>' cannot be persisted because the column is non-deterministic.
I think this has to do with the excessive amount of columns in the table: 695 columns, all varchar(2000).
What confuses me is that when I set the hashing algorithms to debug, I still get the same error
I don't have any transformations on this table, its a straight copy from to ODX.
Is there a limit on the amount of fields a History table can have?
Best answer by rory.smith
Is your _fd_edit field a varchar(2000) that you are overriding to be datetime? In that case if the conversion does not have a deterministic style type, it will not be able to include it in the calculated column for the hash. From the documentation:
Source or target type is datetime or smalldatetime, the other source or target type is a character string, and a nondeterministic style is specified. To be deterministic, the style parameter must be a constant. Additionally, styles less than or equal to 100 are nondeterministic, except for styles 20 and 21. Styles greater than 100 are deterministic, except for styles 106, 107, 109 and 113.
No, there is no limits to how many rows a history table contains. It is a normal table with the difference that it will not delete rows in the valid table, only update or add new ones.
I am not sure changing the hashing algorithm, would make it work necessarily. How is the history table setup in terms of natural key, type 0, I and II types?
Hi @sierd.zuiderveld Could you please try with the default hashing algorithm (SHA-1, SQL Server 2005 +)? And also to troubleshoot further please try with a table with only a few columns. Does the table only have one mapping? Have you added any indexes? If so try a table with only one mapping and no manually created indexes.
Is your _fd_edit field a varchar(2000) that you are overriding to be datetime? In that case if the conversion does not have a deterministic style type, it will not be able to include it in the calculated column for the hash. From the documentation:
Source or target type is datetime or smalldatetime, the other source or target type is a character string, and a nondeterministic style is specified. To be deterministic, the style parameter must be a constant. Additionally, styles less than or equal to 100 are nondeterministic, except for styles 20 and 21. Styles greater than 100 are deterministic, except for styles 106, 107, 109 and 113.
Hi @sierd.zuiderveld did the answers above help resolve the issue? If so please help us by marking the best answer. Also please let us know if you have any follow up questions
We use 3 different kinds of cookies. You can choose which cookies you want to accept. We need basic cookies to make this site work, therefore these are the minimum you can select. Learn more about our cookies.
