Collation considerations to create robust queries
|Is your code "This" robust?
If you are dedicated to database development or you provide support to a lot of user databases, then you must be aware of the collation of the database at the moment to write your queries to avoid execution failures at deploy stage.
On this post, I will show you some of the most common errors related to collation and how to avoid them to have solid T-SQL queries.
1. Your DEV machine (all defaults)
If you just install SQL Server in your dev machine and use all the defaults, including collation, it is very likely you won't have any issues of this kind, so let us take the following TSQL as an example:
It will execute without any issues:
So now, let us change the Collation for the School database:
|Collations are different now
After this change, the fun begins...
2. The column prefix '<something>' does not match with a table name or alias name used in the query
Executing the same tsql code, now with a different collation will raise an error:
To solve this, verify that the case on the alias, object and properties are the same, and change it to be the same across all your query:
3. The multi-part identifier "<something>" could not be bound
Our second error, if we add a WHERE clause to the query, again with a different case, an error will show:
To solve this, as the previous error, verify that the case on the alias, object and properties are the same, and change it to be the same across all your query:
4. Cannot resolve the collation conflict between "<Collation 1>" and "<Collation 2>" in the equal to operation
For our third error, we will use a different query that use tables for different databases (and maybe different collations):
Even when we have applied the case rules we explained before, we have another kind of error when databases use different collation:
To solve this, you must apply the same collation to both sides of the join, using COLLATE DATABASE_DEFAULT as you can see here:
One important thing, you only have to do this change where you use multi-database joins, if your joins are in the same database, this is not necessary.
Now you have the knowledge to build more robust T-SQL queries that can be deployed to a wide set of different environments and configurations and you have more probabilities they will not fail.