sql-server - 检查约束不适用于超过 250 条记录的批量插入

Question

我的查询:

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

为什么会有这种奇怪的行为？

注释:

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

2. 我检查了 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

Answer 1

解释是您的执行计划正在使用 "wide" (逐个索引)更新计划。

这些行将在计划的第 1 步插入到聚集索引中。并且在步骤 2 中验证每一行的检查约束。

在将所有行插入聚集索引之前，不会将任何行插入非聚集索引。

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

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

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

当您插入较少的行时，您会得到一个“狭窄的”(逐行)更新计划并避免出现问题。

我的建议是避免在检查约束中进行这种验证。很难确定代码在所有情况下都能正确工作(例如不同的执行计划和隔离级别)，此外它们 block parellelism在针对表的查询中。尝试以声明方式执行此操作(需要连接到其他表的唯一约束通常可以通过索引 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;