Validation constraint does not work on bulk insert for more than 250 records

Question

My request:

INSERT into PriceListRows (PriceListChapterId,[No]) SELECT TOP 250 100943 ,N'2' FROM #AnyTable 

This request works fine, and if necessary the following exception occurs:

The INSERT statement was contrary to the CHECK constraint "CK_PriceListRows_RowNo_Is_Not_Unqiue_In_PriceList". The conflict occurred in the database "TadkarWeb", the table "dbo.PriceListRows".

but with the change of SELECT TOP 250 to SELECT TOP 251 (yes! just changing 250 to 251!) the query is successful without exception checking the restriction!

Why is this weird behavior?

NOTES:

• My limitation of validation is a function that validates some kind of uniqueness. It queries about 4 tables.

• I checked both SQL Server 2012 SP2 and SQL Server 2014 SP1

** EDIT 1 **

Check restriction function:

 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 

** EDIT 2 ** Check the restriction code (which produces 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 

** EDIT 3 **

Implementation plans are here: https://www.dropbox.com/s/as2r92xr14cfq5i/execution%20plans.zip?dl=0

** EDIT 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

The explanation is that the “wide” (index by index) update plan is used in the execution plan.

Rows are inserted into the clustered index in step 1 of the plan. And control constraints are checked for each row in step 2.

No rows are placed in indexes without clustering until all rows are inserted into the clustered index.

This is because there are two blocking statements between checking the insertion / constraints of clustered indexes and nonclustered insertions of indexes. Waiting coil (step 3) and sorting (step 4). Both of them do not produce output lines until they consume all input lines.

The scalar UDF plan uses a non-clustered index to find matching rows.

At the moment when the check constraint does not work, the rows have not yet been inserted into the nonclustered index, so this check seems 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 check in control constraints. It is difficult to be sure that the code will work correctly under any circumstances (for example, different execution plans and isolation levels), and in addition, they are block parellelism in table queries. Try to do this declaratively (a unique constraint that needs to be attached to other tables can often be achieved with an indexed view).

---

Simplified Registry

 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; 

Answer 2

You may have encountered incorrect query optimization, but without data in all the tables involved, we cannot reproduce the error.

However, for these types of checks, I recommend using triggers instead of function-based control constraints. In a trigger, you can use the SELECT statement to debug why it is not working properly. For example:

 CREATE TRIGGER trg_PriceListRows_CheckUnicity ON PriceListRows FOR INSERT, UPDATE AS IF @@ROWCOUNT>0 BEGIN /* SELECT * FROM inserted i INNER JOIN RowInfsView r ON r.PriceListId = ( SELECT c.PriceListId FROM ChapterInfoView c WHERE c.Id = i.priceListChapterId ) AND r.Id <> i.Id AND r.No = i.No AND (r.ProjectId=i.ProjectId OR r.ProjectId IS NULL AND i.ProjectId IS NULL) */ IF EXISTS ( SELECT * FROM inserted i WHERE EXISTS ( SELECT * FROM RowInfsView r WHERE r.PriceListId = ( SELECT c.PriceListId FROM ChapterInfoView c WHERE c.Id = i.priceListChapterId ) AND r.Id <> i.Id AND r.No = i.No AND (r.ProjectId=i.ProjectId OR r.ProjectId IS NULL AND i.ProjectId IS NULL) ) ) BEGIN RAISERROR ('Duplicate rows!',16,1) ROLLBACK RETURN END END 

In this way, you can see that your views and / or existing data are being verified and corrected.