December 9, 2020 at 1:36 pm
Hi,
Just wondered if anyone knows off hand how the rownumber field in a history table works.
What I'm doing is unioning the history table and the main table on a schedule then processing the events in date order to model state changes. As we're firing our replications directly into a temporal table what we're finding is that SQL is batching the replications together, sometimes the same PK appears in the same batch, when this happens we see two events with the same validFrom --> validTo dates in the history. I'm making the assumption here that it's making the entries in the order that the replication is presenting them, if it isn't then I guess I need to dig into how the batching is working.
If that is the case then I'm wondering whether I can use the rownumber field to order the events in the same order they're coming in off the replications - at the moment it's date based but I'm hitting the above problem making the row evaluations occur in what I believe to be an arbitrary order.
What I don't know is whether the rownumber is generated exclusively in order in the main table, then the rownumber is copied to the history table, or the history table contains it's own row number sequence. I think it's the former, but it's difficult to prove that conclusively.
December 9, 2020 at 9:36 pm
What do you mean by row number? How is that column created and populated - is it really an identity column? If it is really an identity column then the definition exists on the primary table - but does not exist on the history table, meaning it is not generated for history and only copied to the history table.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
December 9, 2020 at 9:56 pm
What do you mean by row number? How is that column created and populated - is it really an identity column? If it is really an identity column then the definition exists on the primary table - but does not exist on the history table, meaning it is not generated for history and only copied to the history table.
If SQL replication is being used, you might to specify NOT FOR REPLICATION on the original identity column to keep SQL from generating a new identity when the replication agent inserts the row.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply