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.
I have tried to reproduce the issue. Created a fresh database and needed objects, ran the dbcc and… no error.
The script used for creating a new database also inserts and deletes a row, just to have pages allocated to objects. DBCC IND are about the same, on the “original” database and on the fresh copy. Also, based on my experience, DBCC DBINFO returned about the same information for both databases.
The good part is that after recreating the objects, inside the same database, dbcc checkconstraint worked fine.
May find a zip with all details here dbcc-checkconstraints-error-446: a database backup (only needed objects) where the error occurs and the script used to reproduce the issue.
Originating server on which issue was first identified:
Microsoft SQL Server 2012 – 11.0.2100.60 (X64) / Express Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
Development server on which issue was tested to be reproduced:
Microsoft SQL Server 2012 – 11.0.5582.0 (X64) / Developer Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
Issue was reported to Microsoft support site.