SQL Server 2000 Best Sort Order to choose
-
Hello everybody,
Currently I’m trying to find the best sort order for my database. What are my needs? I must have an international DB. The database is design in English. The main problem is, that client comes from all over the world. We must stored a lot of different languages, western Europe (French, English, deutch,…) but also Chinese characters, Arabic Characters,… Do you know what I mean? And we want a full non sensitive DB (insensitive for case, accent, kanatype, width).
At this moment I’m thinking about a sort order which have Code Page = 850 with non sensitive option. But when I install it I have this : “Latin1-General, case-insensitive, accent-insensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 57 on Code Page 850 for non-Unicode Data”
I want insert some Unicode Data. Must I chose a Binary Sort order for that or is it exist a sort order for Unicode data or the meaning of “Code Page 850 for non-Unicode Data” doesn’t mean that we cannot insert some Unicode data.
To give you more details, the client send characters from their configuration that they have different regional settings. I don’t want to have some char(25) into my db because my client insert a Chinese characters for example.
Before writing this document, I’ve already read a lot of Microsoft doc and a lot of article on the web site.
If you want more details to understand what I need, no problem, just ask it.
Jerome.
-
These are the sort orders for code page 850
quote:
40 bin_cp850 Binary order41 dictionary_850 Dictionary order, case-sensitive
42 nocase_850 Dictionary order, case-insensitive
43 nocasepref_850 Dictionary order, case-insensitive, uppercase preference
44 noaccents_850 Dictionary order, case-insensitive, accent-insensitive
49 caseless_34 Strict compatibility with version 1.x case-insensitive databases
55 alt_dictionary Alternate dictionary order, case-sensitive
56 alt_nocasepref Alternate dictionary order, case-insensitive, uppercase preference
57 alt_noaccents Alternate dictionary order, case-insensitive, accent-insensitive
58 scand_nocasepref Scandinavian dictionary order, case-insensitive, uppercase preference
59 scand_dictionary Scandinavian dictionary order, case-sensitive
60 scand_nocase Scandinavian dictionary order, case-insensitive
61 alt_nocase Alternate dictionary order, case-insensitive
Yours 57 is Alternate dictionary order, case-insensitive, accent-insensitive. Which based on your statement "we want a full non sensitive DB (insensitive for case, accent, kanatype, width)." should do exactly that. Keep in mind thou for a field to support unicode the column must be of a datatype that supports it. char does not nchar however does. If this does not answer your question can you please express more details.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
-
Yeah, that’s exactly what I want to know. And effectively, I know that I must use nvarchar, nchar (Unicode data type) to insert some Unicode values. Just 2 other questions:
- Why they put in the description of sort order 57: “SQL Server Sort Order 57 on Code Page 850 for non-Unicode Data ” ? What does-it mean? Does-it means that the sort order was created for non Unicode data? Because that’s I want use Unicode data.
- What’s the difference between sort order 57 and 44? They just said that for 57, it’s an alternate? Is-it better to use 44 instead of 57?
-
After a bit of research on this, I don't believe the sort order applies to the unicode data but only the non-unicode stuff when the supported characters are based on the code page. But instead of making a mistake get a lot of detail form the horses mouth here http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlsg/html/msdn_interntl.asp
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Edited by - antares686 on 08/29/2002 04:39:51 AM
-
Jerome,
If you're using Unicode, you don't have to worry about switching code pages when switching languages. You do have to think plenty about sort orders.
Are you sure you want your database to be insensitive for case, accent, kanatype, and width? French in particular has some special considerations for accent. If you don't get the proper sort, your software will not be useful to the French.
COLLATE can be used on the column level in SQL Server. One solution is to have a separate column for each of the languages you intend to support.
COLLATE can be included on SELECT queries, but it has no effect on output, at least on SQL Server 2000 without service packs. (I gotta get those installed one of these days.) A workaround to get a collation on the fly is to use a table variable with COLLATE:
DECLARE @SortStrings TABLE (
SimpleString NVARCHAR(4000) COLLATE Lithuanian_CS_AS_KS_WS)
INSERT INTO @SortStrings SELECT Txt FROM MultiTxt$
SELECT SimpleString FROM @SortStrings ORDER BY SimpleString
What design you go with depends largely on how many languages you have to work with, and if you can find one or two collations that will work with them all. The more languages and more collations you get, the more complex the architecture gets.
Hope that clears things up a little.
Steve
Steve Miller -
By the way, the best page I know covering the topic is http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/intlfeaturesinsqlserver2000.asp
Steve
Steve Miller
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply