To homepage To homepage
Solved

SCD Surrogate Hash Key is non-deterministic.

  • 30 March 2023
  • 5 replies
  • 118 views
sierd.zuiderveld
Rank
Userlevel 3
Badge +1

Hi,

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? 

icon

Best answer by rory.smith 21 April 2023, 11:05

View original

5 replies

Thomas Lind
Rank
Userlevel 6
Badge +5

Hi Sierd

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?

sierd.zuiderveld
Rank
Userlevel 3
Badge +1

Hi Thomas, 

Everything is Type I, the natural key is the same as the primary key: 

Essentially I didn't change anything to the settings. 

Christian Hauggaard
Rank
Userlevel 6
Badge +5

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. 

rory.smith
Rank
Userlevel 5
Badge +7

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.

Christian Hauggaard
Rank
Userlevel 6
Badge +5

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

Reply


Terms and ConditionsCookie settings