Bin collation speed for english language

http://technet.microsoft.com/en-us/library/ms143524%28SQL.90%29.aspx
The above link mentions "Binary is the fastest sorting order"
Does this apply when the language is only english?
LVL 6
anushahannaAsked:
Who is Participating?
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
No, Its applicable to all languages and characters..
If you have Binary collation, then it would convert all of your character strings into ANSI values and then apply Binary sort orders to it..

And that's the reason why it was faster compared to other collations where the sort order and everything is defined..
0
 
anushahannaAuthor Commented:
OK. Can you help me understand that with English only, how sorts different in speed between BIN and nonBIN.

a table has these 4 rows:
First
first
1st
1'st

and if the collation is binary, what are the ansi values for these? and what would a BIN sort be like for the above set

0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
This can help you out..

http://msdn.microsoft.com/en-us/library/ms143350.aspx

ANSI Code page values can be obtained from the table here:

http://www.handheld-basic.com/documentation/text/page_599.html
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
anushahannaAuthor Commented:
Thanks for the links. In the ANSI link, would it be right that
First is " 46 69 72 73 74" and
first will be " 66 69 72 73 74"?
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> First is " 46 69 72 73 74" and
first will be " 66 69 72 73 74"?

Small change, you checked the values of Hexadecimal code instead of Decimal code..
0
 
anushahannaAuthor Commented:
OK. Thanks for catching it.

So for example- between F and f, the values are 70 and 102. Why is a binary sort on 70 and 102 ANSI decimal be faster than plain F and f.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
ANSI code pages use the default codepages and for explanation only, I have used the example above.
Kindly check this one out:

http://www2.sqlblog.com/blogs/linchi_shea/archive/2009/01/31/string-comparison-binary-vs-dictionary.aspx

And in order to find how it internally works best, you need to understand Binary sort algorithm better and this can help you.
http://en.wikipedia.org/wiki/Binary_search_tree

And the different types of sort here:
http://en.wikipedia.org/wiki/Sorting_algorithm
0
 
anushahannaAuthor Commented:
Wow.. that is comprehensive. Thanks.  I will read them and post you back.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Sure, Kindly revert with your doubts so that I can explain it in more detail.
0
 
anushahannaAuthor Commented:
rrjegan17, thanks for your kind help. Could not absorb it all, but I get some good ideas. Thank you for your help with this post. The example was a good proof how bin works faster on sorts.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Welcome and glad to guide you in the correct direction..
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.