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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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 & Architect, EE Solution GuideCommented:
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
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

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 & Architect, EE Solution GuideCommented:
>> 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 & Architect, EE Solution GuideCommented:
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 & Architect, EE Solution GuideCommented:
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 & Architect, EE Solution GuideCommented:
Welcome and glad to guide you in the correct direction..
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.