时间表上的合并失败，尝试将非NULL-able列的值设置为NULL [英] Merge on temporal table fails with Attempting to set a non-NULL-able column's value to NULL
问题描述
工作流程：
- 在数据仓库中创建包含默认历史记录表的时态表
- 在历史表上创建索引
- 每小时对时态表执行合并语句
- 请注意，有时错误会产生： 尝试设置非NULL-将列的值设置为NULL
- 从历史记录表中删除索引
- 使用相同的数据集执行合并，并且它将成功
- 重新创建索引在历史表上
- 随机错误将再次生成，重复步骤5到7
开4月29日我创建了一个名为[dbo]。[FactPersonnelScheduleDetails_Current]的新时态表，其中包含一个名为[History]的默认历史表。[FactPersonnelScheduleDetails_Current]。
On April 29th I created a new temporal table named [dbo].[FactPersonnelScheduleDetails_Current] with a default history table named [History].[FactPersonnelScheduleDetails_Current].
此表位于执行的数据仓库中每小时几次合并声明。
This table is in a data warehouse that performs several merge statements on an hourly basis.
自4天前实施此表以来，两次合并交易因以下错误而失败："尝试将非NULL-able列的值设置为NULL"
Twice now since implementing this table 4 days ago, the merge transaction has failed with the following error: "Attempting to set a non-NULL-able column's value to NULL"
History表具有一个用户定义的索引。只要删除该索引，我就可以成功完成合并。
The History table has one user-defined index. As soon as I drop that index, I can the successfully complete the merge.
当我在历史记录表上创建了用户定义的索引时，为什么MERGE失败的任何建议，但在我删除用户定义的索引？
Any suggestions why the MERGE is failing when I have a user defined index created on the history table, but works when I remove the user defined index?
CREATE TABLE [dbo].[FactPersonnelScheduleDetails_Current] (
[ScheduleCellKey] INT NOT NULL,
[ScheduleCellID] INT NOT NULL,
[DBID] SMALLINT NOT NULL,
[ScheduleDetailsID] INT NOT NULL,
[RecordNumber] SMALLINT NULL,
[WorkDate] DATE NULL,
[WorkDateKey] INT NULL,
[WeekStartDate] DATE NULL,
[DayOfTheWeek] VARCHAR(50) NULL,
[WorkDayNumber] TINYINT NULL,
[MasterScheduleCellKey] INT NULL,
[WorkingScheduleMatchesMasterScheduleFlag] VARCHAR(3) NULL,
[DateWorkingScheduleNoLongerMatchesMaster] DATETIME2(7) NULL,
[IsHoliday] VARCHAR(3) NULL,
[JobPostDetailID] INT NULL,
[JobPostDetailKey] INT NULL,
[JobNumber] VARCHAR(10) NOT NULL,
[JobKey] INT NOT NULL,
[JobTierKey] INT NOT NULL,
[EmployeeNumber] INT NULL,
[EmployeeKey] INT NULL,
[CategoriesDetailID] INT NULL,
[BillCategory] VARCHAR(50) NULL,
[HoursTypeID] SMALLINT NULL,
[HoursTypeDescription] VARCHAR(50) NULL,
[PaycheckDescriptionId] SMALLINT NULL,
[PaycheckDescription] VARCHAR(50) NULL,
[RecordTypeID] TINYINT NULL,
[Hours] DECIMAL(19,4) NULL,
[InTime] TIME(3) NULL,
[InTimeString] VARCHAR(8) NULL,
[InDateTime] DATETIME NULL,
[OutTime] TIME(3) NULL,
[OutTimeString] VARCHAR(8) NULL,
[OutDateTime] DATETIME NULL,
[LunchHours] DECIMAL(19,4) NULL,
[NextDay] VARCHAR(3) NOT NULL,
[PayRate] MONEY NULL,
[SpecialPayRate] VARCHAR(3) NOT NULL,
[ForceOTToThisJob] VARCHAR(3) NOT NULL,
[OvertimeHours] DECIMAL(19,4) NULL,
[DoubletimeHours] DECIMAL(19,4) NULL,
[InvoiceNumber] INT NULL,
[InvoiceDescription] VARCHAR(100) NULL,
[TierDescription] VARCHAR(100) NULL,
[BillRate] MONEY NULL,
[SpecialBillRate] VARCHAR(3) NOT NULL,
[BillingPayRate] MONEY NULL,
[NonBillableTypeID] SMALLINT NULL,
[NonBillableTypeDescription] VARCHAR(50) NULL,
[LastChangedBy] VARCHAR(20) NULL,
[LastChangedDate] DATETIME NULL,
[DaysBetweenWorkDateAndModifiedDate] INT NULL,
[UpdatedToTKHours] VARCHAR(3) NULL,
[SpecialInvoiceDescription] VARCHAR(3) NULL,
[SpecialTierDescription] VARCHAR(3) NULL,
[InvoiceDetailID] INT NULL,
[TTMStatusID] INT NULL,
[IsConfirmed] VARCHAR(3) NULL,
[DateConfirmed] DATETIME NULL,
[IsPublished] VARCHAR(3) NULL,
[AcceptedTypeForPSTT] TINYINT NULL,
[TeamTimeAcceptedTypeDescription] VARCHAR(50) NULL,
[UserName] VARCHAR(20) NULL,
[DateChanged] datetime NULL,
[Notes] VARCHAR(255) NULL,
[SystemNotes] VARCHAR(8000) NULL,
[ETLDateChanged] DATETIME2(7) NOT NULL DEFAULT (sysdatetime()),
[RecordInsertTimestamp] DATETIME2(7) NOT NULL DEFAULT (sysdatetime()),
[RecordUpdateTimestamp] DATETIME2(7) NOT NULL DEFAULT (sysdatetime()),
SysStartTime datetime2(0) GENERATED ALWAYS AS ROW START CONSTRAINT DF_SysStart DEFAULT SYSUTCDATETIME(),
SysEndTime datetime2(0) GENERATED ALWAYS AS ROW END CONSTRAINT DF_SysEnd DEFAULT CONVERT(datetime2 (0), '9999-12-31 23:59:59'),
PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime),
CONSTRAINT PK_FactPersonnelScheduleDetails_Current PRIMARY KEY NONCLUSTERED (ScheduleCellKey))
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = History.FactPersonnelScheduleDetails_Current));
CREATE CLUSTERED INDEX [IX01_FactPersonnelScheduleDetails_Current]
ON [dbo].[FactPersonnelScheduleDetails_Current] (WorkDateKey,DBID);
CREATE NONCLUSTERED INDEX [IX02_FactPersonnelScheduleDetails_Current]
ON [dbo].[FactPersonnelScheduleDetails_Current] (JobKey)
INCLUDE (ScheduleCellKey,DBID,WeekStartDate,JobTierKey,EmployeeKey,Hours,WorkingScheduleMatchesMasterScheduleFlag);
CREATE NONCLUSTERED INDEX [IX03_FactPersonnelScheduleDetails_Current]
ON [dbo].[FactPersonnelScheduleDetails_Current] (RecordUpdateTimestamp,MasterScheduleCellKey)
INCLUDE (ScheduleCellKey,WeekStartDate,DayOfTheWeek,JobPostDetailKey,JobKey,EmployeeKey,CategoriesDetailID,Hours,InTime,OutTime,NextDay,PayRate,SpecialPayRate,ForceOTToThisJob,BillRate,SpecialBillRate,NonBillableTypeID,ETLDateChanged,RecordNumber,WorkingScheduleMatchesMasterScheduleFlag);
CREATE NONCLUSTERED INDEX [IX04_FactPersonnelScheduleDetails_Current]
ON [dbo].[FactPersonnelScheduleDetails_Current] (WorkingScheduleMatchesMasterScheduleFlag)
INCLUDE (ScheduleCellKey,DBID,WeekStartDate,JobKey,JobTierKey,EmployeeKey,DateWorkingScheduleNoLongerMatchesMaster);
CREATE NONCLUSTERED INDEX [IX01_FactPersonnelScheduleDetails_Current]
ON [History].[FactPersonnelScheduleDetails_Current] (WorkDateKey,DBID)
INCLUDE (ScheduleCellKey,WeekStartDate,JobKey,JobTierKey,EmployeeKey,Hours,SysStartTime,SysEndTime);
推荐答案
嗯，这听起来很不幸！
Well, that sounds unfortunate!
出于好奇，我发现WorkDateKey可以为空。
Just out of curiosity, I see that WorkDateKey is nullable.
你知道这是什么吗？当数据失败时，数据的值是什么？ 只是想知道该复合聚集键值中的空值是否可能是其中的一部分 - 尽管我看不出它是否重要。
Do you have any idea what the values of the data are, when it fails like that? Just wondering if null values in that compound clustered key value might be a part of it - though I can't see that it should matter.
Josh
这篇关于时间表上的合并失败，尝试将非NULL-able列的值设置为NULL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
