Constraint violation on parallel run

Question

I created a function that will serves as primary key for my table

CREATE FUNCTION dbo.NewCustomerPK()
RETURNS VARCHAR (10)
AS
BEGIN
DECLARE @LastCustID VARCHAR(10)
DECLARE @newID      INT
DECLARE @charID     CHAR(10)


SELECT 
    @LastCustID = MAX(CustID)
FROM
    dbo.TestCust

IF (@LastCustID IS NULL)
BEGIN
    SET @LastCustID = 'CUST000001'
END
ELSE
BEGIN

    SET @newID  =   RIGHT(@LastCustID, 6) + 1
    SET @charID =   'CUST' + RIGHT(('0000000' + CONVERT(VARCHAR(6), @newID)), 6)
    SET @LastCustID =   @charID

END

RETURN @LastCustID

END

CREATE TABLE dbo.TestCust
(
   CustID   VARCHAR(10) PRIMARY KEY NOT NULL DEFAULT dbo.NewCustomerPK(),
   Name     VARCHAR(50) 
)

And tried to insert a test data

DECLARE @Counter    INT = 1,
        @Stopper    INT = 500000

WHILE(@Counter <= @Stopper)
BEGIN

INSERT INTO dbo.TestCust(NAME)
VALUES('test'+CONVERT(VARCHAR(6), @Counter))

SET @Counter = @Counter + 1
END

It works fine but when I try a parallel run(Running the loop data insertion in the new window) it cause a Primary Constraint Violation Error

Answer 1

Another way to do this is to use an identity column in combination with a calculated column.

create table dbo.TestCust
(
  ID int identity not null,
  CustID as isnull('CUST'+right('0000000' + convert(varchar(6), ID), 6), ''),
  Name varchar(50),
  constraint PK_TestCust_CustID primary key clustered (CustID) 
);

The isnull around the calculation of CustID is there to make sure the column will never have NULL values and that makes it possible to use CustID as a primary key.

Not sure if it is possible to fix your current design. Perhaps using isolation level serializable when adding new rows would do the trick.

Answer 2

CREATE TRIGGER tr_Group ON TargetTable
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON
    INSERT INTO dbo.targetTable
    SELECT dbo.NewCustomerPK(),<other fields>
    FROM INSERTED
END