It strikes me as very odd that changing the containment type of a database results in different behavior in code: in this case the $action field.

But this is just a personal opinion (however a few colleagues do agree)

I did a kind of an extension to this test-case:

- Database [test] in CONTAINMENT TYPE=NONE

- Create a stored procedure with the MERGE-statement above.

- Try to change the CONTAINMENT TYPE=PARTIAL

That resulted in following error, which is basically the same as in the first test-case.

Msg 468, Level 16, State 9, Procedure sp_TEST_CASE, Line 19

Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "Latin1_General_100_CI_AS_KS_WS_SC" in the equal to operation.

Msg 12813, Level 16, State 2, Line 2

Errors were encountered in the procedure 'dbo.sp_TEST_CASE' during compilation of the object. Either the containment option of the database 'test' was changed, or this object was present in model db and the user tried to create a new contained database.

Msg 12836, Level 16, State 1, Line 2

ALTER DATABASE statement failed. The containment option of the database 'test' could not be altered because compilation errors were encountered during validation of SQL modules. See previous errors.

Msg 5069, Level 16, State 1, Line 2

ALTER DATABASE statement failed.

Apparently there is a view where one can query for possible problems when changing to a CONTAINED database: SYS.DM_DB_UNCONTAINED_ENTITIES

But a query on this view does not show this particular stored procedure.

All in all: I guess I'm forced to use the COLLATE-clause ...