...
SQL code across databases with different collation sequences
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 a different collation sequence to the current database.
| Code Block | ||||
|---|---|---|---|---|
| ||||
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
|
The resolution is to specify the collation on EACH column that will be compared with a field in the source database:-
| Code Block | ||||
|---|---|---|---|---|
| ||||
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
|
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 to specify the collation with table variables too as they are backed by the temp database
If the temp table is created by a SELECT INTO we do not appear to have collation issues… Only when we CREATE and then INSERT.
| Code Block | ||||
|---|---|---|---|---|
| ||||
-- in this example we are using system tables therefore we cannot change the collation seq 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
) |