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

Question

My query :

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

This query works fine and the following exception raises as desired:

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".

but with changing SELECT TOP 250 to SELECT TOP 251 (yes! just changing 250 to 251!) the query runs successfully without any check constrain exception!

Why this odd behavior?

NOTES :

1. My check constraint is a function which checks some sort of uniqueness. It queries about 4 table.

2. I checked on both SQL Server 2012 SP2 and SQL Server 2014 SP1

** EDIT 1 **

Check constraint 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 constraint code (what SQL Server 2012 produces):

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

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

** EDIT 4 ** RowInfsView definition is :

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

Egor

You're inserting the same 2 values (100943 and 2) 250 times? Sounds like you should get the exception already in 2nd row – Protanopia

@jamez exactly! it is for test. I should get error but I don't know why I don't get it. (indeed I get it for 250 records insertion but not for 251 ones!) – Egor

yes strange. I edited the post. – Egor

Do you have triggers on the table that would affect RowInfsView? Somehow, I think the problem is self-referentiality in the check constraint. – Antiworld

@MahmoudMoravej . . . Martin Smith referenced this blog in an answer to a similar question (sqlblog.com/blogs/alexander_kuznetsov/archive/2009/06/25/…). I suspect that some sort of foreign key constraint or unique constraint might be better suited for what you want to do. – Antiworld

There is no Trigger with PriceListRows table. Maybe. Note : RowsInfosView is a view on PriceListRows which provide more info about the row FKs – Egor

You have select top 1 without order by, so it can pick any random row from ChapterInfoView to compare with RowInfsView, so without understanding your data, I'd say that's the source of your problems. – Protanopia

@JameZ, Indeed I don't need it. It is a select query on PK. I put it just for test. I will remove it from the question to prevent misleading – Egor

@GordonLinoff I read the link but in his example the problem is with row by row check which is good for me! – Egor

You want a unique constraint for this. – Cheerful

@Cheerful what do you mean? – Egor

Have you looked at the query plan for the two inserts? – Metalloid

This is not a check constraint code. This is code of a fuction. Show also code of a constraint itself... – Entrails

@ShannonSeverance I attached the execution plan to the end of post. But I couldn't find any strange behavior in it. – Egor

@GiorgiNakeuri I added to the end of post – Egor

In table RowInfsView, are there any records with [NO] under 251 ? Or with a matching ProjectID ? Perhaps the issue here is data in the checking tables. – Scirrhous

Please post the definition of the RowInfsView view. – Henninger

@Amir, [No] is varchar and there is no any record with these conditions. note that the query tries to insert 251 same records. Except the first record, the others should not be inserted. – Egor

Answer 1

The explanation is that your execution plan is using a "wide" (index by index) update plan.

The rows are inserted into the clustered index at step 1 in the plan. And the check constraints are validated for each row at step 2.

No rows are inserted into the non clustered indexes until all rows have been inserted into the clustered index.

This is because there are two blocking operators between the clustered index insert / constraints checking and the non clustered index inserts. The eager spool (step 3) and the sort (step 4). Both of these produce no output rows until they have consumed all input rows.

The plan for the scalar UDF uses the non clustered index to try and find matching rows.

At the point the check constraint runs no rows have yet been inserted into the non clustered index so this check comes up 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 validation in check constraints. It is difficult to be sure that the code will work correctly in all circumstances (such as different execution plans and isolation levels) and additionally they block parellelism in queries against the table. Try to do it declaratively (a unique constraint that needs to join onto other tables can often be achieved with an indexed view).

---

A simplified repro is

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; 

Gowk

Answer 2

It's possible that you are encountering an incorrect optimization of a query, but without having the data in all the involved tables, we cannot reproduce the bug.

However, for this kind of checks, I recommend using triggers instead of check constraints based on functions. In a trigger, you could use a SELECT statement to debug why it's not working as expected. 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

This way, you can see what is being checked and correct your views and/or existing data.

Henninger