Problems with Turkish SQL Collation (classic "I" problem)

Question

Hi,

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.

Thanks Madeleine

Answer 1

Perhaps I don't understand the problem here, but is this not more likely because the database is case sensitive and your query is not? For example, on Sybase I can do the following:

USE master
GO
EXEC sp_server_info 16
GO

Which tells me that my database is case-insensitive:

attribute_id   attribute_name     attribute_value 
          16   IDENTIFIER_CASE    MIXED

Answer 2

If you can change the collation that you're using then try the Invariant locale. But make sure you don't impact other things like customer names and addresses. If a customer is accustomed to having case insensitive searching for their own name, they won't like it if ı and I stop being equivalent, or if i and İ stop being equivalent.

Answer 3

Can you change the database collation to the default: this will leave all your text columns with the Turkish colllation?

Queries will work but data will behave correctly. In theory...

There are some gotchas with temp tables and table variables with varchar columns: you'll have to add COLLATE clauses to these

Answer 4

I realize you don't want to go through all the stored procedures to fix the issue but maybe you'd be OK to use a refactoring tool to solve the problem. I say take a look at SQL Refactor. I didn't use it but looks promising.

Answer 5

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.

Answer 6

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].