DuckLake with its catalog in Microsoft SQL Server or Azure SQL - embedded DuckLake, native TDS through the mssql extension.
Maintainer(s):
VGSML
Compatibility Matrix
This table covers all DuckDB releases that have not reached end-of-life, including LTS releases, and shows the 2 most recent patch releases of each. Older patch releases are not listed, but the extension may be available for them as well.
| Platform | 1.4.4 | 1.4.5 | 1.5.5 | 1.5.6 | 2.0.0 |
|---|---|---|---|---|---|
| Linux AMD64 | |||||
| Linux ARM64 | |||||
| macOS AMD64 | |||||
| macOS ARM64 | |||||
| Windows AMD64 | |||||
| Windows ARM64 | |||||
| WebAssembly |
Installing and Loading
INSTALL mssql_ducklake FROM community;
LOAD mssql_ducklake;
Example
INSTALL mssql FROM community;
INSTALL mssql_ducklake FROM community;
LOAD mssql_ducklake;
ATTACH 'ducklake:mssql:Server=localhost,1433;Database=lake_meta;User Id=sa;Password=...;TrustServerCertificate=yes' AS lake (DATA_PATH 's3://bucket/lake/');
CREATE TABLE lake.t AS SELECT range AS i FROM range(100);
SELECT count(*) FROM lake.t;
About mssql_ducklake
Runs DuckLake with its catalog in Microsoft SQL Server or Azure SQL. The extension embeds the complete, unmodified DuckLake source at a pinned commit (catalog format 1.1 on DuckDB 2.0) and adds a SQL Server metadata manager beside DuckLake's built-in PostgreSQL and SQLite ones; it talks to the server through the mssql extension (native TDS, no ODBC), which must be installed too. Everything DuckLake does - snapshots, time travel, schema evolution, data inlining, partitioning, the maintenance functions, ducklake:postgres: catalogs as well - works as in the stock extension; the data files live wherever DuckLake puts them.
Documentation: https://hugr-lab.com/docs/mssql-ducklake/
Mutually exclusive with the stock ducklake extension: both provide the same functions and the same ducklake: attach prefix - load one or the other, and load mssql_ducklake before the first ATTACH 'ducklake:...'. Needs the mssql extension (v0.3.0 or newer on DuckDB 2.0, v0.2.5 or newer on 1.5.6), and a SQL Server 2019+ or Azure SQL database (a UTF-8 collation).
Experimental. The catalog is shaped for SQL Server on creation (primary keys, UTF-8 binary collation, indexes, forced parameterization), a commit goes to the server as one T-SQL batch, and the catalog loads in one statement and is kept per ATTACH. On equal Azure VMs the 500-table benchmark takes 0.63x of the PostgreSQL backend's time; what is not there yet is on the limitations page.
Added Functions
| function_name | function_type | description | comment | examples |
|---|---|---|---|---|
| ducklake_add_data_files | table | NULL | NULL | |
| ducklake_cleanup_old_files | table | NULL | NULL | |
| ducklake_commit | table | NULL | NULL | |
| ducklake_current_snapshot | table | NULL | NULL | |
| ducklake_delete_orphaned_files | table | NULL | NULL | |
| ducklake_expire_snapshots | table | NULL | NULL | |
| ducklake_flush_inlined_data | table | NULL | NULL | |
| ducklake_last_committed_snapshot | table | NULL | NULL | |
| ducklake_list_files | table | NULL | NULL | |
| ducklake_merge_adjacent_files | table | NULL | NULL | |
| ducklake_options | table | NULL | NULL | |
| ducklake_rewrite_data_files | table | NULL | NULL | |
| ducklake_scan | table | NULL | NULL | |
| ducklake_set_commit_message | table | NULL | NULL | |
| ducklake_set_option | table | NULL | NULL | |
| ducklake_settings | table | NULL | NULL | |
| ducklake_snapshots | table | NULL | NULL | |
| ducklake_table_changes | table_macro | NULL | NULL | |
| ducklake_table_deletions | table | NULL | NULL | |
| ducklake_table_info | table | NULL | NULL | |
| ducklake_table_insertions | table | NULL | NULL | |
| mssql_azure_auth_test | scalar | NULL | NULL | |
| mssql_close | scalar | NULL | NULL | |
| mssql_close_all | scalar | NULL | NULL | |
| mssql_ducklake_version | scalar | NULL | NULL | |
| mssql_exec | scalar | NULL | NULL | |
| mssql_invalidate_cache | scalar | NULL | NULL | |
| mssql_kerberos_auth_test | scalar | NULL | NULL | |
| mssql_kerberos_auth_test_secret | scalar | NULL | NULL | |
| mssql_open | scalar | NULL | NULL | |
| mssql_ping | scalar | NULL | NULL | |
| mssql_pool_stats | table | NULL | NULL | |
| mssql_preload_catalog | scalar | NULL | NULL | |
| mssql_refresh_cache | scalar | NULL | NULL | |
| mssql_scan | table | NULL | NULL | |
| mssql_version | scalar | NULL | NULL | |
| mssql_winsspi_auth_test | scalar | NULL | NULL | |
| mssql_winsspi_auth_test_spn | scalar | NULL | NULL | |
| murmur3_32 | scalar | NULL | NULL |
Overloaded Functions
This extension does not add any function overloads.
Added Types
| type_name | type_size | logical_type | type_category | internal |
|---|---|---|---|---|
| MSSQL_NVARCHAR | 16 | VARCHAR | STRING | true |
| MSSQL_VARCHAR | 16 | VARCHAR | STRING | true |
Added Settings
| name | description | input_type | scope | aliases |
|---|---|---|---|---|
| ducklake_default_data_inlining_row_limit | Default row limit for data inlining (0 disables inlining) | UBIGINT | GLOBAL | [] |
| ducklake_max_retry_count | The maximum amount of retry attempts for a ducklake transaction | UBIGINT | GLOBAL | [] |
| ducklake_retry_backoff | Backoff factor for exponentially increasing retry wait time | DOUBLE | GLOBAL | [] |
| ducklake_retry_wait_ms | Time between retries | UBIGINT | GLOBAL | [] |
| ducklake_target_file_size | Target file size for insertion and compaction | VARCHAR | GLOBAL | [] |
| ducklake_write_deletion_vectors | [EXPERIMENTAL] Write Iceberg V3 deletion vectors (puffin) instead of positional delete files (parquet) | BOOLEAN | GLOBAL | [] |
| mssql_acquire_timeout | Connection acquire timeout in seconds (0 = fail immediately) | BIGINT | GLOBAL | [] |
| mssql_attach_validation_timeout | Timeout in seconds for the ATTACH-time credential round trip (0 = inherit mssql_connection_timeout). Spec 047 / US2. | BIGINT | GLOBAL | [] |
| mssql_browser_timeout_seconds | SQL Server Browser UDP query timeout in seconds for named-instance resolution (default: 3) | BIGINT | GLOBAL | [] |
| mssql_catalog_cache_ttl | Metadata cache TTL in seconds (0 = manual refresh only) | BIGINT | GLOBAL | [] |
| mssql_catalog_native_types | Report MSSQL_VARCHAR(n)/MSSQL_NVARCHAR(n) for attached string columns instead of VARCHAR (default: true) | BOOLEAN | 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_convert_varchar_max | Convert VARCHAR(MAX) to NVARCHAR(MAX) in table scans for UTF-8 compatibility (default: true) | BOOLEAN | GLOBAL | [] |
| mssql_copy_flush_rows | Rows per bulk-load batch sent to SQL Server (default: 102400 — SQL Server's threshold for writing compressed columnstore rowgroups directly; 0 = one batch at the end, high memory) | BIGINT | GLOBAL | [] |
| mssql_copy_parallel_writers | Concurrent bulk-load connections a single COPY/CTAS may open (default: 0 = derive from DuckDB's thread count, capped at 8). 1 disables parallel loading. Ignored inside an explicit transaction, where the connection is pinned | BIGINT | GLOBAL | [] |
| mssql_copy_tablock | TABLOCK hint for COPY/BCP: 'auto' (by target shape — heap on, anything clustered off), 'true', or 'false' | VARCHAR | 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 | Type given to an unannotated VARCHAR column by CTAS and COPY: NVARCHAR (Unicode, default) or VARCHAR (single-byte, needs a UTF-8 collation) | VARCHAR | GLOBAL | [] |
| mssql_ctas_use_bcp | Use BCP protocol for CTAS data transfer (default: true, 2-10x faster than INSERT) | BOOLEAN | GLOBAL | [] |
| mssql_default_string_length | Length for an unannotated VARCHAR column created by CTAS/COPY (0 = MAX, the default) | BIGINT | GLOBAL | [] |
| mssql_default_table_kind | Shape of a table created by CTAS/COPY: HEAP (default) or COLUMNSTORE | 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_ducklake_forced_parameterization | Set PARAMETERIZATION FORCED on a DuckLake catalog's SQL Server database when the catalog is shaped; one plan per query shape instead of one per literal | BOOLEAN | GLOBAL | [] |
| mssql_enable_statistics | Enable statistics collection from SQL Server for query optimizer | BOOLEAN | GLOBAL | [] |
| mssql_exec_invalidate_cache | Invalidate the catalog cache after DDL executed via mssql_exec() (CREATE/DROP/ALTER/TRUNCATE/RENAME/EXEC) | 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_login7_max_packet | TEST-ONLY: max LOGIN7 TDS packet size in bytes for integrated auth (0 = default 4096) | BIGINT | GLOBAL | [] |
| mssql_metadata_timeout | Metadata query timeout in seconds (default: 300, 0 = no timeout). Increase for very large catalogs | BIGINT | GLOBAL | [] |
| mssql_min_connections | Minimum connections to maintain per context | BIGINT | GLOBAL | [] |
| mssql_named_instance_resolution | Enable SQL Server Browser (UDP 1434) resolution of host\instance connection strings | BOOLEAN | GLOBAL | [] |
| mssql_order_pushdown | Enable ORDER BY pushdown to SQL Server (default: false) | BOOLEAN | GLOBAL | [] |
| mssql_query_timeout | Query execution timeout in seconds (0 = no timeout, default: 30) | BIGINT | GLOBAL | [] |
| mssql_reset_connection | Reset session state (temp tables, SET options, open transactions) when a connection returns to the pool; false hands that state to the user | BOOLEAN | 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 | [] |
| mssql_tds_packet_size | TDS frame size in bytes requested at login, clamped to [512, 32767] (default: 16384) | BIGINT | GLOBAL | [] |
| mssql_utf8_collation | Collation given to VARCHAR columns created by CTAS when the server supports UTF-8 (default: Latin1_General_100_BIN2_UTF8, matching Fabric; empty inherits the database default) | VARCHAR | GLOBAL | [] |
| mssql_utf8_support | Advertise the UTF8SUPPORT feature in LOGIN7 so UTF-8 collation columns arrive as UTF-8 instead of UTF-16 (default: true) | BOOLEAN | GLOBAL | [] |