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

我的查询：

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

解决方法

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

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

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

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

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

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

我的建议是在检查约束中避免这种验证.很难确定代码在所有情况下都能正常工作(例如不同的执行计划和隔离级别),另外还有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;

（编辑：安卓应用网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!