T-SQL Collation Conflict
A collation in SQL Server decides how character strings are stored in databases. A conflict can arise when you use temporary tables in your database application and the collation of the database does not match that of the temporary table.
Other People Are Reading
-
Collation
-
A collation is a specification for the manner in which characters are physically stored, that is, the bit-patterns that represent each character. It also specifies the sorting and ordering rules for character strings.
Specifying Collations
-
Collation can be specified at the database, table and also the column level, that is, for a field in a table. Each column in a table can have a different collation.
-
Collation Conflict
-
If you use temporary tables as a means of temporary workspace in T-SQL procedures, you have to pay attention to collations. If the default collation of the temporary table does not match that of the database or its tables, SQL Server gives an error message indicating a collation conflict.
Resolving Collation Conflicts
-
Whenever you need a temporary table in T-SQL code, either create the table with the default database collation or use the "COLLATE" command when inserting into, updating and referencing the table. The default collation for the current database can be specified by the keyword "database_default" in the "COLLATE" command. As an alternative, use a separate SQL Server instance for each distinct collation.
Finding Available Collations
-
Execute this T-SQL query for the names of collations supported by the SQL Server instance:
select * from ::fn_helpcollations()
-
References
Resources
- Photo Credit business report image by Christopher Hall from Fotolia.com
