Duplicates in temporal history table

Question

We have JCC feed that pumping data from Oracle to SQL Server 2016. For some unknown reasons there are duplicates in the history table with THE SAME start and end times. How can it happen? I tried to update record with the condition SET Column = Column. In that case I have 2 records where all the fields are the same, but start and end times are different. How can it happen that there are same datetimes?

UPDATE:

DDL:

CREATE TABLE [dbo].[LEASES](
    [LEASE_NUMBER] [CHAR](7) NOT NULL,
    [CREDIT_DECISION_CODE] [CHAR](1) NULL,
    [LEASE_APPLICATION] [CHAR](7) NULL,
    [ACCOUNT_NUMBER] [CHAR](8) NULL,
    [CELLULAR_NUMBER] [CHAR](10) NULL,
    [DEALER_CODE] [CHAR](5) NULL,
    [USERNAME] [CHAR](12) NULL,
    [LEASE_DATE] [DATETIME2](7) NULL,
    [NEW_USED_FLAG] [CHAR](1) NULL,
    [MANUFACTURER_CODE] [CHAR](6) NULL,
    [MODEL] [CHAR](10) NULL,
    [SERIAL_NUMBER_ELECTRONIC] [INT] NULL,
    [SERIAL_NUMBER_MECHANICAL] [CHAR](10) NULL,
    [CONTROL_HEAD] [CHAR](12) NULL,
    [LEASE_TERM] [SMALLINT] NULL,
    [LESSEE_CITY] [CHAR](17) NULL,
    [LESSEE_ADDRESS_1] [CHAR](30) NULL,
    [LESSEE_ADDRESS_2] [CHAR](30) NULL,
    [LESSEE_STATE] [CHAR](2) NULL,
    [LESSEE_ZIP_CODE] [CHAR](9) NULL,
    [LESSEE_NAME] [CHAR](30) NULL,
    [KEY_NAME] [CHAR](12) NULL,
    [BASE_PAYMENT] [DECIMAL](10, 2) NULL,
    [MONTHLY_SALES_TAX] [DECIMAL](10, 2) NULL,
    [INSURANCE] [DECIMAL](10, 2) NULL,
    [MONTHLY_PAYMENT] [DECIMAL](10, 2) NULL,
    [SECURITY_DEPOSIT] [DECIMAL](10, 2) NULL,
    [INVOICES_GENERATED_COUNT] [SMALLINT] NULL,
    [DATE_LAST_INVOICED] [DATETIME] NULL,
    [DATE_LAST_LATE_FEE] [DATETIME] NULL,
    [SECURITY_DEPOSITS_INVOICED] [DECIMAL](10, 2) NULL,
    [SECURITY_DEPOSITS_REFUNDED] [DECIMAL](10, 2) NULL,
    [ADVANCE_RENT] [DECIMAL](10, 2) NULL,
    [ADVANCE_SALES_TAX] [DECIMAL](10, 2) NULL,
    [TOTAL_ADVANCE_PAYMENT] [DECIMAL](10, 2) NULL,
    [AUTO_LEASE_EXPIRATION_DATE] [CHAR](4) NULL,
    [PAYMENTS_REMAINING] [SMALLINT] NULL,
    [PV_PAYMENTS_REMAINING] [DECIMAL](10, 2) NULL,
    [TAX_RATE] [DECIMAL](10, 4) NULL,
    [TAX_STATE] [CHAR](2) NULL,
    [LEASE_FACTOR] [DECIMAL](10, 4) NULL,
    [AMOUNT_FINANCED] [DECIMAL](10, 2) NULL,
    [REMARKS] [CHAR](60) NULL,
    [VOUCHER_NUMBER] [CHAR](12) NULL,
    [BILL_METHOD_ADVANCE] [CHAR](1) NULL,
    [FINANCING_PACKAGE] [INT] NULL,
    [BUYOUT_AMOUNT] [DECIMAL](10, 2) NULL,
    [BUYOUT_DATE] [DATETIME] NULL,
    [DEPRECIATION_MONTHS] [SMALLINT] NULL,
    [SALVAGE_VALUE] [DECIMAL](10, 2) NULL,
    [LAST_DEPRECIATION_DATE] [DATETIME] NULL,
    [LAST_DEPRECIATION_AMOUNT] [DECIMAL](10, 2) NULL,
    [ACCUMULATED_DEPRECIATION] [DECIMAL](10, 2) NULL,
    [BILL_METHOD_BUYOUT] [CHAR](1) NULL,
    [BUYOUT_INVOICED] [CHAR](1) NULL,
    [RECEIVED_DATE] [DATETIME] NULL,
    [LEASE_PROGRAM] [CHAR](5) NULL,
    [PAYMENTS_INCLUDED_ADVANCE] [SMALLINT] NULL,
    [SALESPERSON_CODE] [CHAR](5) NULL,
    [UNGUARANTEED_RESIDUAL_VALUE] [DECIMAL](10, 2) NULL,
    [UNEARNED_INCOME] [DECIMAL](10, 2) NULL,
    [DIRECT_COST] [DECIMAL](10, 2) NULL,
    [AMORTIZABLE_UNEARNED_INCOME] [DECIMAL](10, 2) NULL,
    [AMORTIZED_FLAG] [CHAR](1) NULL,
    [RESIDUAL_VALUE_PERCENTAGE] [DECIMAL](10, 4) NULL,
    [MINIMUM_LEASE_PAYMENTS] [DECIMAL](10, 2) NULL,
    [IMPLICIT_MONTHLY_INTEREST_RATE] [DECIMAL](10, 8) NULL,
    [AP_POSTED_FLAG] [CHAR](1) NULL,
    [AP_POSTED_DATE] [DATETIME] NULL,
    [CAPITALIZED_LEASE_FLAG] [CHAR](1) NULL,
    [LEASE_STATUS] [CHAR](1) NULL,
    [GROSS_INVESTMENT] [DECIMAL](10, 2) NULL,
    [ADVANCE_BILLED_FLAG] [CHAR](1) NULL,
    [AP_VOUCHER_NUMBER] [CHAR](12) NULL,
    [BANK_PACKAGE] [CHAR](5) NULL,
    [INSURANCE_BINDER] [CHAR](1) NULL,
    [CURRENT_BUYOUT] [DECIMAL](10, 2) NULL,
    [LEASE_AGE_YEARS] [SMALLINT] NULL,
    [GUARANTOR_NAME] [CHAR](30) NULL,
    [GUARANTOR_ADDRESS_LINE_1] [CHAR](30) NULL,
    [GUARANTOR_ADDRESS_LINE_2] [CHAR](30) NULL,
    [GUARANTOR_CITY] [CHAR](17) NULL,
    [GUARANTOR_STATE] [CHAR](2) NULL,
    [GUARANTOR_ZIP] [CHAR](9) NULL,
    [GUARANTOR_TELEPHONE] [CHAR](18) NULL,
    [GUARANTOR_SS_NUMBER] [CHAR](9) NULL,
    [GUARANTOR] [CHAR](30) NULL,
    [BILL_CYCLES_DEFER] [INT] NULL,
    [REVENUE_ACCOUNT] [CHAR](5) NULL,
    [INVOICE_TYPE] [CHAR](5) NULL,
    [CORRESPONDENCE_FLAG] [CHAR](1) NULL,
    [DOWN_PAYMENT] [DECIMAL](10, 2) NULL,
    [ADVANCE_INSURANCE] [DECIMAL](10, 2) NULL,
    [ORIGINAL_EQUIPMENT_COST] [DECIMAL](10, 2) NULL,
    [SERVICING_DEALER_CODE] [CHAR](5) NULL,
    [DEALER_BUYOUT_DATE] [DATETIME] NULL,
    [LEASE_OWNER_CODE] [CHAR](5) NULL,
    [LEASE_OWNER_DATE] [DATETIME] NULL,
    [VENDOR_CODE] [CHAR](5) NULL,
    [SPLIT_FUNDING_COUNT] [SMALLINT] NULL,
    [DEALER_AMOUNT] [DECIMAL](10, 2) NULL,
    [VENDOR_AMOUNT] [DECIMAL](10, 2) NULL,
    [SALESPERSON_AMOUNT] [DECIMAL](10, 2) NULL,
    [DEALER_OFFICE] [SMALLINT] NULL,
    [ASSESSMENT_YEAR] [SMALLINT] NULL,
    [PROPERTY_TAX_RATE] [DECIMAL](10, 4) NULL,
    [ASSESSMENT_FACTOR] [DECIMAL](10, 4) NULL,
    [MONTHLY_PROPERTY_TAX] [DECIMAL](10, 2) NULL,
    [MANAGER_CODE] [CHAR](5) NULL,
    [DEALER_BUYOUT_PROGRAM] [CHAR](5) NULL,
    [SHARED_RESID_METHOD] [CHAR](1) NULL,
    [SHARED_RESID_AMOUNT] [DECIMAL](10, 2) NULL,
    [SHARED_RESID_PERCENT] [DECIMAL](10, 4) NULL,
    [SHARED_RESID_L_AND_D] [CHAR](1) NULL,
    [SHARED_RESID_COLLECTION_TYPE] [CHAR](1) NULL,
    [SHARED_RESID_MONTHS_OVERDUE] [SMALLINT] NULL,
    [ORIGINAL_LEASE_TERM] [SMALLINT] NULL,
    [ORIGINAL_LEASE_DATE] [DATETIME] NULL,
    [ORIGINAL_BASE_PAYMENT] [DECIMAL](10, 2) NULL,
    [ORIGINAL_MINIMUM_PAYMENTS] [DECIMAL](10, 2) NULL,
    [NEW_PAYMENT_PLAN_FLAG] [CHAR](1) NULL,
    [NEW_PAYMENT_PLAN_OFFSET] [SMALLINT] NULL,
    [NEW_PAYMENT_PLAN_DATE] [DATETIME] NULL,
    [NEW_MINIMUM_PAYMENTS] [DECIMAL](10, 2) NULL,
    [BILLING_PERIOD_NUMBER] [SMALLINT] NULL,
    [BILLING_PERIOD_1_INVOICES] [SMALLINT] NULL,
    [BILLING_PERIOD_1_PAYMENT] [DECIMAL](10, 2) NULL,
    [BILLING_PERIOD_2_INVOICES] [SMALLINT] NULL,
    [BILLING_PERIOD_2_PAYMENT] [DECIMAL](10, 2) NULL,
    [BILLING_PERIOD_3_INVOICES] [SMALLINT] NULL,
    [BILLING_PERIOD_3_PAYMENT] [DECIMAL](10, 2) NULL,
    [BILLING_PERIOD_4_INVOICES] [SMALLINT] NULL,
    [BILLING_PERIOD_4_PAYMENT] [DECIMAL](10, 2) NULL,
    [BILLING_PERIOD_5_INVOICES] [SMALLINT] NULL,
    [BILLING_PERIOD_5_PAYMENT] [DECIMAL](10, 2) NULL,
    [BILLING_PERIOD_6_INVOICES] [SMALLINT] NULL,
    [BILLING_PERIOD_6_PAYMENT] [DECIMAL](10, 2) NULL,
    [EQUIPMENT_AMOUNT] [DECIMAL](10, 2) NULL,
    [SERVICE_AMOUNT] [DECIMAL](10, 2) NULL,
    [MONTHS_OF_RECOURSE] [SMALLINT] NULL,
    [VENDOR_PAID_FLAG] [CHAR](1) NULL,
    [SALESPERSON_PAID_FLAG] [CHAR](1) NULL,
    [AP_ACCOUNT] [CHAR](5) NULL,
    [GL_AP_ACCOUNT] [CHAR](4) NULL,
    [GL_OWNER_COMPANY] [CHAR](4) NULL,
    [GL_BRANCH] [CHAR](1) NULL,
    [GL_DEPARTMENT] [CHAR](2) NULL,
    [GL_EQUIPMENT] [CHAR](2) NULL,
    [GL_STATE] [CHAR](2) NULL,
    [DEALER_RECOURSE_PERCENT] [DECIMAL](10, 4) NULL,
    [VENDOR_RECOURSE_PERCENT] [DECIMAL](10, 4) NULL,
    [SALESPERSON_RECOURSE_PERCENT] [DECIMAL](10, 4) NULL,
    [NUMBER_OF_UNITS] [SMALLINT] NULL,
    [INVOICES_SUBJECT_TO_DISCOUNT] [CHAR](1) NULL,
    [ORIGINAL_LEASE_PROGRAM] [CHAR](5) NULL,
    [MISSING_PAYMENT_COUNT] [SMALLINT] NULL,
    [BROKER_CODE] [CHAR](5) NULL,
    [REVENUE_SHARING_CODE] [CHAR](5) NULL,
    [REVENUE_SHARING_FACTOR] [DECIMAL](5, 2) NULL,
    [REVENUE_SHARING_FLAG] [CHAR](1) NULL,
    [DEALER_SHARING_CODE] [CHAR](5) NULL,
    [DEALER_SHARING_FACTOR] [DECIMAL](5, 2) NULL,
    [DEALER_SHARING_FLAG] [CHAR](1) NULL,
    [CHARGEBACK_RESERVE_FLAG] [CHAR](1) NULL,
    [FUNDING_FEE] [DECIMAL](10, 2) NULL,
    [FUNDING_FEE_INVOICED_FLAG] [CHAR](1) NULL,
    [LEASE_REFERENCE] [CHAR](30) NULL,
    [ORIGINAL_LEASE_NUMBER] [CHAR](12) NULL,
    [ORIGINAL_ACCOUNT_NUMBER] [CHAR](12) NULL,
    [BASE_PAYMENT_TAXABLE] [CHAR](1) NULL,
    [DAY_TO_CHARGE_BACK] [SMALLINT] NULL,
    [LESSEE_FED_ID_NUMBER] [CHAR](9) NULL,
    [ORIGINAL_PURCHASE_PRICE] [DECIMAL](10, 2) NULL,
    [ORIGINAL_PURCHASE_DATE] [DATETIME] NULL,
    [ACQUISITION_PURCHASE_PRICE] [DECIMAL](10, 2) NULL,
    [ACQUISITION_PURCHASE_DATE] [DATETIME] NULL,
    [BROKER_REV_SHAR_AMT_OVERRIDE] [DECIMAL](10, 2) NULL,
    [DEALER_REV_SHAR_AMT_OVERRIDE] [DECIMAL](10, 2) NULL,
    [UPFRONT_TAX_FLAG] [CHAR](1) NULL,
    [UPFRONT_TAX_AMOUNT] [DECIMAL](10, 2) NULL,
    [UPFRONT_TAX_BILLED] [CHAR](1) NULL,
    [SysStart] [DATETIME2](7) GENERATED ALWAYS AS ROW START NOT NULL,
    [SysEnd] [DATETIME2](7) GENERATED ALWAYS AS ROW END NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [LEASE_NUMBER] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
    PERIOD FOR SYSTEM_TIME ([SysStart], [SysEnd])
) ON [PRIMARY]
WITH
(
SYSTEM_VERSIONING = ON ( HISTORY_TABLE = [dbo].[LEASES_HISTORY] )
)
GO

UPDATE 2:

I can't provide the real data, however this is the query I've used to get duplicates. Note that I am GROUPing BY all columns from the table, so these are definite duplicates:

SELECT LEASE_NUMBER
     , SysStart
     , SysEnd
     , cnt
  FROM
       (   SELECT *
                , COUNT(*) cnt
             FROM dbo.LEASES_HISTORY AS l
            GROUP BY l.LEASE_NUMBER
                   , l.CREDIT_DECISION_CODE
                   , l.LEASE_APPLICATION
                   , l.ACCOUNT_NUMBER
                   , l.CELLULAR_NUMBER
                   , l.DEALER_CODE
                   , l.USERNAME
                   , l.LEASE_DATE
                   , l.NEW_USED_FLAG
                   , l.MANUFACTURER_CODE
                   , l.MODEL
                   , l.SERIAL_NUMBER_ELECTRONIC
                   , l.SERIAL_NUMBER_MECHANICAL
                   , l.CONTROL_HEAD
                   , l.LEASE_TERM
                   , l.LESSEE_CITY
                   , l.LESSEE_ADDRESS_1
                   , l.LESSEE_ADDRESS_2
                   , l.LESSEE_STATE
                   , l.LESSEE_ZIP_CODE
                   , l.LESSEE_NAME
                   , l.KEY_NAME
                   , l.BASE_PAYMENT
                   , l.MONTHLY_SALES_TAX
                   , l.INSURANCE
                   , l.MONTHLY_PAYMENT
                   , l.SECURITY_DEPOSIT
                   , l.INVOICES_GENERATED_COUNT
                   , l.DATE_LAST_INVOICED
                   , l.DATE_LAST_LATE_FEE
                   , l.SECURITY_DEPOSITS_INVOICED
                   , l.SECURITY_DEPOSITS_REFUNDED
                   , l.ADVANCE_RENT
                   , l.ADVANCE_SALES_TAX
                   , l.TOTAL_ADVANCE_PAYMENT
                   , l.AUTO_LEASE_EXPIRATION_DATE
                   , l.PAYMENTS_REMAINING
                   , l.PV_PAYMENTS_REMAINING
                   , l.TAX_RATE
                   , l.TAX_STATE
                   , l.LEASE_FACTOR
                   , l.AMOUNT_FINANCED
                   , l.REMARKS
                   , l.VOUCHER_NUMBER
                   , l.BILL_METHOD_ADVANCE
                   , l.FINANCING_PACKAGE
                   , l.BUYOUT_AMOUNT
                   , l.BUYOUT_DATE
                   , l.DEPRECIATION_MONTHS
                   , l.SALVAGE_VALUE
                   , l.LAST_DEPRECIATION_DATE
                   , l.LAST_DEPRECIATION_AMOUNT
                   , l.ACCUMULATED_DEPRECIATION
                   , l.BILL_METHOD_BUYOUT
                   , l.BUYOUT_INVOICED
                   , l.RECEIVED_DATE
                   , l.LEASE_PROGRAM
                   , l.PAYMENTS_INCLUDED_ADVANCE
                   , l.SALESPERSON_CODE
                   , l.UNGUARANTEED_RESIDUAL_VALUE
                   , l.UNEARNED_INCOME
                   , l.DIRECT_COST
                   , l.AMORTIZABLE_UNEARNED_INCOME
                   , l.AMORTIZED_FLAG
                   , l.RESIDUAL_VALUE_PERCENTAGE
                   , l.MINIMUM_LEASE_PAYMENTS
                   , l.IMPLICIT_MONTHLY_INTEREST_RATE
                   , l.AP_POSTED_FLAG
                   , l.AP_POSTED_DATE
                   , l.CAPITALIZED_LEASE_FLAG
                   , l.LEASE_STATUS
                   , l.GROSS_INVESTMENT
                   , l.ADVANCE_BILLED_FLAG
                   , l.AP_VOUCHER_NUMBER
                   , l.BANK_PACKAGE
                   , l.INSURANCE_BINDER
                   , l.CURRENT_BUYOUT
                   , l.LEASE_AGE_YEARS
                   , l.GUARANTOR_NAME
                   , l.GUARANTOR_ADDRESS_LINE_1
                   , l.GUARANTOR_ADDRESS_LINE_2
                   , l.GUARANTOR_CITY
                   , l.GUARANTOR_STATE
                   , l.GUARANTOR_ZIP
                   , l.GUARANTOR_TELEPHONE
                   , l.GUARANTOR_SS_NUMBER
                   , l.GUARANTOR
                   , l.BILL_CYCLES_DEFER
                   , l.REVENUE_ACCOUNT
                   , l.INVOICE_TYPE
                   , l.CORRESPONDENCE_FLAG
                   , l.DOWN_PAYMENT
                   , l.ADVANCE_INSURANCE
                   , l.ORIGINAL_EQUIPMENT_COST
                   , l.SERVICING_DEALER_CODE
                   , l.DEALER_BUYOUT_DATE
                   , l.LEASE_OWNER_CODE
                   , l.LEASE_OWNER_DATE
                   , l.VENDOR_CODE
                   , l.SPLIT_FUNDING_COUNT
                   , l.DEALER_AMOUNT
                   , l.VENDOR_AMOUNT
                   , l.SALESPERSON_AMOUNT
                   , l.DEALER_OFFICE
                   , l.ASSESSMENT_YEAR
                   , l.PROPERTY_TAX_RATE
                   , l.ASSESSMENT_FACTOR
                   , l.MONTHLY_PROPERTY_TAX
                   , l.MANAGER_CODE
                   , l.DEALER_BUYOUT_PROGRAM
                   , l.SHARED_RESID_METHOD
                   , l.SHARED_RESID_AMOUNT
                   , l.SHARED_RESID_PERCENT
                   , l.SHARED_RESID_L_AND_D
                   , l.SHARED_RESID_COLLECTION_TYPE
                   , l.SHARED_RESID_MONTHS_OVERDUE
                   , l.ORIGINAL_LEASE_TERM
                   , l.ORIGINAL_LEASE_DATE
                   , l.ORIGINAL_BASE_PAYMENT
                   , l.ORIGINAL_MINIMUM_PAYMENTS
                   , l.NEW_PAYMENT_PLAN_FLAG
                   , l.NEW_PAYMENT_PLAN_OFFSET
                   , l.NEW_PAYMENT_PLAN_DATE
                   , l.NEW_MINIMUM_PAYMENTS
                   , l.BILLING_PERIOD_NUMBER
                   , l.BILLING_PERIOD_1_INVOICES
                   , l.BILLING_PERIOD_1_PAYMENT
                   , l.BILLING_PERIOD_2_INVOICES
                   , l.BILLING_PERIOD_2_PAYMENT
                   , l.BILLING_PERIOD_3_INVOICES
                   , l.BILLING_PERIOD_3_PAYMENT
                   , l.BILLING_PERIOD_4_INVOICES
                   , l.BILLING_PERIOD_4_PAYMENT
                   , l.BILLING_PERIOD_5_INVOICES
                   , l.BILLING_PERIOD_5_PAYMENT
                   , l.BILLING_PERIOD_6_INVOICES
                   , l.BILLING_PERIOD_6_PAYMENT
                   , l.EQUIPMENT_AMOUNT
                   , l.SERVICE_AMOUNT
                   , l.MONTHS_OF_RECOURSE
                   , l.VENDOR_PAID_FLAG
                   , l.SALESPERSON_PAID_FLAG
                   , l.AP_ACCOUNT
                   , l.GL_AP_ACCOUNT
                   , l.GL_OWNER_COMPANY
                   , l.GL_BRANCH
                   , l.GL_DEPARTMENT
                   , l.GL_EQUIPMENT
                   , l.GL_STATE
                   , l.DEALER_RECOURSE_PERCENT
                   , l.VENDOR_RECOURSE_PERCENT
                   , l.SALESPERSON_RECOURSE_PERCENT
                   , l.NUMBER_OF_UNITS
                   , l.INVOICES_SUBJECT_TO_DISCOUNT
                   , l.ORIGINAL_LEASE_PROGRAM
                   , l.MISSING_PAYMENT_COUNT
                   , l.BROKER_CODE
                   , l.REVENUE_SHARING_CODE
                   , l.REVENUE_SHARING_FACTOR
                   , l.REVENUE_SHARING_FLAG
                   , l.DEALER_SHARING_CODE
                   , l.DEALER_SHARING_FACTOR
                   , l.DEALER_SHARING_FLAG
                   , l.CHARGEBACK_RESERVE_FLAG
                   , l.FUNDING_FEE
                   , l.FUNDING_FEE_INVOICED_FLAG
                   , l.LEASE_REFERENCE
                   , l.ORIGINAL_LEASE_NUMBER
                   , l.ORIGINAL_ACCOUNT_NUMBER
                   , l.BASE_PAYMENT_TAXABLE
                   , l.DAY_TO_CHARGE_BACK
                   , l.LESSEE_FED_ID_NUMBER
                   , l.ORIGINAL_PURCHASE_PRICE
                   , l.ORIGINAL_PURCHASE_DATE
                   , l.ACQUISITION_PURCHASE_PRICE
                   , l.ACQUISITION_PURCHASE_DATE
                   , l.BROKER_REV_SHAR_AMT_OVERRIDE
                   , l.DEALER_REV_SHAR_AMT_OVERRIDE
                   , l.UPFRONT_TAX_FLAG
                   , l.UPFRONT_TAX_AMOUNT
                   , l.UPFRONT_TAX_BILLED
                   , l.SysStart
                   , l.SysEnd
           HAVING COUNT(*) > 1
       ) a;

UPDATE 3: Ok, by this time this is new info I was able to got. We tried to catch rpc_completed events to see what was actually going on. The trace file had 7 different statements with the different event_sequence numbers. As far as I understand this means that these statements were executed in different transactions. I'll try to prepare more detailed update later, but for now the statements were following (All the statements except the final one were executed using sp_prepexec stored procedures):

There are actually 3 different states the row had, let's call them x,y,z

UPDATE record by PK with x state -- at this point there is no such record in the DB
UPDATE record by PK with y state-- at this point there is no such record in the DB
INSERT record with x state
INSERT record with y state -- this failed with PK constraint violation
UPDATE record to the x state -- that actually didn't change any row, so the actual record didn't change
UPDATE record to the y state -- the actual record changed
UPDATE record to the z state using sp_execute procedure where the handle_id is the same from the previous update (updating to the z state) but the values passed via parameters are different

So at the end of these operations we have:

Original table is in z state, and the history table has something like that (the datetimes are the real values):

x state columns, 2017-11-01 16:55:31.3358248, 2017-11-01 16:55:31.3358248
x state columns, 2017-11-01 16:55:31.3358248, 2017-11-01 16:55:31.3358248
y state columns, 2017-11-01 16:55:31.3358248, 2017-11-01 16:55:41.9296659

You sure no one has been inserting data directly into the history table? — iamdave, Oct 27 '17 at 14:30
A key point that you don't seem to have mentioned - all of your duplicates seem to have identical start and end times. That would seem to be "valid" in that if you update a row multiple times within the same "instant", what would you *expect* to see in the history table? — Damien_The_Unbeliever, Oct 31 '17 at 08:17
@Damien_The_Unbeliever I expect that there is at least nano second (or even less) difference between 2 statements. How can it be possible to have 2 UPDATES at EXACTLY the same time? — Dmitrij Kultasev, Oct 31 '17 at 08:23
@DmitrijKultasev - just because `datetime2`s have an *accuracy* to 100ns, that doesn't mean that they're obtained from a source that is *precise* to 100ns intervals. See [`SYSUTCDATETIME`](https://learn.microsoft.com/en-us/sql/t-sql/functions/sysutcdatetime-transact-sql)'s Remarks section. — Damien_The_Unbeliever, Oct 31 '17 at 08:26
How can I re-produce that behavior? What is the source of that datetime? Windows machine where the Sql Server is running? — Dmitrij Kultasev, Oct 31 '17 at 08:31
Do you have any `UPDATE` statements using MS's extended form that uses a `FROM` clause? That form won't error if the same row could be updated multiple times and what actual behaviour it produces is not guaranteed. — Damien_The_Unbeliever, Oct 31 '17 at 08:52
I couldn't re-produce manually, but I understand what do you say. Anyway, is there a way how to avoid such situation? — Dmitrij Kultasev, Oct 31 '17 at 10:51
@Damien_The_Unbeliever and @SMM is on track. Here is a relevant quote from MSDN: "FOR SYSTEM_TIME filters out rows that have period of validity with zero duration (SysStartTime = SysEndTime). Those rows will be generated if you perform multiple updates on the same primary key within the same transaction. In that case, temporal querying surfaces only row versions before the transactions and ones that became actual after the transactions." - taken from https://learn.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables . The solution is to use `FOR SYSTEM_TIME` — Alex, Nov 02 '17 at 06:02
I don't believe event_sequence shows transaction boundaries (see [here](https://blogs.msdn.microsoft.com/psssql/2007/02/20/the-importance-of-the-trace-event-sequence-column-and-sql-server-2005-sp2-changes/)). See [this question about viewing transactions in the profiler](https://stackoverflow.com/q/6635445/6490059). — SMM, Nov 02 '17 at 15:06

SMM · Answer 1 · 2017-11-02T14:59:51.920

Temporal tables use the begin time of the transaction as the timestamp used (see Temporal Tables in MS Docs under the insert, update, and delete sections). By doing multiple updates within your transaction, or an insert followed by an update within the same transaction you will get multiple records for a given primary key value with the same start/end times.

An excerpt from the link:

UPDATES: On an UPDATE, the system stores the previous value of the row in the history table and sets the value for the SysEndTime column to the begin time of the current transaction (in the UTC time zone) based on the system clock

Here is an example that will reproduce the behaviour. Notice that there are 2 identical entries for Miniature Widgets in the ProductsHistory table for ProductId = 2. Also note that although the updates for ProductId = 1 did not change the value, they still created identical history table records.

create table test.Products (
        ProductId       int not null primary key,
        ProductName     nvarchar(100) not null,
        EffectiveDate   datetime2(7) generated always as row start hidden not null,
        EndingDate      datetime2(7) generated always as row end hidden not null,
        period for system_time (EffectiveDate, EndingDate)
        )
        with (system_versioning = on (history_table = test.ProductsHistory));

go

begin tran

insert  test.Products (ProductId, ProductName) values
        (1, N'Widgets'),
        (2, N'Miniature Widgets');

update  test.Products set ProductName = 'Tiny Widgets' where ProductId = 2;
update  test.Products set ProductName = 'Miniature Widgets' where ProductId = 2;
update  test.Products set ProductName = 'Tiny Widgets' where ProductId = 2;
update  test.Products set ProductName = 'Widgets' where ProductId = 1;
update  test.Products set ProductName = 'Widgets' where ProductId = 1;

commit tran

select  p.ProductId, p.ProductName, p.EffectiveDate, p.EndingDate
from    test.Products for system_time all p;

select  *
from    test.ProductsHistory;

can you provide any live example? I couldn't re-produce it anyhow. I was able to get THE SAME start/end times for different records, but I couldn't get absolute duplicates. — Dmitrij Kultasev, Oct 31 '17 at 13:02
You should be able to create exact duplicates by updating one field, then setting it to a new value, then setting it back within the same transaction. Have you looked at a sampling for one specific data and retrieved all records for that lease number to see if there are other values that are not duplicates for the same start/end? — SMM, Oct 31 '17 at 13:27
See my updated example. Even if you update but the values do not change, a history record is still generated. Within the same transaction the start/end will be the same. (I do notice in your question your note about being in different transactions.) — SMM, Nov 02 '17 at 15:01

score 0 · Answer 2 · answered Nov 01 '17 at 15:51

I'm going to add another possibility as a separate answer although I think this is far less likely - the server clock has changed. I have seen instances (more with text log files than SQL but still possible) with time overlap where the server/PC time has changed. This can happen when daylight savings starts/ends (although in this scenario it is unlikely since the start/end fields are in UTC). A more remote possibility is that you are having server clock drift. I doubt you would see this on a server - we were seeing it on single core industrial PCs running hot and CPU frequently maxxed out. Even synching the clock daily we could be short by a minute or 2 at the end of the day.

it is not the case as we are getting duplicates much more frequently — Dmitrij Kultasev, Nov 01 '17 at 15:59

Duplicates in temporal history table

2 Answers2

Linked