SQL Server Sorting Order
SQL Server supports multiple collating schemes, and its default is not the same as TDV. Furthermore, TDV cannot change data source collating schemes connection by connection.
The default SQL Server collating behavior results in incorrect results when columns contain special characters in situations like this:
• SQL Server data is on one side of a SORT MERGE join algorithm. The query engine inserts an ORDER BY clause on the joining columns, and the orderings differ.
An option is to use {OPTION HASH} in SORT MERGE queries, forcing TDV to use a HASH algorithm instead of SORT MERGE for joins. Be aware, though, that the HASH algorithm uses more memory because the query engine needs to hash the smaller side and then streams the bigger side over it.
• SQL Server data is in a comparison predicate of a WHERE clause.
• SQL Server data is in an ORDER BY clause.
In many situations you can specify a different collating scheme in the SQL (for example, using “COLLATE Latin1_General_BIN”), but this can interfere with indexing and thus affect performance.