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;
