Collating Sequence
TDV uses binary collation and does not support changing the collation setting. So when the underlying data source’s collation setting is different, push and no-push query results might vary for queries that depend on collation—for example, a query that sorts on a column containing CHAR or VARCHAR data.
Data sources support different collating schemes (some support multiple collating schemes), and their defaults are not always the same as TDV. Furthermore, TDV cannot change data source collating schemes connection by connection or query by query, because most data sources do not allow that.
This difference in collation can cause unpredictable or incorrect results when columns contain special characters (%, -, and so on). Users should looks for the following SQL constructs to make sure that their results are not affected by this difference:
• During JOINs, TDV picks SORT MERGE as the default join algorithm. When executing the SORT MERGE, TDV injects an ORDER BY clause on both sides. If one side of the join contains data source data, the sorting order might be different from what TDV expects, and so the MERGE process may produce incorrect results.
An option is to use {OPTION HASH} in SORT MERGE queries, forcing TDV to use a HASH algorithm instead of the SORT MERGE algorithm. Be aware, though, that the HASH algorithm uses more memory because the query engine needs to hash the smaller side and then stream the bigger side over it.
• In general, data sources may have different result when ORDER BY is pushed vs. executed within TDV.
• If a WHERE clause contains a predicate with special characters, results might differ between push and no-push.
A check box near the bottom of the Advanced tab for data sources lets you mark the data sources as Collation Sensitive. TDV does not use the SORT MERGE join algorithm if one of the data sources involved in the join is marked as collation sensitive.
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.