时间历史表中的重复项 [英] Duplicates in temporal history table
本文介绍了时间历史表中的重复项的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我们有 JCC 将数据从 Oracle 传送到 SQL Server 2016.由于某些未知原因，历史记录表中有重复的开始和结束时间.它怎么会发生?我尝试使用条件 SET Column = Column 更新记录.在这种情况下，我有 2 个记录，其中所有字段都相同，但开始时间和结束时间不同.怎么会出现相同的日期时间?
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?
更新:
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
更新 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;
更新 3:好的，这一次是我能够获得的新信息.我们试图捕捉 rpc_completed 事件以查看实际发生了什么.跟踪文件有 7 个不同的语句，它们具有不同的 event_sequence 编号.据我了解，这意味着这些语句是在不同的事务中执行的.稍后我会尝试准备更详细的更新，但现在语句如下(除了最后一条语句之外的所有语句都使用 sp_prepexec 存储过程执行):
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):
该行实际上有 3 种不同的状态，我们称它们为 x,y,z
There are actually 3 different states the row had, let's call them x,y,z
- PK 更新记录，带有
x状态——此时 DB 中没有这样的记录 - PK 使用
y状态更新记录——此时 DB 中没有这样的记录 - INSERT 记录带有
x状态 - INSERT 记录具有
y状态 -- 因违反 PK 约束而失败 - 将记录更新为
x状态 -- 实际上没有更改任何行，因此实际记录没有更改 - UPDATE 记录到
y状态——实际记录改变了 - 使用
sp_execute过程更新记录到z状态，其中 handle_id 与上次更新相同(更新到z状态)但是通过参数传递的值是不同的
- UPDATE record by PK with
xstate -- at this point there is no such record in the DB - UPDATE record by PK with
ystate-- at this point there is no such record in the DB - INSERT record with
xstate - INSERT record with
ystate -- this failed with PK constraint violation - UPDATE record to the
xstate -- that actually didn't change any row, so the actual record didn't change - UPDATE record to the
ystate -- the actual record changed - UPDATE record to the
zstate usingsp_executeprocedure where the handle_id is the same from the previous update (updating to thezstate) but the values passed via parameters are different
所以在这些操作结束时，我们有:
So at the end of these operations we have:
原始表处于 z 状态，而历史表有类似的东西(日期时间是真实值):
Original table is in z state, and the history table has something like that (the datetimes are the real values):
x状态列，2017-11-01 16:55:31.3358248, 2017-11-01 16:55:31.3358248x状态列，2017-11-01 16:55:31.3358248, 2017-11-01 16:55:31.3358248y状态列，2017-11-01 16:55:31.3358248, 2017-11-01 16:55:41.9296659
xstate columns, 2017-11-01 16:55:31.3358248, 2017-11-01 16:55:31.3358248xstate columns, 2017-11-01 16:55:31.3358248, 2017-11-01 16:55:31.3358248ystate columns, 2017-11-01 16:55:31.3358248, 2017-11-01 16:55:41.9296659
推荐答案
临时表使用事务的开始时间作为使用的时间戳(请参阅MS Docs 中的临时表，在插入、更新和删除部分下).通过在您的事务中进行多次更新，或者在同一个事务中先插入后进行更新，您将获得具有相同开始/结束时间的给定主键值的多条记录.
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.
摘自链接:
UPDATES:在 UPDATE 时，系统存储该行的先前值在历史表中并将 SysEndTime 列的值设置为当前交易的开始时间(在UTC时区)基于在系统时钟上
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
这是一个可以重现该行为的示例.请注意，对于 ProductId = 2，ProductsHistory 表中有 2 个相同的 Miniature Widgets 条目.另请注意，虽然 ProductId = 1 的更新没有更改该值，但它们仍然创建了相同的历史表记录.
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;
这篇关于时间历史表中的重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
