The difference between BIN2 and Case-Sensitive collations in SQL

Question

The difference between BIN2 and Case-Sensitive collations in SQL

Jovan Popovic

SSC Enthusiast

Points: 191
More actions
March 15, 2024 at 12:00 am

#4383123

Comments posted to this topic are about the item The difference between BIN2 and Case-Sensitive collations in SQL

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135259 More actions · Answer 1

Recently a software vendor required collation Latin1_General_100_CS_AS_KS_WS_SC for their databases.

I still don't understand why, but - as we are used to SQL_Latin1_General_CP1_CI_AS - it really is anoying to say the least.

I'm just wondering what would be the performance impact of chosing such a restricted collation.

IMHO this software vendor maybe should develope with these restrictions, but should never enforce such collation without profound justification. ( and not just "that's how it works" )

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

MarryJQ Grasshopper Points: 12 More actions · Answer 2

Alright, so BIN2 and fancy case-sensitive stuff in SQL both control how your data gets ordered and compared, but they work differently:

BIN2: Imagine sorting chips by their code number, not their flavor. A red and blue chip with the same code would be next to each other, even though they're different. BIN2 is super fast, but it doesn't care if things are uppercase (like BLUE) or lowercase (like blue).

Case-Sensitive: This treats things like words, where order matters. "Apple" would come before "apple" because the uppercase A comes before the lowercase a. Use this when you actually care about those capital letters!

Here's a quick tip:

BIN2: Fast like lightning, sorts like numbers (uppercase/lowercase doesn't matter).

Case-Sensitive: Sorts words properly, including those tricky capital letters.

Basically:

Use BIN2 for quick sorting when uppercase and lowercase don't matter (like ID numbers).

Use Case-Sensitive for sorting stuff like names or sentences where capitals count.

Marry JWQ

Chris Wooding SSCarpal Tunnel Points: 4530 More actions · Answer 3

BIN2 does distinguish between uppercase and lowercase, it just sorts them differently from CS collations.

Jovan Popovic SSC Enthusiast Points: 191 More actions · Answer 4

Johan, you would need to check with the vendor, but I don't belive that performance improvements are the core reason. I belive that for some special purposethey choose KS+WS flags because application logic depends on it.

There are some common SQL collation that are additional optimized, but I don't belive that thye choose it for perf purpose.

This reply was modified 1 month, 1 week ago by Jovan Popovic.

Johan Bijnens SSC Guru Points: 135259 More actions · Answer 5

Jovan Popovic wrote:

Johan, you would need to check with the vendor, but I don't belive that performance improvements are the core reason. I belive that for some special purposethey choose KS+WS flags because application logic depends on it.
There are some common SQL collation that are additional optimized, but I don't belive that thye choose it for perf purpose.

We share the same finding about this chosen collation.

SV doesn't supply the actual reason(s) and isn't open for discussion at all.