SQL Server collation case-sensitivity

Question

I need to determine the collation of a database in SQL 2012, and extract from it the case-sensitivity. How can I do it? After I know the collasion case-sensitivity I need to use it in C#. Is there a mapping between SQL Server collasion case-sensitivity and C# ?

Answer 1

Your question is fundamentally flawed as it reduces all characteristics of a collation to only case sensitivity. Writing code on this assumption will result in incorrect code, and I have only two words two add: Turkish I. To be correct, you must use the same collation in your C# code as the collation in the database, not simply use ToLowerCase() or ToUpperCase().

I ask how to map it to C#

Given that the list of collations supported by SQL Server is known and fixed (for each version) I would recommend hard-coding the mapping from SQL returned collation name to a C# culture name. You'll only have to build the list once, is fairly easy to build. You'll need to somehow handle future versions of SQL Server that may ship with new collations.

Be warned (again...) that the problem you're trying to solve is far from trivial. SQL Server persists data using a given collation rules and this makes changes to collations impossible (if collations says A sorts after B and data is saved so, changing the collation to say A sorts before B would result in data corruption when reading the persisted data, same for equality comparison, think foreign keys, unique and check constraints etc etc). However the platform (Windows) does change collation settings every now and then, usually to fix bugs in existing collations. Your C# culture will use the current platform collation, which may differ from what SQL captured for same collation in, say, 2008. There are many dragons under that bridge...

Answer 2

Is there a mapping between SQL Server collasion case-sensitivity and C# ?

Yes, given that:

• Collation Insneitive is coded into the collation nmes (CI, check the list at http://msdn.microsoft.com/en-us/library/ms144250(v=sql.105).aspx)
• You can get the collation of a database or field (yes, can be changed on field level) using SQL. CHeck for example:

http://technet.microsoft.com/en-us/library/ms174396.aspx

for the property with the default collation.

So, there is a mapping. Just no easy property as you likely want.

There is no correlation though - SQL Server mappings are quite elaborate. Name comparison is the best bet.