Problems with Turkish SQL Collation (Turkish "I")

Question

I'm having problems with our MSSQL database set to any of the Turkish Collations. Becuase of the "Turkish I" problem, none of our queries containing an 'i' in them are working correctly. For example, if we have a table called "Unit" with a column "UnitID" defined in that case, the query "select unitid from unit" no longer works because the lower case "i" in "id" differs from the defined capital I in "UnitID". The error message would read "Invalid column name 'unitid'."

I know that this is occurring because in Turkish, the letter i and I are seen as different letters. However, I am not sure as to how to fix this problem? It is not an option to go through all 1900 SPs in the DB and correct the casing of the "i"s.

Any help would be appreciated, even suggestions of other collations that could be used instead of Turkish but would support their character set.

Answer 1

Turns out that the best solution was to in fact refactor all SQL and the code.

In the last few days I've written a refactoring app to fix up all Stored procs, functions, views, tablenames to be consistent and use the correct casing eg:

select unitid from dbo.unit 

would be changed to

select UnitId from dbo.Unit

The app also then goes through the code and replaces any occurrences of the stored proc and its parameters and corrects them to match the case defined in the DB. All datatables in the app are set to invariant locale (thanks to FXCop for pointing out all the datatables..), this prevents the calls from within code having to be case sensitive.

If anyone would like the app or any advice on the process you can contact me on [email protected].

Answer 2

I developed so many systems with Turkish support and this is well known problem as you said.

Best practice to do change your database settings to UTF-8, and that's it. It should solve the all problem.

You might run into problems if you want to support case-sensitivity in (ı-I,i-İ) that can be a problematic to support in SQL Server. If the whole entrance is from Web ensure that is UTF-8 as well.

If you keep your Web UTF-8 input and SQL Server settings as UTF-8 everything should goes smoothly.