sql-server – 对于超过250条记录的批量插入,检查约束不起作用

栏目: 数据库 · 发布时间: 5年前

内容简介:翻译自:https://stackoverflow.com/questions/33054748/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

此查询工作正常,并根据需要引发以下异常:

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!),查询成功运行,没有任何检查约束异常!

为何这种奇怪的行为?

注意:

>我的检查约束是一种检查某种唯一性的函数.它查询约4个表.

>我检查了SQL Server 2012 SP2和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生成的内容):

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定义是:

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
解释是您的执行计划使用 “wide”

(索引索引)更新计划.

在计划的步骤1中将行插入聚簇索引中.并且在步骤2对每行验证检查约束.

在将所有行都插入到聚簇索引中之前,不会将任何行插入到非聚簇索引中.

这是因为聚簇索引插入/约束检查和非聚簇索引插入之间有两个 blocking operators .急切的线轴(步骤3)和排序(步骤4).在消耗了所有输入行之前,这两个行都不会产生输出行.

sql-server – 对于超过250条记录的批量插入,检查约束不起作用

标量UDF的计划使用非聚集索引来尝试查找匹配的行.

sql-server – 对于超过250条记录的批量插入,检查约束不起作用

在检查约束运行时,尚未将任何行插入到非聚集索引中,因此此检查为空.

当您插入较少的行时,您将获得“窄”(逐行)更新计划并避免此问题.

我的建议是在检查约束中避免这种验证.很难确定代码在所有情况下都能正常工作(例如不同的执行计划和隔离级别),另外还有 block parellelism 对表的查询.尝试以声明方式执行此操作(通常可以使用索引视图实现需要连接到其他表的唯一约束).

一个简化的复制品是

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;

翻译自:https://stackoverflow.com/questions/33054748/check-constraint-does-not-work-on-bulk-insert-for-more-than-250-records


以上就是本文的全部内容,希望本文的内容对大家的学习或者工作能带来一定的帮助,也希望大家多多支持 码农网

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

算法的乐趣

算法的乐趣

王晓华 / 人民邮电出版社 / 2015-4 / 79.00元

算法之大,大到可以囊括宇宙万物的运行规律;算法之小,小到寥寥数行代码即可展现一个神奇的功能。算法的应用和乐趣在生活中无处不在: 历法和二十四节气计算使用的是霍纳法则和求解一元高次方程的牛顿迭代法; 音频播放器跳动的实时频谱背后是离散傅立叶变换算法; DOS时代著名的PCX图像文件格式使用的是简单有效的RLE压缩算法; RSA加密算法的光环之下是朴实的欧几里德算法、蒙哥马利算......一起来看看 《算法的乐趣》 这本书的介绍吧!

在线进制转换器
在线进制转换器

各进制数互转换器

Base64 编码/解码
Base64 编码/解码

Base64 编码/解码

UNIX 时间戳转换
UNIX 时间戳转换

UNIX 时间戳转换