davoscollective (8/2/2012)
Thanks for taking the time to explain it to me Tom, much appreciated.
Glad to be of help.
The reason why I asked if the temp tables are in the contained database (during the time in which the temp tables live - not permanently obviously) is because of the BOL info that says temp tables will use the DATABASE_DEFAULT collation. To me that means that the temp tables will not use the contained database's collation, which is opposite to the correct answer in the QotD.
Ah, you have missed something there. The DATABASE_DEFAULT collation for temp tables created in the context of a (partly) contained database is the database default collation for that database, not the database default collation of tempdb.
If you look at the Non-Contained Databases section of you'll see an example 2 (it's a pity example numbering restarts in each new section of that page, so that several examples get the same number) where, in an uncontained database, the collation clause on creation of a temp table is used to get the same behaviour as a contained database:
CREATE TABLE #T2 (T2_txt nvarchar(max) COLLATE DATABASE_DEFAULT);
Tom