Lots of things can prevent parallel execution plans, but inline UDFs are not one of them. Inline UDFs are essentially macros - they are at least as fast as CTEs. As such, the optimizer can produce exactly the same parallel execution plans whether if our queries are wrapped in inline UDFs or copied and pasted into the main query.

As usual, I will provide a repro script.

Prerequisites

Let us set up a table with 2 million rows - that should be enough to qualify for a parallel plan:


CREATE TABLE dbo.Numbers(n INT NOT NULL PRIMARY KEY);
GO

INSERT INTO dbo.Numbers
    ( n )
        VALUES  ( 1 );
GO
DECLARE @i INT;
    SET @i=0;
WHILE @i<21  
    BEGIN
    INSERT INTO dbo.Numbers
        ( n )
        SELECT n + POWER(2, @i)
        FROM dbo.Numbers;
    SET @i = @i + 1;
    END;  
GO

SELECT  n ,
        'asdfasdfas' AS Filler
INTO    dbo.ParallelTest
FROM    dbo.Numbers ;
GO

ALTER TABLE dbo.ParallelTest ADD CONSTRAINT PK_ParallelTest PRIMARY KEY(n) ;
GO  

Running a query with parallel execution plan

The following query runs with parallel execution plan (number of executions is 12):

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO
SELECT COUNT(*) FROM dbo.ParallelTest
WHERE Filler LIKE '%nosuchstring%'

Let us wrap it in an inline UDF:

CREATE FUNCTION dbo.MatchCountInParallelTestByPattern ( @Pattern VARCHAR(10) )
RETURNS TABLE
AS RETURN
  ( SELECT  COUNT(*) AS MatchCount
    FROM    dbo.ParallelTest
    WHERE   Filler LIKE @Pattern
  ) ;

The following query invokes the inline UDF, yet still runs with exactly the same parallel execution plan (number of executions is the same: 12):


SELECT  MatchCount
FROM    dbo.MatchCountInParallelTestByPattern('%nosuchstring%') ;
 

Conclusion

At the time of this writing, reusing code wrapped in inline UDFs does not prevent parallel execution plans, at least on the following version:

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)   Apr  2 2010 15:48:46   Copyright (c) Microsoft Corporation  Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) 

 

After timeouts we need to make sure that active transactions, if any, are rolled back. All timeout handling must be done on the client. This post provides the implementation and unit tests.

Implementation

The following class extends SqlCommand and rolls back active transactions after timeouts:


    public static class SqlCommandExtentions
    {
        public static void ExecuteNonQueryWithErrorHandling(this SqlCommand command)
        {
            try
            {
                command.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                HandleSqlException(command, e);
                throw;
            }
        }

        public static SqlDataReader ExecuteReaderWithErrorHandling(this SqlCommand command)
        {
            try
            {
                return command.ExecuteReader();
            }
            catch (SqlException e)
            {
                HandleSqlException(command, e);
                throw;
            }
        }

        private static void HandleSqlException(SqlCommand command, SqlException e)
        {
            const int timeoutCode = -2;
            if (e.Number != timeoutCode) return;
            var rollbackCommand = command.Connection.CreateCommand();
            rollbackCommand.CommandText = "IF @@TRANCOUNT > 0 BEGIN ; ROLLBACK ;  END ;";
            rollbackCommand.CommandType = CommandType.Text;
            rollbackCommand.CommandTimeout = 0;
            rollbackCommand.ExecuteNonQueryWithErrorHandling();
        }
    }   

Testing

We need to unit test the two methods. For each one, we need to test the following four cases:

• Command succeeds 
• Command blows up with some other error, which must not be caught by our handling
• Command times out, active transaction
• Command times out, no active transaction

The following tests cover these four cases for ExecuteReader. The other four tests are very similar, so there is no need to post them.

 
        [Test]
        public void ExecuteReaderWithErrorHandling_WorksOnSuccess()
        {
            using (var dr = ExecuteReaderWithErrorHandling(sqlConn, "SELECT 1 AS n"))
            {
                Assert.IsTrue(dr.Read());
                Assert.AreEqual(1, dr.GetInt32(0));
            }
        }

        [Test]
        public void ExecuteReaderWithErrorHandling_HandlesTimeout_ActiveTransaction()
        {
            try
            {
                ExecuteReaderWithErrorHandling(sqlConn, "EXEC dbo.IWillTimeOut", timeoutInSeconds: 1);
                Assert.Fail("Must throw exception");
            }
            catch(SqlException e)
            {
                Assert.AreEqual(-2, e.Number);
                VerifyNoOpenTransaction(sqlConn);
            }
        }

        [Test]
        public void ExecuteReaderWithErrorHandling_HandlesTimeout_NoActiveTransaction()
        {
            try
            {
                ExecuteReaderWithErrorHandling(sqlConn, "EXEC dbo.IWillTimeOutWithoutTransaction", timeoutInSeconds: 1);
                Assert.Fail("Must throw exception");
            }
            catch (SqlException e)
            {
                Assert.AreEqual(-2, e.Number);
                VerifyNoOpenTransaction(sqlConn);
            }
        }

        [Test]
        public void ExecuteReaderWithErrorHandling_ThrowsOtherExceptions()
        {
            try
            {
                ExecuteReaderWithErrorHandling(sqlConn, "EXEC dbo.IThrowError8134");
                Assert.Fail("Must throw exception");
            }
            catch (SqlException e)
            {
                Assert.AreEqual(8134, e.Number);
                VerifyNoOpenTransaction(sqlConn);
            }
        }


These unit tests utilize a few database objects:

 
CREATE FUNCTION dbo.IWillCompleteInNSeconds ( @NumSeconds INT )
RETURNS INT
AS
    BEGIN ;
      DECLARE @StartTime DATETIME ,
        @Toggle INT ;
      SELECT  @StartTime = GETDATE() ,
              @Toggle = 1 ;
      WHILE DATEDIFF(SECOND, @StartTime, GETDATE()) < @NumSeconds
        BEGIN ;
          SET @Toggle = 1 - @Toggle ;
        END ;
      RETURN 0 ;
    END ;
GO

CREATE PROCEDURE dbo.IWillTimeOut
AS
  BEGIN ;
    BEGIN TRANSACTION ;
    SELECT  dbo.IWillCompleteInNSeconds( 5 ) AS SomeNumber ;
    COMMIT ;
  END ;
GO

CREATE PROCEDURE dbo.IWillTimeOutWithoutTransaction
AS
  BEGIN ;
    WAITFOR DELAY '00:00:05' ;
  END ;
GO

CREATE PROCEDURE dbo.IThrowError8134
AS
BEGIN ;
    SET XACT_ABORT ON ;
    DECLARE @i INT ;
   SELECT @i = 1/0 ;
   SELECT 1 AS n;
END ;  

Investing in proper database design is a very efficient way to cut maintenance costs. If we expect a system to last, we need to make sure it has a good solid foundation - high quality database design.

Surely we can and sometimes do cut corners and save on database design to get things done faster. Unfortunately, such cutting corners frequently comes back and bites us: we may end up spending a lot of time solving issues caused by poor design. 

So, solid understanding of relational database design is absolutely essential for database developers, including Agile folks like myself. Let me put it stronger: we Agile developers especially need solid understanding of relational database design, because we are quite likely to cut corners and spend just enough effort to pass unit tests.

This is why I think the book by "Pro SQL Server 2008 Relational Database Design and Implementation" by Louis Davidson, Kevin Kline, and others is a must read.

The following is a chapter by chapter synopsis

Introduction to Database Concepts

This chapter introduces several crucial theoretical concepts, and each concept is demonstrated with a short crisp example. The chapter must be read before proceeding any further.

The Language of Data Modeling

This chapter describes various aspects of traditional waterfall methodology. As such, it can safely be skipped by Agile developers.

Conceptual Data Modeling

This chapter deals with business rules and requirements, and provides very good practical examples. As the previous chapter, all the examples are described in the context of waterfall methodology. However, this should not turn us off. Agile developers need to identify business rules and requirements, and implement them via database design, as mush as anyone else.

This chapter is a must read for everyone, even if some of us do not use Erwin or Visio.

The Normalization Process

This chapter builds up on the concepts introduced in the previous one. As such, we shall get better results if we read about Conceptual Data Modeling first.

As before, all theoretical concepts are illustrated with succinct practical examples. 

In my opinion, this chapter is most important in this book - it gives a great description of a complex technique that is not always described well. If it was the last chapter in this book, the book would still be a highly useful one.

Of course, there are other books describing normalization. This one really stands out because it provides clear in-depth explanations with good concrete practical examples.

The rest of the book

The next chapters of the book are describing various steps needed to implement the complete RDBMS solution, including developing T-SQL, securing access, indexing and so on. All these chapters are useful and practical as well.

Although these chapters are very useful and a pleasure to read, I am not going into more detail here. The reason: many other books describe similar things as well. In my opinion, it is the first chapters that really make this book unique.

Conclusion

We may be using very different methodologies to develop systems build on top of RDBMS. However, eventually professionally developed systems that are meant to last should have proper database design.

The book provides a practical description of what  is proper database design. As such, it will be really useful for all developers who want to develop robust systems using RDBMS.

Enjoy!

 

When a SELECT is used to populate variables from a subquery, it fails to change them if the subquery returns nothing - and that can lead to subtle bugs. We shall use OUTER APPLY to eliminate this problem.

Prerequisites

All we need is the following mock function that imitates a subquery:

CREATE FUNCTION dbo.BoxById ( @BoxId INT )
RETURNS TABLE
AS RETURN
  ( SELECT  CAST(1 AS INT) AS [Length] ,
            CAST(2 AS INT) AS [Width] ,
            CAST(3 AS INT) AS [Height]
    WHERE   @BoxId = 1
  ) ;

Let us assume that this subquery is slow and we do not want to run it more than absolutely necessary.

Short recap on SET vs. SELECT

The following approach uses SET to populate variables:

DECLARE @Length INT ,
  @Width INT ,
  @BoxId INT ;

SELECT  @Length = 0 ,
        @Width = 0  ;

SET @BoxId = 1 ;

SET @Length = ( SELECT  [Length]
                FROM    dbo.BoxById(@BoxId)
              ) ;
SET @Width = ( SELECT [Width]
               FROM   dbo.BoxById(@BoxId)
             ) ;

This script will always change the values of variables, even when the subquery returns nothing, which is good. However, it runs a possibly slow/expensive subquery twice. Also it duplicates code. We want to avoid both these disadvantages.

The following script uses SELECT, it has no duplication, and it runs the subquery only once:

DECLARE @Length INT ,
  @Width INT ,
  @BoxId INT ;
  
SELECT  @Length = 0 ,
        @Width = 0  ;

SET @BoxId = 1 ;

SELECT  @Length = [Length] ,
        @Width = [Width]
FROM    dbo.BoxById(@BoxId) ;

SELECT  @Length AS [Length] ,
        @Width AS [Width] ;
 

This code looks cleaner and performs better, but it has the following problem: if the subquery returns nothing, the variables are not changed. You can change @BoxId from 1 to any other number, rerun the script, and see for yourself.

When the variables are not changed by an assignment, that can lead to subtle bugs, 

like this one.

To avoid this vulnerability, we can use OUTER APPLY, as follows:

DECLARE @Length INT ,
  @Width INT ,
  @BoxId INT ;
  
SELECT  @Length = 0 ,
        @Width = 0  ;

SET @BoxId = 2 ;

SELECT  @Length = [Length] ,
        @Width = [Width]
FROM    (SELECT 1 AS PlaceHolder) AS AlwaysOneRow
OUTER APPLY dbo.BoxById(@BoxId) ;

SELECT  @Length AS [Length] ,
        @Width AS [Width] ;

As a result, we still have no duplication, run an expensive subquery only once, and also always change the variables, making the code less vulnerable to bugs.

Beginning a transaction only when @@TRANCOUNT=0 might not improve performance at all. At least, I did not notice any difference whatsoever. No matter if I use this pattern:

BEGIN TRAN ;
-- (snip)
COMMIT ;

or a more complex one:

DECLARE @trancount INT ;
SET @trancount = @@TRANCOUNT ;
IF @trancount = 0 BEGIN ;
   BEGIN TRAN ;
END ;
--(snip)
IF @trancount = 0 BEGIN ;
   COMMIT ;
END ;
 

the performance stays the same: I was not able to notice any difference. Here are my benchmarks.

Prerequisites

All we need is the following two tables:

CREATE TABLE dbo.Toggle1
    (
      id INT NOT NULL
             PRIMARY KEY ,
      i INT NOT NULL
    ) ;
GO
INSERT  INTO dbo.Toggle1
        ( id, i )
VALUES  ( 1, 0 ) ;
GO
CREATE TABLE dbo.Toggle2
    (
      id INT NOT NULL
             PRIMARY KEY ,
      i INT NOT NULL
    ) ;
GO
INSERT  INTO dbo.Toggle2
        ( id, i )
VALUES  ( 1, 0 ) ;
 

Simple benchmarking

First, let us run a very simple case, without nested stored procedure calls:

CREATE PROCEDURE dbo.ToggleWithNestedTrans
   @NumIterations INT
AS
    BEGIN;
        SET NOCOUNT ON ;
        SET XACT_ABORT ON ;
        DECLARE @i INT ;
        SET @i = 0 ;
        WHILE @i < @NumIterations
            BEGIN ;
                BEGIN TRAN ;
                UPDATE  dbo.Toggle1
                SET     i = 1 - i
                WHERE   id = 1 ;
                BEGIN TRAN ;
                UPDATE  dbo.Toggle2
                SET     i = 1 - i
                WHERE   id = 1 ;
                COMMIT ;
                COMMIT ;
                SET @i = @i + 1 ;
            END ;
    END ;
GO

CREATE PROCEDURE dbo.ToggleAvoidingNestedTrans
   @NumIterations INT
AS
    BEGIN;
        SET NOCOUNT ON ;
        SET XACT_ABORT ON ;
        DECLARE @i INT ;
        SET @i = 0 ;
        WHILE @i < @NumIterations
            BEGIN ;
                BEGIN TRAN ;
                UPDATE  dbo.Toggle1
                SET     i = 1 - i
                WHERE   id = 1 ;
                DECLARE @trancount INT ;
                SET @trancount = @@TRANCOUNT ;
                IF @trancount = 0 BEGIN ;
                   BEGIN TRAN ;
               END ;
                UPDATE  dbo.Toggle2
                SET     i = 1 - i
                WHERE   id = 1 ;
                IF @trancount = 0 BEGIN ;
                   COMMIT ;
               END ;
                COMMIT ;
                SET @i = @i + 1 ;
            END ;
    END ;
GO


CREATE PROCEDURE dbo.ToggleNoNestedTrans
   @NumIterations INT
AS
    BEGIN;
        SET NOCOUNT ON ;
        SET XACT_ABORT ON ;
        DECLARE @i INT ;
        SET @i = 0 ;
        WHILE @i < @NumIterations
            BEGIN ;
                BEGIN TRAN ;
                UPDATE  dbo.Toggle1
                   SET     i = 1 - i
                   WHERE   id = 1 ;
                UPDATE  dbo.Toggle2
                   SET     i = 1 - i
                   WHERE   id = 1 ;
                COMMIT ;
                SET @i = @i + 1 ;
            END ;
    END ;
GO
EXEC dbo.ToggleWithNestedTrans @NumIterations = 100000;
GO
EXEC dbo.ToggleAvoidingNestedTrans @NumIterations = 100000;
GO

EXEC dbo.ToggleWithNestedTrans @NumIterations = 100000;
GO
EXEC dbo.ToggleAvoidingNestedTrans @NumIterations = 100000;
GO

EXEC dbo.ToggleWithNestedTrans @NumIterations = 100000;
GO
EXEC dbo.ToggleAvoidingNestedTrans @NumIterations = 100000;

GO

EXEC dbo.ToggleNoNestedTrans @NumIterations = 100000;
GO
EXEC dbo.ToggleNoNestedTrans @NumIterations = 100000;
GO
EXEC dbo.ToggleNoNestedTrans @NumIterations = 100000;
GO  
 

In fact, real execution costs are the same for both approaches, and not different from then run without nested transactions at all.

Benchmarking with nested stored procedure calls

Let us benchmark another, possibly more realistic scenario, when the avoiding nested transaction logic is inside another, nested stored procedure. Here is the benchmarking script:

CREATE PROCEDURE dbo.Toggle2WithNestedTran
AS
    BEGIN;
        SET NOCOUNT ON ;
        SET XACT_ABORT ON ;
        BEGIN TRAN ;
        UPDATE  dbo.Toggle2
        SET     i = 1 - i
        WHERE   id = 1 ;
        COMMIT ;
    END ;
GO

ALTER PROCEDURE dbo.ToggleWithNestedTrans
   @NumIterations INT
AS
    BEGIN;
        SET NOCOUNT ON ;
        SET XACT_ABORT ON ;
        DECLARE @i INT ;
        SET @i = 0 ;
        WHILE @i < @NumIterations
            BEGIN ;
                BEGIN TRAN ;
                UPDATE  dbo.Toggle1
                SET     i = 1 - i
                WHERE   id = 1 ;
                EXEC dbo.Toggle2WithNestedTran ;
                COMMIT ;
                SET @i = @i + 1 ;
            END ;
    END ;
GO

CREATE PROCEDURE dbo.Toggle2AvoidingNestedTrans
AS
    BEGIN;
        SET NOCOUNT ON ;
        SET XACT_ABORT ON ;
        DECLARE @trancount INT ;
        SET @trancount = @@TRANCOUNT ;
        IF @trancount = 0 BEGIN ;
           BEGIN TRAN ;
       END ;
        UPDATE  dbo.Toggle2
        SET     i = 1 - i
        WHERE   id = 1 ;
        IF @trancount = 0 BEGIN ;
           COMMIT ;
       END ;
    END ;
GO

ALTER PROCEDURE dbo.ToggleAvoidingNestedTrans
   @NumIterations INT
AS
    BEGIN;
        SET NOCOUNT ON ;
        SET XACT_ABORT ON ;
        DECLARE @i INT ;
        SET @i = 0 ;
        WHILE @i < @NumIterations
            BEGIN ;
                BEGIN TRAN ;
                UPDATE  dbo.Toggle1
                SET     i = 1 - i
                WHERE   id = 1 ;
                EXEC dbo.Toggle2AvoidingNestedTrans ;
                COMMIT ;
                SET @i = @i + 1 ;
            END ;
    END ;
GO

Again, I was not able to notice any difference.

Conclusion

Apparently the following logic does not affect performance at all:

DECLARE @trancount INT ;
SET @trancount = @@TRANCOUNT ;
IF @TRANCOUNT = 0 BEGIN ;
   BEGIN TRAN ;
END ;
--(snip)
IF @TRANCOUNT = 0 BEGIN ;
   COMMIT ;
END ;

As such, this pattern seems to be just an unnecessary complication.

What do you think - are you getting the same results when you benchmark on your system?

Are you using this pattern for other than performance? What are those?

Wrapping related changes in a transaction is a good way to ensure data integrity. Besides, in some cases it just runs noticeably faster, using less CPU. As usual, I am posting repro scripts, which you can run, tweak, and see for yourself.

Environment

I've run my scripts on 2008 R2 Dev Edition. Snapshot isolation is enabled, READ_COMMITTED_SNAPSHOT is not enabled.

Test data

 After applying Occum's razor, all we need is two tables with one row in each:

 CREATE TABLE dbo.Toggle1
    (
      id INT NOT NULL
             PRIMARY KEY ,
      i INT NOT NULL
    ) ;
GO
INSERT  INTO dbo.Toggle1
        ( id, i )
VALUES  ( 1, 0 ) ;
GO
CREATE TABLE dbo.Toggle2
    (
      id INT NOT NULL
             PRIMARY KEY ,
      i INT NOT NULL
    ) ;
GO
INSERT  INTO dbo.Toggle2
        ( id, i )
VALUES  ( 1, 0 ) ;
 

Benchmarking

The following two stored procedures run the same modifications 10K times. The first one does not use an explicit transaction:

 CREATE PROCEDURE dbo.Toggle50kTimesX3WithoutTransaction
AS
    BEGIN;
        SET NOCOUNT ON ;
        SET XACT_ABORT OFF ;
        DECLARE @i INT ;
        SET @i = 0 ;
        WHILE @i < 50000
            BEGIN ;
                UPDATE  dbo.Toggle1
                SET     i = 1 - i
                WHERE   id = 1 ;
                UPDATE  dbo.Toggle2
                SET     i = 1 - i
                WHERE   id = 1 ;
                SET @i = @i + 1 ;
            END ;
    END ;

The second procedure does use an explicit transaction:

CREATE PROCEDURE dbo.Toggle50kTimesX3WithXactAbortOff
AS
    BEGIN;
        SET NOCOUNT ON ;
        SET XACT_ABORT OFF ;
        DECLARE @i INT ;
        SET @i = 0 ;
        WHILE @i < 50000
            BEGIN ;
                BEGIN TRAN ;
                UPDATE  dbo.Toggle1
                SET     i = 1 - i
                WHERE   id = 1 ;
                UPDATE  dbo.Toggle2
                SET     i = 1 - i
                WHERE   id = 1 ;
                COMMIT ;
                SET @i = @i + 1 ;
            END ;
    END ;

Let's run the following script a few times:

EXEC dbo.Toggle50kTimesX3WithoutTransaction ;
GO
EXEC dbo.Toggle50kTimesX3WithXactAbortOff ;
 

Typical CPU and duration is as follows:

 dbo.Toggle50kTimesX3WithoutTransaction

CPU: 1900-2340 Duration:5900

dbo.Toggle50kTimesX3WithXactAbortOff

CPU: 1500-1700 Duration: 3200-3300

Conclusion

As we have seen, wrapping related changes in as transaction may improve performance.By related, I mean changes that should all succeed or all fail if we are ensuring atomicity.

However, we typically need to keep transactions short, so we should not bundle unrelated changes in transactions - that may hurt concurrency.

Also I am being very careful and not making any blanket statements here. I did not research this effect with due diligence. I noticed the effect in one case and came up with a repro that works in one particular case on one server, this is all.

I would really appreciate if you run the repro, tweak it as you see needed, and post the results here. TIA!

In general, we should not assume that larger transactions always mean better performance - the opposite is true in many cases. We should always benchmark the specific case at hand and decide on case by case basis.

Edit: I have simplified the scripts a little bit, and that changed the numbers a little bit.

Our OLTPish mixed load system has not had a single deadlock since last April, and we just love it.

I would not make any blanket statements, but I think in our case being deadlock-free just makes a lot of practical sense. Of course, in other cases in might not be worth it.

To accomplish deadlock-free state, we have taken the following steps:

• Snapshot isolation for all readers
• If concurrent updates are not frequent, we just acquire some exclusive lock before updating. As long as collisions are not likely, this does not slow us down much. We stress test how it works.
  
• If concurrent updates become frequent, we solve the problem outside the database. Our application servers accumulate incoming changes and save them in bulk. Of course, these bulk saves still have to acquire some exclusive lock before updating. We stress test how it works.

I still have to see how it works out long term, but short term we save considerable effort on not troubleshooting deadlocks, and not having to implement retry logic. So far I think that being deadlock-free is worth it, but only if we can avoid high concurrency, when lots of modifications touch a single row each. If we cannot avoid high concurrency, then it might be easier to just let it deadlock from time to time.

What do you think? Are you doing anything similar?

 

In SQL 2008 R2, MERGE does not implement foreign keys properly. I will show both false negatives (valid rows are rejected) and false positives - orphan rows that are allowed to save.

 

False Negatives

 

The following tables implement a very common type/subtype pattern:

CREATE TABLE dbo.Vehicles(
    ID INT NOT NULL,
    [Type] VARCHAR(5) NOT NULL,
    CONSTRAINT Vehicles_PK PRIMARY KEY(ID),
    CONSTRAINT Vehicles_UNQ_ID_Type UNIQUE(ID, [Type]),
    CONSTRAINT Vehicles_CHK_ValidTypes CHECK([Type] IN ('Car', 'Truck'))
);
GO

CREATE TABLE dbo.Cars(ID INT NOT NULL,
    [Type] AS CAST('Car' AS VARCHAR(5)) PERSISTED,
    OtherData VARCHAR(10) NULL,
    CONSTRAINT Cars_PK PRIMARY KEY(ID),
    CONSTRAINT Cars_FK_Vehicles FOREIGN KEY(ID, [Type])
        REFERENCES dbo.Vehicles(ID, [Type])
);
GO
-- adding parent rows
INSERT INTO dbo.Vehicles(ID, [Type])
VALUES(1, 'Car'),
(2, 'Truck');

The following INSERT succeeds:

INSERT INTO dbo.Cars(ID, OtherData)
VALUES(1, 'Some Data');

DELETE FROM dbo.Cars;

MERGE should be able to add the same row, but it fails:

  MERGE dbo.Cars AS TargetTable
    USING
        ( SELECT    1 AS ID ,
                    'Some Data' AS OtherData
        ) AS SourceData
    ON  SourceData.ID = TargetTable.ID
    WHEN NOT MATCHED
        THEN INSERT (ID, OtherData)
        VALUES(SourceData.ID, SourceData.OtherData);

Msg 547, LEVEL 16, State 0, Line 1
The MERGE statement conflicted WITH the FOREIGN KEY CONSTRAINT "Cars_FK_Vehicles" The conflict occurred IN DATABASE "Test" TABLE "dbo.Vehicles"
The statement has been terminated.


 

False Positives

 

Martin Smith has posted this repro on stackoverflow, in response to my scenario described above. This script successfully adds an orphan row into dbo.Cars table:

ALTER TABLE dbo.Vehicles
   DROP CONSTRAINT Vehicles_CHK_ValidTypes ;
GO

INSERT INTO dbo.Vehicles(ID, [Type]) VALUES (3, '');
GO

MERGE dbo.Cars AS TargetTable
    USING
        ( SELECT    3 AS ID ,
                    'Some Data' AS OtherData
        ) AS SourceData
    ON  SourceData.ID = TargetTable.ID
    WHEN NOT MATCHED
        THEN INSERT (ID, OtherData)
        VALUES(SourceData.ID, SourceData.OtherData);
GO

-- the FK constraint is enabled and trusted
SELECT is_disabled,is_not_trusted
   FROM sys.foreign_keys
   WHERE name = 'Cars_FK_Vehicles';
-- yet we have an orphan row
SELECT * FROM dbo.Cars
SELECT * FROM dbo.Vehicles
GO

is_disabled is_not_trusted
----------- --------------
0           0

(1 row(s) affected)

ID          TYPE  OtherData
----------- ----- ----------
3           Car   SOME Data

(1 row(s) affected)

ID          TYPE
----------- -----
1           Car
2           Truck
3          


Conclusion 

In Ch 7 of my book "Defensive Database Programming", I wrote the following statement.

"As long as a constraint is trusted, then we know for sure that all our data complies with the rules that it dictates."

At the time I was writing that, I believed that was true. Unfortunately, as we have just seen, I was wrong - we can bypass trusted foreign key constraints in SQL 2008 R2.

P.S.

I have just learned that there is another scenario to bypass a foreign key. Google up this: "Parameterized DELETE and MERGE Allow Foreign Key Constraint Violations".

Another very important thing that I have just learned that this Connect item did not get urgent priority. Quoting directly from Connect: "Since this is not a common scenario or use case i.e., doing DDL changes and DML in the same transaction we don't see this as a big issue".