Enforcing Upper/Lower Case in a Sql Server Column

Case sensitivity in Sql Server depends on the collation applicable to the column, which of course is often the same as the database default collation. Unlike Oracle, Sql Server out of the box is case-insensitive (at least in English-speaking Western countries).

This means that you don’t need to consider whether data is upper- or lower-case when you’re searching for it, which tends to make an application more robust. You probably won’t want to change that. On the other hand, if your database stores codes or descriptions used as query filters (not including proper nouns like personal names where the capitalisation is part of the data) there may be an advantage to standardising on upper or lower case. You’ve reduced the need to take account of upper/lower case in client code, where string comparisons may be case-sensitive.

Is there an easy way? Yes. Continue reading

Advertisements

Sql Server Collation Cheat Sheet

This post is not written for an architect or DBA who has to choose a collation and wants an in-depth explanation, but for a developer (perhaps one with an error to fix) who needs a primer on the subject.

Put very simply, collations have to do with languages and with the rules used to compare characters. This is relevant in joins and sorting. You might think that a collation ought to be specified for the query; that in your sql statement you should determine the rules to use for comparing values. You can do that (although you’ll set it at column, not query level), but the columns in the tables have a collation property which is what the query uses by default. This is a convenience as it standardises comparisons and saves you having to specify the rules over and over again. It’s when the two conflict that you have an error which may make it necessary to override one or more default collations. Continue reading