collation and performance

Question

collation and performance

mjschwenger

SSCarpal Tunnel

Points: 4019
More actions
September 5, 2006 at 11:20 pm

#192091

I need to select the best collation possible to display initially a mix of ASCII and Latin1 characters. In about 1 to 2 years, I 'll need to add some asian characters.
What will be the best approach? I need case insensitive database and less storage as possible. So, I might stay for now with char fields versus nchar... Is this the right decision?
I know that there's no UTF8 character page like in Oracle or DB2 - what will be the best equivalent to that?
Thanks a lot for the help.
mj
September 8, 2006 at 8:00 am

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

RichB SSCrazy Eights Points: 9651 More actions · Answer 1

The collation breaks down into several discrete chunks. The Character set, case sensitivity, accent sensitivity, and kana sensitivity. For perfomance I gather a binary collation is quickest.

I would suggest if you are pretty sure you will need unicode in a year or 2, put it in at the start. In 2 years time the effort to convert it will be huge compared to the effort of doing it now (almost nil!). Disks are cheap.

mjschwenger SSCarpal Tunnel Points: 4019 More actions · Answer 2

I have a problem with the binary collation as it sorts all umlauts in the end(after z). The UI application I'm supporting is in Java and has no problem but the crystal reports running use the database sorting and have wrong sort if binary. Our Crystal developler said they have no way to sort on the report side...

BTW, I have a problem with German collation(phonebook) sorting wrong - it put A-umlaut before A, which is not correct. Any idea on how to solve this? The SQL_Latin1_General_CP1_CI_AS also sorts them incorrectly... Am I doing something wrong?

myu server default collation is SQL_Latin1_General_CP1_CI_AS .

Thanks a lot ,mj

RichB SSCrazy Eights Points: 9651 More actions · Answer 3

The crystal application should be able to modify the collation on the fly (depending on how you are doing it). At a last ditch you could create a set of views with specified collations for each relevant field/application.

eg:

create table test(a char(1), b char(1))

go

create view testview

as

select

a collate SQL_Latin1_General_CP1_Cs_AS as a,

b collate Latin1_General_BIN as b

from test

I expect there's a better way, but...

ps don't know if you've looked at SELECT * FROM ::fn_helpcollations()

This might give you some ideas. Also check BOL- it has quite a lot on collations - look up collate in the index.

mjschwenger SSCarpal Tunnel Points: 4019 More actions · Answer 4

Thanks a lot for the example. I'm already doing that but for German(Phonebook)collation this is still wrong although I call it correctly - it is just wrong sorting...

thanks a lot, mj