检查约束不适用于超过250条记录的批量插入 [英] Check constraint does not work on bulk insert for more than 250 records
问题描述
我的查询:
INSERT into PriceListRows (PriceListChapterId,[No])
SELECT TOP 250 100943 ,N'2'
FROM #AnyTable
此查询工作正常，并根据需要引发以下异常:
This query works fine and the following exception raises as desired:
INSERT语句与CHECK约束冲突 "CK_PriceListRows_RowNo_Is_Not_Unqiue_In_PriceList".冲突 发生在数据库"TadkarWeb"的表"dbo.PriceListRows"中.
The INSERT statement conflicted with the CHECK constraint "CK_PriceListRows_RowNo_Is_Not_Unqiue_In_PriceList". The conflict occurred in database "TadkarWeb", table "dbo.PriceListRows".
但是将SELECT TOP 250更改为SELECT TOP 251(是的！仅将250更改为251！)，查询成功运行，没有任何检查约束异常！
but with changing SELECT TOP 250 to SELECT TOP 251 (yes! just changing 250 to 251!) the query runs successfully without any check constrain exception!
为什么这种奇怪的行为?
Why this odd behavior?
注意:
-
我的检查约束是用于检查某种唯一性的函数.查询约4张表.
My check constraint is a function which checks some sort of uniqueness. It queries about 4 table.
我同时检查了SQL Server 2012 SP2和SQL Server 2014 SP1
I checked on both SQL Server 2012 SP2 and SQL Server 2014 SP1
** 编辑1 **
检查约束功能:
ALTER FUNCTION [dbo].[CheckPriceListRows_UniqueNo] (
@rowNo nvarchar(50),
@rowId int,
@priceListChapterId int,
@projectId int)
RETURNS bit
AS
BEGIN
IF EXISTS (SELECT 1
FROM RowInfsView
WHERE PriceListId = (SELECT PriceListId
FROM ChapterInfoView
WHERE Id = @priceListChapterId)
AND (@rowID IS NULL OR Id <> @rowId)
AND No = @rowNo
AND (@projectId IS NULL OR
(ProjectId IS NULL OR ProjectId = @projectId)))
RETURN 0 -- Error
--It is ok!
RETURN 1
END
** 编辑2 ** 检查约束代码(SQL Server 2012产生的代码):
** EDIT 2 ** Check constraint code (what SQL Server 2012 produces):
ALTER TABLE [dbo].[PriceListRows] WITH NOCHECK ADD CONSTRAINT [CK_PriceListRows_RowNo_Is_Not_Unqiue_In_PriceList] CHECK (([dbo].[tfn_CheckPriceListRows_UniqueNo]([No],[Id],[PriceListChapterId],[ProjectId])=(1)))
GO
ALTER TABLE [dbo].[PriceListRows] CHECK CONSTRAINT [CK_PriceListRows_RowNo_Is_Not_Unqiue_In_PriceList]
GO
** 编辑3 **
执行计划在这里: https://www.dropbox .com/s/as2r92xr14cfq5i/execution％20plans.zip?dl = 0
** 编辑4 **
RowInfsView的定义是:
** EDIT 4 **
RowInfsView definition is :
SELECT dbo.PriceListRows.Id, dbo.PriceListRows.No, dbo.PriceListRows.Title, dbo.PriceListRows.UnitCode, dbo.PriceListRows.UnitPrice, dbo.PriceListRows.RowStateCode, dbo.PriceListRows.PriceListChapterId,
dbo.PriceListChapters.Title AS PriceListChapterTitle, dbo.PriceListChapters.No AS PriceListChapterNo, dbo.PriceListChapters.PriceListCategoryId, dbo.PriceListCategories.No AS PriceListCategoryNo,
dbo.PriceListCategories.Title AS PriceListCategoryTitle, dbo.PriceListCategories.PriceListClassId, dbo.PriceListClasses.No AS PriceListClassNo, dbo.PriceListClasses.Title AS PriceListClassTitle,
dbo.PriceListClasses.PriceListId, dbo.PriceLists.Title AS PriceListTitle, dbo.PriceLists.Year, dbo.PriceListRows.ProjectId, dbo.PriceListRows.IsTemplate
FROM dbo.PriceListRows INNER JOIN
dbo.PriceListChapters ON dbo.PriceListRows.PriceListChapterId = dbo.PriceListChapters.Id INNER JOIN
dbo.PriceListCategories ON dbo.PriceListChapters.PriceListCategoryId = dbo.PriceListCategories.Id INNER JOIN
dbo.PriceListClasses ON dbo.PriceListCategories.PriceListClassId = dbo.PriceListClasses.Id INNER JOIN
dbo.PriceLists ON dbo.PriceListClasses.PriceListId = dbo.PriceLists.Id
推荐答案
原因是您的执行计划正在使用(逐索引)更新计划.
The explanation is that your execution plan is using a "wide" (index by index) update plan.
在计划的步骤1中，将行插入到聚集索引中.然后在步骤2中为每一行验证检查约束.
The rows are inserted into the clustered index at step 1 in the plan. And the check constraints are validated for each row at step 2.
在将所有行都插入聚簇索引之前，不会将任何行插入到非聚簇索引中.
No rows are inserted into the non clustered indexes until all rows have been inserted into the clustered index.
这是因为有两个阻止运算符在聚集索引插入/约束检查和非聚集索引插入之间.急切线轴(第3步)和排序(第4步).在消耗完所有输入行之前，这两个都不会产生输出行.
This is because there are two blocking operators between the clustered index insert / constraints checking and the non clustered index inserts. The eager spool (step 3) and the sort (step 4). Both of these produce no output rows until they have consumed all input rows.
标量UDF的计划使用非聚簇索引来尝试查找匹配的行.
The plan for the scalar UDF uses the non clustered index to try and find matching rows.
此时，检查约束运行，尚未向非聚簇索引中插入任何行，因此此检查为空.
At the point the check constraint runs no rows have yet been inserted into the non clustered index so this check comes up empty.
当您插入较少的行时，您将获得一个窄"(逐行)的更新计划，并避免了该问题.
When you insert fewer rows you get a "narrow" (row by row) update plan and avoid the problem.
我的建议是避免在检查约束中进行这种验证.很难确定代码在所有情况下(例如不同的执行计划和隔离级别)都能正常运行，此外，它们
My advice is to avoid this kind of validation in check constraints. It is difficult to be sure that the code will work correctly in all circumstances (such as different execution plans and isolation levels) and additionally they block parellelism in queries against the table. Try to do it declaratively (a unique constraint that needs to join onto other tables can often be achieved with an indexed view).
一个简化的复制是
CREATE FUNCTION dbo.F(@Z INT)
RETURNS BIT
AS
BEGIN
RETURN CASE WHEN EXISTS (SELECT * FROM dbo.T1 WHERE Z = @Z) THEN 0 ELSE 1 END
END
GO
CREATE TABLE dbo.T1
(
ID INT IDENTITY PRIMARY KEY,
X INT,
Y CHAR(8000) DEFAULT '',
Z INT,
CHECK (dbo.F(Z) = 1),
CONSTRAINT IX_X UNIQUE (X, ID),
CONSTRAINT IX_Z UNIQUE (Z, ID)
)
--Fails with check constraint error
INSERT INTO dbo.T1 (Z)
SELECT TOP (10) 1 FROM master..spt_values;
/*I get a wide update plan for TOP (2000) but this may not be reliable
across instances so using trace flag 8790 to get a wide plan. */
INSERT INTO dbo.T1 (Z)
SELECT TOP (10) 2 FROM master..spt_values
OPTION (QUERYTRACEON 8790);
GO
/*Confirm only the second insert succceed (Z=2)*/
SELECT * FROM dbo.T1;
DROP TABLE dbo.T1;
DROP FUNCTION dbo.F;
这篇关于检查约束不适用于超过250条记录的批量插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
