The difference between BIN2 and Case-Sensitive collations in SQL

A common question that arises in SQL Server collation discussions is whether a binary collation (BIN2) is functionally equivalent to a case-sensitive collation that is sensitive to all aspects of sensitivity (for instance, Latin1_General_100_CS_AS). In simpler terms, if we opt for a collation that is case-sensitive, accent-sensitive, and sensitive to all other aspects, would it be the same as a binary collation when comparing the string values?

Sometimes, individuals who require case-sensitive collation opt for BIN2 collation, anticipating improved performance without altering functionality. However, they often wonder if case-sensitive and binary collations will have identical behavior.

The answer is