All character sets are offered with a binary sort order at a minimum, which blindly sorts all data based only on the arithmetic value of the code assigned to represent each letter (the “binary” code) in the character set.
Binary sort order works well for the first 128 characters of each character set (ASCII English) and for Asian languages.When a character set supports more than one language (for example, Group 1 or Unicode) the binary sort order most likely give incorrect results, and you should select another sort order.
Dictionary order, case-sensitive, accent-sensitive – sorts uppercase and lowercase letters separately. Dictionary order recognizes the various accented forms of a letter and sorts them after the associated unaccented letter.
Dictionary order, case-insensitive, accent-sensitive – sorts data in dictionary order but does not recognize case differences. Uppercase letters are equivalent to their lowercase counterparts and are intermingled in sorting results. Useful for avoiding duplicate entries in tables of names.
Dictionary order, case-insensitive, accent-sensitive, order with preference – does not recognize case difference in determining equivalency of items. A word in uppercase is equivalent to the same word in lowercase. Preference is given to uppercase letters (they appear first) if all other conditions are equal.
Using case-insensitive with preference may cause poor performance in large tables when the columns specified in an order by clause match the key of the table’s clustered index. Do not select case-insensitive order with preference unless your installation requires that uppercase letters be sorted before lowercase letters in otherwise equivalent strings for order by clauses.
Dictionary order, case-insensitive, accent-insensitive – treats accented forms of a letter as equivalent to the associated unaccented letter. It intermingles accented letters in sorting results.