Friday, June 03, 2005
SQL Collation
SQL Server 6.0 / 7.0 doesn't allow to change collation (code page and sort order) on the fly. To change the collation of an existing database, we need to rebuild the master database or reinstall SQL Server. Whereas SQL Server 2000 supports Collation changes in much degree. We can have different collations within database.
Some of the frequent issues we face when we have different collation settings are:
A) When you create a temporary table, the columns get the default collation of the server. When you try to join that temporary table to a user table on a column that has a different collation, you get an error message about SQL not being able to resolve a collation conflict.
For this always use the COLLATE DATABASE_DEFAULT clause for each column when creating a temporary table.
B) When you run a query that joins a user table to a system table in the master database, again with different collations - produces the same error.
For this always use that clause next to the column from the system table in your ON clause.
Some of the frequent issues we face when we have different collation settings are:
A) When you create a temporary table, the columns get the default collation of the server. When you try to join that temporary table to a user table on a column that has a different collation, you get an error message about SQL not being able to resolve a collation conflict.
For this always use the COLLATE DATABASE_DEFAULT clause for each column when creating a temporary table.
B) When you run a query that joins a user table to a system table in the master database, again with different collations - produces the same error.
For this always use that clause next to the column from the system table in your ON clause.
# posted by Satheesh Chandra @ 12:11 AM 
