Why is the target table of a MERGE statement not allowed to have enabled rules?

Question

We have a process updating the database which uses the following SQL

IF NOT EXISTS (SELECT * FROM Target_Table WHERE Target_Table_ID = ...)
BEGIN
    INSERT ...
END
ELSE
BEGIN
    UPDATE ...
END

I wanted to change this to use a MERGE statement in order to avoid a possible race condition where the IF and then INSERT or UPDATE statements are evaluated separately.

However, as soon as I executed the query I got the error:

The target table 'Target_Table' of the MERGE statement cannot have any enabled rules. Found rule 'TargetTable_RULE'

I understand the documentation states that the table cannot have rules enabled, although I missed it when first reading it, and apart from disabling the rule before performing the MERGE (which I don't see as a workable solution) it seems I'm stuck with the IF NOT EXISTS solution.

What the document does not explain is why the target table is not allowed to have rules. In my case the rule is a simple > 0 check on a value.

Does anyone know why rules are not allowed in this situation and if there is another way to perform the upsert in an atomic way?

EDIT: As Andriy M commented the question Atomic UPSERT in SQL Server 2005 already has a wealth of information on atomic upserts so I'll not duplicate the discussion here. Instead I'd like just to know why the MERGE statement cannot execute on tables with rules defined and is there a work-around?

Answer 1

• Rules are old feature of SQL Server.
• This feature will be removed in a future version of Microsoft SQL Server.. I think, this is the reason why MERGE statement does not supports tables with rules.
• You can replace rules with check constraints.

MERGE example (with rules & with CHECK constraints):

CREATE RULE MyRule
AS 
@Status IN ('Y', 'N');
GO

CREATE TABLE dbo.SalesOrder
(
    SalesOrderID INT PRIMARY KEY
    ,OrderDate DATETIME NOT NULL
    ,IsDeleted CHAR(1) NOT NULL DEFAULT 'N'
);
GO

EXEC sp_bindrule @rulename='MyRule', @objname='dbo.SalesOrder.IsDeleted';
GO

INSERT  dbo.SalesOrder (SalesOrderID, OrderDate)
SELECT  1, '20110101'
UNION ALL
SELECT  2, '20110202'
UNION ALL
SELECT  3, '20110303';
GO

SELECT  *
FROM    dbo.SalesOrder;

PRINT '*****First test*****';
GO

MERGE   dbo.SalesOrder Dst
USING   (VALUES (1,'Y'), (4,'Y')) AS Src(SalesOrderID, IsDeleted) 
ON      Dst.SalesOrderID = Src.SalesOrderID
WHEN    MATCHED THEN UPDATE SET IsDeleted = Src.IsDeleted
WHEN    NOT MATCHED BY TARGET THEN INSERT (SalesOrderID, OrderDate, IsDeleted) VALUES (Src.SalesOrderID, GETDATE(), Src.IsDeleted);
GO

EXEC sp_unbindrule 'dbo.SalesOrder.IsDeleted'; --Disabling `MyRule` for IsDeleted column
ALTER TABLE dbo.SalesOrder --We "replace" the old rule with a new `CHECK` constraint
ADD CONSTRAINT CK_SalesOrder_IsDeleted CHECK( IsDeleted IN ('Y', 'N') );
GO

PRINT '*****Second test*****';
MERGE   dbo.SalesOrder Dst
USING   (VALUES (1,'Y'), (4,'Y')) AS Src(SalesOrderID, IsDeleted) 
ON      Dst.SalesOrderID = Src.SalesOrderID
WHEN    MATCHED THEN UPDATE SET IsDeleted = Src.IsDeleted
WHEN    NOT MATCHED BY TARGET THEN INSERT (SalesOrderID, OrderDate, IsDeleted) VALUES (Src.SalesOrderID, GETDATE(), Src.IsDeleted);
GO

SELECT  *
FROM    dbo.SalesOrder;

DROP TABLE dbo.SalesOrder;
DROP RULE MyRule;

Results:

Rule bound to table column.

(3 row(s) affected)
SalesOrderID OrderDate               IsDeleted
------------ ----------------------- ---------
1            2011-01-01 00:00:00.000 N
2            2011-02-02 00:00:00.000 N
3            2011-03-03 00:00:00.000 N

(3 row(s) affected)

*****First test*****
Msg 358, Level 16, State 1, Line 2
The target table 'Dst' of the MERGE statement cannot have any enabled rules.  Found rule 'MyRule'.
Rule unbound from table column.
*****Second test*****

(2 row(s) affected)
SalesOrderID OrderDate               IsDeleted
------------ ----------------------- ---------
1            2011-01-01 00:00:00.000 Y
2            2011-02-02 00:00:00.000 N
3            2011-03-03 00:00:00.000 N
4            2011-09-20 16:03:56.030 Y

(4 row(s) affected)