Skip to main content

Microsoft SQL Server

Time-travel support​

Time-travel is not currently supported by DirectQuery for MS SQL, despite a partial support in this database through MS SQL's temporal tables. This means that updating that applications based on this Database are subject to data desynchronization in case of changes in the Database.
Read this page to learn more about the visible effects of this desynchronization.

Vector supports​

Only multi-rows vectors and multi-columns vectors are supported by MS SQL.

MS SQL uses Microsoft Transact-SQL dialect. Though this dialect offers a basic array type, it does not offer powerful aggregation functions to work with them.

Version specificities​

At session initialization, a query is run to discover the database version. Based on the version, some features might not be available. Some of them are emulated by DirectQuery, but some other are just not available.

Version 2017​

  • Does not support the SQL function APPROX_DISTINCT_COUNT, which limits the kind of statistics queries that can be run by DirectQuery
  • Does not support the SQL functions GREATEST and LEAST, which are emulated for the aggregation functions LongSum and ShortSum

Version 2019​

  • Does not support the SQL functions GREATEST and LEAST, which are emulated for the aggregation functions LongSum and ShortSum

Gotchas​

Nullable fields​

MS SQL can define fields with nullable types. DirectQuery is capable of detecting this and defines its internal model accordingly.
While this is not a problem in itself, it can conflict with the rule in Atoti cubes that all levels must be based on non-nullable values. This does not create an issue of any sort as the local model is updated behind the scene, assigning a default value based on the type.
It has a side effect on the query performance, as DirectQuery must convert on-the-fly null values to their assigned default values, as well as adding extra conditions during joins to handle null values on both sides.

Pool sizing​

Due to some limitations of the MS SQL JDBC driver, a Hikari connection pool is use on top of the driver to manage the connections to the database. The pool size is configured automatically by the DirectQuery connector, based on the number of CPU cores available. It is possible to override this setting with the system properties hikaricp.configurationFile (via -Dhikaricp.configurationFile=path/to/hikari.properties).

The associated file format is:

maximumPoolSize=31