On a customer database, on an empty table, DBCC CHECKCONSTRAINTS failed with the below message:
Msg 446, Level 16, State 11, Line 1
Cannot resolve collation conflict between “Latin1_General_BIN” and “Latin1_General_CI_AI” in CASE operator for DISTINCT operation.
Msg 2509, Level 16, State 2, Line 1
DBCC CHECKCONSTRAINTS failed due to an internal query error. Please run DBCC CHECKDATABASE to ensure your data consistency.
According to MSDN, indeed a temp table is created on tempdb. So I suspected (and still am) a coding bug: when database collation is different from the server one, in case of a join on char types columns, the collation should be specified.