I'd be good to note that XACT_STATE = 1 means the transaction is commitable.

I think the correct answer depends upon how the clustered index is created.  The posed question is not explicit in that, only in how the constraint was created.

Could you add a computed persistent field to this table, with a calculation that would be useful to index?  If so, index this computed persistent field and use it for...

EXCEPT is much faster and finds differences in the attributes not just differences in which primary keys exist.

One thing I forgot to note. To find the differences between two...

I found that Varchar(Max) and blob type values had to be specially handled. Our solution was to only compare the first 8000 characters. Also timestamp columns need different...

We had this situation comparing two copies of a history table. We had to compare the natural key less than history_id where the natural key was not unique. ...

Ditto

The question asked if count would ignore values not ignore counting the row. I think the stated answer is wrong.

Max, in effect, handles Nulls as a lower possible value....

My technique is to put all of those queries at the end. Then I can enable the test queries or the final result query as a block of text...

Sneaky. Bravo.

Two queries that do not have "ORDER BY" running at the about the same time might give results in the same order but might not. For example: the order...

Hmm. Selected 1 and 3 and was told wrong.

When answering I could see it was likely the QOD poser made a mistake and what the possible intended answer was. It was also possible it was a trick...

Agreed. But given the code as provided there was a valid set of answers. The second set of inserts would insert two records, both successfully inserting and failing...

My guesses were the same as yours. I've just tried the code on SQL-Server MS 2005 and empirically SSMS agrees with us also. The official answer is not...

I too get 1, 2, 3, 4.