Two codes look identical on the screen, yet your query treats them differently. A binary collation gives SQL Server explicit rules for comparing character values. You can use those rules to distinguish case and accents, but you also need to understand sorting, spaces, and index access.
Retire the Numbered Sort-Order Habit
Older SQL Server installations exposed numbered sort orders. That vocabulary still turns up in inherited scripts and old server notes. Today, a named collation tells you much more directly what a string comparison means. The name describes language rules and options such as case sensitivity. A binary option changes the comparison approach itself.
I check the stored collation before blaming the application for inconsistent matches. Two databases on the same instance can have different defaults. Two columns in one table can also differ. The server default is a starting point, not a promise about every string you touch.
The following checks show the instance setting and character columns in the current database. Include the schema and table names when saving the output. A column name by itself makes a very poor inventory. Apparently, every database needs a column named Name.
SELECT SERVERPROPERTY('Collation') AS ServerCollation;
SELECT SCHEMA_NAME(t.schema_id) AS SchemaName,
t.name AS TableName, c.name AS ColumnName, c.collation_name
FROM sys.tables AS t
JOIN sys.columns AS c ON c.object_id = t.object_id
WHERE c.collation_name IS NOT NULL
ORDER BY SchemaName, TableName, c.column_id;See What a BIN2 Binary Collation Sorts
The older BIN family uses legacy binary sorting rules. BIN2 provides a consistent code-point comparison for Unicode characters. For new binary designs, choose BIN2 rather than carrying forward an old BIN choice without a reason. Unicode code points describe characters, while encoded bytes depend on the string data type and encoding.
For these Latin letters, uppercase values precede lowercase values, and accented characters follow their numeric code-point order. That sequence differs from the order a reader expects in a language-aware address book. Neither order is universally correct. Each answers a different requirement.
Run the next example without changing your database default. The COLLATE clause applies only to this expression. Add characters from the actual business data before approving the rule. Does your identifier really distinguish Cafe from Cafe with an accent? The answer belongs in the data contract, not in an accidental server default.
SELECT v.Token, UNICODE(v.Token) AS FirstCodePoint
FROM (VALUES (N'a'), (N'A'), (N'z'), (N'Z'), (N'é')) AS v(Token)
ORDER BY v.Token COLLATE Latin1_General_BIN2;Compare Values Without Folding Case
Use an explicit comparison when the requirement is case and accent sensitivity. The next query keeps the test small enough to inspect. Its literal uses Unicode syntax, so character conversion does not depend on the session's non-Unicode code page. The column expression receives the comparison rule directly.
A binary collation distinguishes the illustrated case and accent differences. It does not make every SQL comparison a byte-for-byte identity test. SQL Server character equality still applies its trailing-space padding behavior. If trailing spaces distinguish your identifiers, compare DATALENGTH as well, or design an appropriate binary representation.
I separate these requirements during reviews because exact means different things to different teams. Sometimes it means case matters. Sometimes it means every stored byte matters. Those are separate tests. Write both expectations down before changing a production predicate. Also test leading spaces, empty values, NULL, and characters outside the initial alphabet.
SELECT v.Token
FROM (VALUES (N'Cafe'), (N'cafe'), (N'Café'), (N'Cafe ')) AS v(Token)
WHERE v.Token COLLATE Latin1_General_BIN2 = N'Cafe'
AND DATALENGTH(v.Token) = DATALENGTH(N'Cafe');
Keep the Index Comparison Compatible
A query-level COLLATE on an indexed column changes the expression the optimizer must evaluate. When that requested rule differs from the index's stored ordering, the existing index cannot directly provide the requested comparison as its normal seek predicate. Expect a scan or residual work and inspect the actual plan.
Do not infer the access path from the fact that an index exists. In SSMS, enable the actual execution plan and inspect Seek Predicates separately from Predicate. A filter beside a seek is still extra comparison work. Check STATISTICS IO for the query you actually run.
For recurring searches, design the stored column with the intended rule. Another option is a persisted computed representation indexed under the required collation, after checking determinism and required settings. That adds write and storage costs. A rare validation query and a frequently called lookup deserve different solutions. Changing the entire database default is too broad for one isolated lookup.
Handle Joins and Temporary Data Deliberately
Collation conflicts also appear when joining data from different databases or combining permanent tables with temporary tables. Each side brings its own definition. Applying COLLATE just to silence an error hides the business decision if nobody records why that rule was chosen.
Check the actual columns first. Then decide which comparison the join needs. If temporary character columns should follow the current database, define them with COLLATE DATABASE_DEFAULT. That avoids inheriting an unrelated tempdb default. It still uses the current database's rules, so changing database context changes what that declaration means.
Avoid converting every text column to BIN2 because one import contains case-sensitive codes. Human names, locations, identifiers, and free text have different needs. Keep display sorting separate from identifier equality where the application requires both. Include representative international characters in the test set, and use Unicode types when the content requires them.
Approve a Binary Collation With Real Edge Cases
A useful test compares the returned values, their order, and the execution plan. Save the input values beside the expected matches. An alphabet-only test is too easy to pass. Include accents, repeated spaces, mixed case, and supplementary characters used by the application.
Check uniqueness too. A unique key under a case-insensitive collation rejects values that a binary key treats as different. The reverse migration can fail when previously distinct values collapse under the new rule. Find those conflicts before attempting schema changes.
Keep a binary collation focused on the requirement it solves. Confirm the application submits parameters with compatible types and lengths. Then compare the same representative requests before and after the change. Correct results come first. A fast lookup using the wrong equality rule is simply a fast way to return the wrong row.
Related reading on this blog: Case-Sensitive Search and UTF-8 Collations in SQL Server 2019: When They Save Space.
Exact matching is not a font choice, it is a comparison rule.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
