Page Comparison

...

For most client organisations this change will be invisible.  If  However, if the organisation has created their own user triggers or user stored procedures that use the tempdb and the Synergetic database uses a different collation sequence to tempdb, they may encounter an error such as the below:

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

and may have to change that user code.

...

The main issue with collation sequences is when you are comparing table string fields across databases.  For example the tempdb database uses the same collation sequence as the model database which uses the server default collation. 

The below example assumes the tempdb database is uses a different collation sequence to the current database.

CREATE TABLE #test
(
  ID        INTEGER,
  Surname   VARCHAR(50),
  Given1    VARCHAR(30)
)
SELECT * FROM #test WHERE Surname = 'rose' –- this will work just fine as we are not comparing with data from a table in the current database
 
-- however if we try
SELECT * FROM #test WHERE Surname IN (SELECT Surname FROM community)
-- this causes a collation conflict as we are comparing string values across databases with different collation sequences
 

...

Note that you only need to specify collation sequence on fields you are doing cross database comparisons on.  The Given1 field does not need it as we are only displaying it.

I also believe it is a good idea It is also good practise to specify the collation with table variables too as they are backed by may utilise the temp database If with larger volumes of data.

Note also if the temp table is created by a SELECT INTO we do not appear to have collation issues…  Only there are no collation issues as it copies the table structure including collation sequences.  The issue occurs only when we CREATE and then INSERT. 

-- In this example we are using system tables therefore we cannot change the collation sequence of the table itself.
-- We need to override the collation sequence on the actual field comparison
SELECT
    sd.DatabaseName
FROM SynDatabases sd
WHERE NOT EXISTS
    (
        SELECT
            *
        FROM master.sys.databases md
        WHERE md.name = sd.DatabaseName COLLATE DATABASE_DEFAULT
    )

...