I'll second everyone else's comments: fascinating stuff, Paul. As I was saying, my "problem" is resolved, but I was really interested to understand why SQL was behaving that...

You might check with Paul Randal's company, SQL Skills. If they can't help you, I'm sure they could recommend someone.

Shaira (6/21/2013)

---

I have been handed a database that someone built hundreds of tables and for the last 5+ years have been dumping massive amounts of data into – and unfortunately...

spaghettidba (6/21/2013)

---

Also, the plans don't look very similar.

The DELETE plan is way different. It's a parallel plan, while the MERGE plan is serial. There's a hash match and... it's...

Hmmm ... if I understand you correctly, this problem severely limits the usefulness of MERGE.

The WHEN NOT MATCHED clause is required for INSERTs and DELETEs, but getting all...

Oh, I am perfectly content with your help. I was hoping one of these people might subscribe to Google alerts. 🙂 I am pretty sure Adam Machanic does,...

Nothing? :w00t: I was sure that one of those brilliant SQL veterans like Gail Shaw, Jeff Moden, Grant Fritchey, Steve Jones or Adam Machanic would have some insight...

Let me see if I understand the problem correctly. You have an overnight ETL process which takes 9 million + records from Table A and then Bulk Inserts them...

ScottPletcher (2/18/2013)

---

All triggers are implicitly part of a transaction, with at least the SQL statement that caused the trigger to fire included in the transaction.

In implicit transaction mode, where each...

OK. My problem is that any constraint violation within a TRY CATCH block within a trigger makes the transaction uncommittable (whether explicit or implicit).

At that point, no matter what...

These are pretty cool!

Also, it is clear that you have been doing this way too long. 😎

Yes! That would have been great. I don't think you even need the parentheses. That answer would be nigh unbeatable. (Too bad it doesn't work. :hehe:)

Well, let's be charitable and assume that he is suitably embarrassed by his behavior.

The only other answer I could come up with was

SELECT DB_ID()

which will return '1' if you are in the Master database (which is usually the default).

This, of course, is...

True, and the competition on SQLAuthority shows a SELECT statement in the sample, but I imagine that something different might be accepted, if it were creative. At least it...