Page Comparison

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
 

CREATE TABLE #test
(
  ID        INTEGER,
  Surname   VARCHAR(50) COLLATE DATABASE_DEFAULT,
  Given1    VARCHAR(30)
)

SELECT * FROM #test WHERE Surname IN (SELECT Surname FROM community) -- now it works