# 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
###### Who is Participating?

SQL 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

Author 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

SQL Server DBA & ArchitectCommented:

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

Author 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

SQL 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

Author 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

SQL 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

Author Commented:
Wow.. that is comprehensive. Thanks.  I will read them and post you back.
0

SQL Server DBA & ArchitectCommented:
Sure, Kindly revert with your doubts so that I can explain it in more detail.
0

Author 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

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