Comparing VARCHAR and VARBINARY in SQL Server 2000
I need to do some in-memory merge in C # of two sorted streams of strings coming from one or more SQL Server 2000 databases into one sorted stream. These data streams can be huge, so I don't want to pull both streams into memory. Instead, I need to store one element at a time from each stream in memory, and at each step compare the current element from each stream, push the minimum to the final stream, and pull the next element from the corresponding original stream. To do this correctly, the in-memory comparison must match the database collation (consider streams [A,B,C]
and [A,B,C]
: correct merged sequence [A,A,B,B,C,C]
, but if your in-memory comparison thinks C < B
, your in-memory merge will giveA,A,B
, and at that moment it will look at B
and C
, and give C
, which will lead to an incorrectly sorted stream.)
So my question is, is there a way to simulate any of the mappings in SQL Server 2000 to enum System.StringComparison
in C #, or vice versa? The closest I've come to is to use System.StringCompaison.Ordinal
with the results of database rows converted to VARBINARY
with a standard order VARBINARY
, which works, but I'd rather add a suggestion "order by name collate X"
to my SQL queries where X is some sort of sort that works exactly the same as order VARBINARY
rather than converting all strings to VARBINARY
when they leave the database and then returning to rows as they come in.
Look at the class StringComparer
. This provides more robust character and string comparisons than you will find in String.Compare
. There are three sets of static instances ( CurrentCulture
, InvariantCulture
, Ordinal
), and case-insensitive versions of each. For more specialized cultures, you can use the function StringComparer.Create()
to create a mapping specific to a culture.
a source to share
As of sql 2005, I know the db engine does not make OS calls to sort, the ordering rules are statically passed from the db (can be updated by a service pack, but not changed from OS). So I don't think you can say with certainty that a given set of application code can order the same, unless you have the same code as the db server, unless you use binary collation.
But if you are using binary collation in your db and client code you should have no problem at all.
EDIT - Any sort that ends with _BIN will give you a binary sort. The rest of the collation name will determine which codepage is used to store the CHAR data, but will not affect the order. _BIN stands for strictly binary collation. See http://msdn.microsoft.com/en-us/library/ms143515(SQL.90).aspx
a source to share