I'm not trying be meta about metadata ("meta meta"?) or give you nostalgia about the movie Inception, but the way collation name is stored using database-specific collation illustrates precisely why relying on it can be problematic.
We can fix this easily in this case by applying a COLLATE
clause to the first SELECT
(and any collation will do, since we just want readable output; we don't need to worry about changing actual Unicode data or sorting rules as a result):
Now we see that they are stored differently, and it gives a better idea why the query above for type names returned an empty result. You will of course want to use a more specific collation clause if you are using a collation and have objects or entities named using characters from those character sets (otherwise they won't look right if that target collation can't display them the same).
Now, admittedly...
...there aren't many real-world scenarios where this can actually lead to problems. Even on a SQL Server instance installed with a binary collation, variable and parameter data types are case insensitive, so you can do this just fine:
There are other, less obvious places where this can break, though. For example, with the new CLR types like geography
, you need to be case sensitive if the database or instance is case sensitive. Try this:
The latter fails on a case-sensitive database or instance with:
And this is not exclusive to the new CLR types, but rather generic to CLR-related syntax; for example, I can make an old type like char
fail in the same way, if I try to invoke invalid methods:
If the database or instance is case sensitive, you get this error message:
On a case insensitive database or instance, it still fails, but with a different message:
You have no business trying to call methods on that data type, but the point of that example is just to illustrate that the problem is about checking the name of the type, and that happens before any of the other syntax around it is validated.
Also, this can happen if you're not careful about case sensitivity when dealing with alias types (not that you should use these). For example:
Again, this works fine in a case-insensitive database or instance, but if you happen to have a case-sensitive or binary collation in play, you'll see this:
While you can probably enumerate the individual scenarios where this is riskier, the underlying argument I'm trying to make here is one about consistency. I'm a big fan of always spelling out date parts like DAY
, MONTH
, and MINUTE
, instead of using their shorthand, even though there are some cases where ambiguity is not possible. Similarly, here, I feel that striving to always match the exact case that is in sys.types
- even in situations where it can't break - leads to better habits that can avoid the above problems.
Summary
You should always code defensively and match the case sensitivity of all objects, variables, and parameters. You really never know when code you've written (or that has helped someone else) will get implemented in a case-sensitive or binary collation. I've explained above why I am very careful to match what's in sys.types
, but this extends to other scenarios, too.
For example, try this in a database using a case-sensitive or binary collation, where you have to be accurate with object names (not just for your own objects, but for system objects, too):
Result:
And even worse, the problem extends to parameter and variable names on a case-sensitive or binary instance (and probably if you've somehow managed to change master
to be that way, though I'll confess I haven't been motivated enough to confirm). On such an instance, this code:
...fails with this error message:
This can lead to quite a surprising troubleshooting session if you develop your solution on a case-insensitive instance and then deploy to one that isn't. One thing I've started doing is developing on a BIN2 collation - yes, if I'm sloppy, it may be painful to deal with those error messages during development, but that's way better, IMHO, than hitting them during or after deployment, or when your app or database gets migrated to a different server. And IntelliSense should negate any sloppiness there anyway...
Comments