Connect DuckDB to Microsoft SQL Server via native TDS (including TLS).
Maintainer(s):
VGSML
Installing and Loading
INSTALL mssql FROM community;
LOAD mssql;
Example
FORCE INSTALL mssql FROM community;
LOAD mssql;
ATTACH 'mssql://sa:YourStrong!Passw0rd@localhost:1433?database=master&use_encrypt=true' AS ms;
SELECT * FROM ms.dbo.table LIMIT 5;
About mssql
The mssql extension provides a DuckDB connector for Microsoft SQL Server using the native TDS protocol. Support DuckDB version: 1.4.4
The extension is experimental and may not cover all edge cases. Please report any issues on the GitHub repository.
Features:
- Native TDS protocol (no ODBC/JDBC required)
- Schema, table, and view catalog integration
- Projection and filter pushdown
- TLS/SSL encrypted connections (encrypt parameter)
- INSERT support with RETURNING clause
- UPDATE and DELETE support (requires primary key)
- Connection pooling with configurable limits
- DuckDB secret management for credentials
- Transaction support (BEGIN, COMMIT, ROLLBACK)
- CREATE TABLE AS SELECT (CTAS) support
- COPY/BCP support for bulk data loading
See all features and configuration options in the documentation.
Limitations:
- Limited data type support (see documentation for details)
- Named instances not supported
- Windows Authentication not supported
Added Functions
| function_name | function_type | description | comment | examples |
|---|---|---|---|---|
| mssql_close | scalar | NULL | NULL | |
| mssql_exec | scalar | NULL | NULL | |
| mssql_open | scalar | NULL | NULL | |
| mssql_ping | scalar | NULL | NULL | |
| mssql_pool_stats | table | NULL | NULL | |
| mssql_refresh_cache | scalar | NULL | NULL | |
| mssql_scan | table | NULL | NULL | |
| mssql_version | scalar | NULL | NULL |
Added Settings
| name | description | input_type | scope | aliases |
|---|---|---|---|---|
| mssql_acquire_timeout | Connection acquire timeout in seconds (0 = fail immediately) | BIGINT | GLOBAL | [] |
| mssql_catalog_cache_ttl | Metadata cache TTL in seconds (0 = manual refresh only) | BIGINT | GLOBAL | [] |
| mssql_connection_cache | Enable connection pooling and reuse | BOOLEAN | GLOBAL | [] |
| mssql_connection_limit | Maximum connections per attached mssql database | BIGINT | GLOBAL | [] |
| mssql_connection_timeout | TCP connection timeout in seconds | BIGINT | GLOBAL | [] |
| mssql_copy_flush_rows | Rows before flushing to SQL Server during COPY (default: 100000, 0=no flush until end - high memory) | BIGINT | GLOBAL | [] |
| mssql_copy_tablock | Use TABLOCK hint for COPY operations (default: true, improves performance 15-30%) | BOOLEAN | GLOBAL | [] |
| mssql_ctas_drop_on_failure | Drop table if CTAS insert phase fails (default: false, table remains for debugging) | BOOLEAN | GLOBAL | [] |
| mssql_ctas_text_type | Text column type for CTAS: NVARCHAR (Unicode, default) or VARCHAR (collation-dependent) | VARCHAR | GLOBAL | [] |
| mssql_dml_batch_size | Maximum rows per UPDATE/DELETE batch (default: 500, affects parameter count) | BIGINT | GLOBAL | [] |
| mssql_dml_max_parameters | Maximum parameters per UPDATE/DELETE statement (SQL Server limit ~2100) | BIGINT | GLOBAL | [] |
| mssql_dml_use_prepared | Use prepared statements for UPDATE/DELETE operations | BOOLEAN | GLOBAL | [] |
| mssql_enable_statistics | Enable statistics collection from SQL Server for query optimizer | BOOLEAN | GLOBAL | [] |
| mssql_idle_timeout | Idle connection timeout in seconds (0 = no timeout) | BIGINT | GLOBAL | [] |
| mssql_insert_batch_size | Maximum rows per INSERT statement (SQL Server limit: 1000) | BIGINT | GLOBAL | [] |
| mssql_insert_max_rows_per_statement | Hard cap on rows per INSERT statement (SQL Server limit: 1000) | BIGINT | GLOBAL | [] |
| mssql_insert_max_sql_bytes | Maximum SQL statement size in bytes | BIGINT | GLOBAL | [] |
| mssql_insert_use_returning_output | Use OUTPUT INSERTED for RETURNING clause | BOOLEAN | GLOBAL | [] |
| mssql_min_connections | Minimum connections to maintain per context | BIGINT | GLOBAL | [] |
| mssql_query_timeout | Query execution timeout in seconds (0 = no timeout, default: 30) | BIGINT | GLOBAL | [] |
| mssql_statistics_cache_ttl_seconds | Statistics cache TTL in seconds | BIGINT | GLOBAL | [] |
| mssql_statistics_level | Statistics detail level: 0=row count, 1=+histogram min/max, 2=+NDV | BIGINT | GLOBAL | [] |
| mssql_statistics_use_dbcc | Allow DBCC SHOW_STATISTICS for column statistics (requires permissions) | BOOLEAN | GLOBAL | [] |