Skip to main content
Skip table of contents

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:

ParameterDefault 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)
JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.