This will return the correct result:
select CHARINDEX( NCHAR(254) Collate Latin1_General_BIN2, N'Tþ' Collate Latin1_General_BIN2)
select CHARINDEX( NCHAR(254) Collate Latin1_General_BIN2, N'Th' Collate Latin1_General_BIN2 )
select CHARINDEX( NCHAR(253) Collate Latin1_General_BIN2, N'Th' Collate Latin1_General_BIN2 )
select CHARINDEX( NCHAR(254) Collate Latin1_General_BIN2, N'Thþ' Collate Latin1_General_BIN2)
Documentation says:
Using Binary Collations
The following considerations will help you to decide whether old or new binary collations are appropriate for your Microsoft SQL Server
implementation. Support for both BIN and BIN2 collations will continue
in future SQL Server releases.
Binary collations sort data based on the sequence of coded values defined in a particular code page. A binary collation in SQL Server
defines the language locale and the ANSI code page to be used,
enforcing a binary sort order. Binary collations are useful in
achieving improved application performance due to their relative
simplicity.
Previous binary collations in SQL Server performed an incomplete code-point-to-code-point comparison for Unicode data, in that older
SQL Server binary collations compared the first character as WCHAR,
followed by a byte-by-byte comparison. For backward compatibility
reasons, existing binary collation semantics will not be changed.
Guidelines for Using Binary Collations
If your Microsoft SQL Server 2005 applications interact with older versions of SQL Server that use binary collations, continue to use
binary. Binary collations may be a more suitable choice for mixed
environments.
Guidelines for Using BIN2 Collations
Binary collations in this release of SQL Server include a new set of pure code-point comparison collations. Customers can choose to
migrate to the new binary collations to take advantage of true
code-point comparisons, and they should utilize the new binary
collations for development of new applications. The new BIN2 suffix
identifies collation names that implement the new code-point collation
semantics. In addition, a new comparison flag is added corresponding
to BIN2 for the new binary sort. Advantages include simpler
application development and clearer semantics.
IE. BIN2
collation is equivalent to using Ordinal
in C#
wrt to charindex
.