MS SQL Server as Analytical Storage

When MS SQL Server is used as analytic storage, certain performance aspects need special attention. To ensure performant execution of distributed joins and for using ORDER BY, the CData Virtuality Server requires that all systems connected to it have a compatible sorting order; otherwise, it must run sorting internally.

MS SQL Server has no sorting order for string types compatible with other popular databases or operating systems. Therefore, the CData Virtuality Server is configured by default not to push down the string sorting operations to MS SQL Server for the STRING and VARCHAR types. This will lead to increased disk and memory usage and some performance deterioration if not enough memory is available. In such cases, the CData Virtuality Server requires more local resources (i.e. RAM) to avoid performance deterioration.

The default values for MS SQL Server are as follows:

Parameter Default value

supportsOrderByString

FALSE

If only sort operations on the first 255 ASCII characters are needed, supportsOrderByString can be set to TRUE;
If sort operations on full Unicode are needed, supportsOrderByString needs to be FALSE for correct work of the CData Virtuality Server

OrderByStringConversion (cast(cast(%s as varchar) as varbinary)

comparisonStringConversion (%s COLLATE Latin1_General_CS_AS)