Skip to main content
Version: Next

Microsoft SQL Server Data Connector

Microsoft SQL Server is a relational database management system developed by Microsoft.

The Microsoft SQL Server Data Connector enables federated/accelerated SQL queries on data stored in MSSQL databases.

Limitations
  1. The connector supports SQL Server authentication (SQL Login and Password) only.
  2. Spatial types (geography) are not supported, and columns with these types will be ignored.
  3. DATETIME2 and DATETIMEOFFSET columns are mapped to Arrow Timestamp(Nanosecond). Timestamps outside the nanosecond range (approximately years 1677–2262) will return an error. This is an inherent limitation of Arrow's nanosecond timestamp representation.
datasets:
  - from: mssql:path.to.my_dataset
    name: my_dataset
    params:
      mssql_connection_string: ${secrets:mssql_connection_string}

Configuration​

from​

The from field takes the form mssql:database.schema.table where database.schema.table is the fully-qualified table name in the SQL server.

info

Unquoted identifiers are normalized to lowercase. To reference a table, schema, or database with mixed-case characters, wrap each case-sensitive part in double quotes: mssql:my_database."MySchema"."MyTable". See Identifier Case Sensitivity.

name​

The dataset name. This will be used as the table name within Spice.

Example:

datasets:
  - from: mssql:path.to.my_dataset
    name: cool_dataset
    params: ...
SELECT COUNT(*) FROM cool_dataset;
+----------+
| count(*) |
+----------+
| 6001215  |
+----------+

The dataset name cannot be a reserved keyword or any of the following keywords that are reserved by Microsoft SQL Server:

  • OUTER
  • SET
  • QUALIFY
  • WINDOW
  • END
  • FOR

params​

The data connector supports the following params. Use the secret replacement syntax to load the secret from a secret store, e.g. ${secrets:my_mssql_conn_string}.

Parameter NameDescription
mssql_connection_stringThe ADO connection string to use to connect to the server. This can be used instead of providing individual connection parameters.
mssql_hostThe hostname or IP address of the Microsoft SQL Server instance.
mssql_port(Optional) The port of the Microsoft SQL Server instance. Default value is 1433.
mssql_database(Optional) The name of the database to connect to. The default database (master) will be used if not specified.
mssql_usernameThe username for the SQL Server authentication.
mssql_passwordThe password for the SQL Server authentication.
mssql_encrypt(Optional) Specifies whether encryption is required for the connection.
  • true or require: (default) This mode requires an SSL connection. If a secure connection cannot be established, server will not connect.
  • false or disable: This mode will not attempt to use an SSL connection, even if the server supports it. Only the login procedure is encrypted.
mssql_trust_server_certificate(Optional) Specifies whether the server certificate should be trusted without validation when encryption is enabled.
  • true: The server certificate will not be validated and it is accepted as-is.
  • false: (default) Server certificate will be validated against system's certificate storage.

Example​

datasets:
  - from: mssql:SalesLT.Customer
    name: customer
    params:
      mssql_host: mssql-host.database.windows.net
      mssql_database: my_catalog
      mssql_username: my_user
      mssql_password: ${secrets:mssql_pass}
      mssql_encrypt: true
      mssql_trust_server_certificate: true

Performance​

TopK / ORDER BY ... LIMIT pushdown​

Spice pushes ORDER BY ... LIMIT N queries down to SQL Server as SELECT TOP N ... ORDER BY ..., avoiding transferring unnecessary rows over the network. This pushdown is applied when the sort can be satisfied exactly by SQL Server — which depends on NULL ordering.

SQL Server treats NULL as the smallest possible value, so its native ordering is:

DirectionNULLs position
ASCFirst
DESCLast

Most SQL clients and tools (including Spice's default planner) use the opposite convention (ASC NULLS LAST, DESC NULLS FIRST). When the requested NULL ordering doesn't match SQL Server's native behavior, Spice falls back to fetching all matching rows and applying the limit locally.

To guarantee TopK pushdown on nullable columns, explicitly specify the NULL ordering that matches SQL Server's native behavior:

-- Pushed down: DESC NULLS LAST matches SQL Server native ordering
SELECT id, value FROM my_dataset ORDER BY value DESC NULLS LAST LIMIT 10;

-- Pushed down: ASC NULLS FIRST matches SQL Server native ordering
SELECT id, value FROM my_dataset ORDER BY value ASC NULLS FIRST LIMIT 10;
tip

Sorting on NOT NULL columns (e.g. primary keys) always pushes the limit down regardless of the NULLS clause, since there are no NULLs to order.

Secrets​

Spice integrates with multiple secret stores to help manage sensitive data securely. For detailed information on supported secret stores, refer to the secret stores documentation. Additionally, learn how to use referenced secrets in component parameters by visiting the using referenced secrets guide.

Cookbook​