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 | Acquires an Azure AD access token with the named azure secret, without connecting to SQL Server, and returns the token abbreviated to its first and last characters and its length, or the error. tenant overrides the secret's tenant for interactive (device code) authentication. | NULL | [mssql_azure_auth_test('my_azure_secret')] |
| mssql_catalog_scan | table | Internal: the scan behind an attached SQL Server table. Registered so DuckDB can copy a plan that holds one; read a table by name instead. | NULL | [SELECT * FROM db.dbo.orders] |
| mssql_dml | table | Internal: the vehicle a pushed UPDATE / DELETE / INSERT … SELECT / MERGE runs through (spec 080). Runs the T-SQL statement once on the attached database – atomically in autocommit, on the transaction's connection inside one – and returns its row count as Count. The remote-pushdown rewriter's target, not an API: its arguments carry no compatibility promise. | NULL | [SELECT * FROM mssql_dml('db', 'UPDATE [dbo].[t] SET [v] = 1 WHERE [id] = @p1', {'p1': 5}, '@p1 int', kind := 'update', target_schema := 'dbo', target_table := 't')] |
| mssql_ducklake_catalog_info | table | NULL | NULL | |
| mssql_ducklake_trace | scalar | NULL | NULL | |
| mssql_ducklake_trace_mark | scalar | NULL | NULL | |
| mssql_ducklake_version | scalar | NULL | NULL | |
| mssql_exec | scalar | Runs a T-SQL statement or batch on an attached SQL Server database and returns the number of rows it affected. | NULL | [mssql_exec('db', 'CREATE TABLE dbo.notes (id int PRIMARY KEY, body nvarchar(200))')] |
| mssql_exec_params | scalar | Runs a parameterised T-SQL statement once per row and returns the rows affected: each field of the params STRUCT becomes an @-parameter of an sp_executesql call, declared from its DuckDB type or by the optional declarations list, so the server compiles the statement once. | NULL | [mssql_exec_params('db', 'INSERT INTO dbo.notes (id, body) VALUES (@id, @body)', {'id': 1, 'body': 'hello'})] |
| mssql_invalidate_cache | scalar | Marks cached metadata of an attached SQL Server database as stale – the whole catalog, one schema, or one table – so it is reloaded from the server on next use; everything else keeps its cache. | NULL | [mssql_invalidate_cache('db', 'dbo', 'orders')] |
| mssql_kerberos_auth_test | scalar | Builds the Kerberos (GSSAPI) token for the SQL Server at host and port (default 1433) without connecting to it, and returns the principal, SPN and token size, or the GSSAPI error. | NULL | [mssql_kerberos_auth_test('sql.example.com', 1433)] |
| mssql_kerberos_auth_test_secret | scalar | mssql_kerberos_auth_test driven by the named MSSQL secret: host, port and the Kerberos options (krb5.conf, keytab, credential cache, realm, SPN override) are read from it, as ATTACH would read them. | NULL | [mssql_kerberos_auth_test_secret('my_mssql_secret')] |
| mssql_pool_stats | table | Returns connection pool statistics – total, idle, active and pinned connections, acquire counts and timeouts, creation failures and the last creation error – for every attached SQL Server database, or for the one named. | NULL | [SELECT * FROM mssql_pool_stats(), SELECT * FROM mssql_pool_stats('db')] |
| mssql_preload_catalog | scalar | Loads the metadata of every schema, table and column of an attached SQL Server database in bulk – or of one schema, given as a second argument – and returns a summary of what it loaded. | NULL | [mssql_preload_catalog('db'), mssql_preload_catalog('db', 'dbo')] |
| mssql_refresh_cache | scalar | Reloads the whole metadata cache – schemas, tables, columns – of an attached SQL Server database from the server, now. | NULL | [mssql_refresh_cache('db')] |
| mssql_scan | table | Runs a T-SQL query on an attached SQL Server database and returns its result set. The shape is described at bind time without running the query; prepared := true compiles it once with sp_prepare instead. | NULL | [SELECT * FROM mssql_scan('db', 'SELECT TOP 10 name, create_date FROM sys.tables')] |
| mssql_scan_params | table | mssql_scan with parameters: each field of the STRUCT becomes an @-parameter of an sp_executesql call, declared from its DuckDB type or by the optional declarations list, so one server plan serves every call. | NULL | [SELECT * FROM mssql_scan_params('db', 'SELECT name FROM sys.objects WHERE object_id > @id', {'id': 100}), SELECT * FROM mssql_scan_params('db', 'SELECT name FROM sys.objects WHERE create_date > @since', {'since': TIMESTAMP '2024-01-01'}, '@since datetime')] |
| mssql_scan_params_unsafe | table | mssql_scan_params with the result's shape given instead of described (columns := {'name': 'TYPE', …}): nothing is asked of the server at bind, and the rows are checked against the shape when the statement runs. | NULL | [SELECT * FROM mssql_scan_params_unsafe('db', 'SELECT name FROM sys.objects WHERE object_id > @id', {'id': 100}, columns := {'name': 'VARCHAR'})] |
| mssql_scan_unsafe | table | mssql_scan with the result's shape given instead of described: columns := {'name': 'TYPE', …} is returned at bind without asking the server (no connection, no describe), and the rows are checked against it when the query runs. The caller vouches for the shape; a stream that does not fit it fails. Types are read as by the catalog: tinyint UTINYINT, datetime2(7) TIMESTAMP_NS, a GEOMETRY column must arrive as WKB (STAsBinary()). | NULL | [SELECT * FROM mssql_scan_unsafe('db', 'SELECT object_id, name FROM sys.objects', columns := {'object_id': 'INTEGER', 'name': 'VARCHAR'})] |
| mssql_version | scalar | Returns the version of the mssql extension. | NULL | [mssql_version()] |
| mssql_winsspi_auth_test | scalar | Windows peer of mssql_kerberos_auth_test: builds the SSPI (Negotiate) token for the SQL Server at host and port (default 1433) without connecting to it, and returns the principal, SPN and token size, or the SSPI error. | NULL | [mssql_winsspi_auth_test('sql.example.com', 1433)] |
| mssql_winsspi_auth_test_spn | scalar | mssql_winsspi_auth_test for an explicit service principal name instead of the MSSQLSvc/ |
NULL | [mssql_winsspi_auth_test_spn('MSSQLSvc/sql.example.com:1433')] |
| 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 | typed_value |
|---|---|---|---|---|---|
| ducklake_default_data_inlining_row_limit | Default row limit for data inlining (0 disables inlining) | UBIGINT | GLOBAL | [] | 10 |
| ducklake_default_version | Default DuckLake version for new catalogs | VARCHAR | GLOBAL | [] | NULL |
| ducklake_max_retry_count | The maximum amount of retry attempts for a ducklake transaction | UBIGINT | GLOBAL | [] | 10 |
| ducklake_retry_backoff | Backoff factor for exponentially increasing retry wait time | DOUBLE | GLOBAL | [] | 1.5 |
| ducklake_retry_wait_ms | Time between retries | UBIGINT | GLOBAL | [] | 100 |
| ducklake_target_file_size | Target file size for insertion and compaction | VARCHAR | GLOBAL | [] | NULL |
| ducklake_write_deletion_vectors | [EXPERIMENTAL] Write Iceberg V3 deletion vectors (puffin) instead of positional delete files (parquet) | BOOLEAN | GLOBAL | [] | false |
| mssql_acquire_timeout | Connection acquire timeout in seconds (0 = fail immediately) | BIGINT | GLOBAL | [] | 30 |
| mssql_attach_validation_timeout | Timeout in seconds for the ATTACH-time credential round trip (0 = inherit mssql_connection_timeout). Spec 047 / US2. | BIGINT | GLOBAL | [] | 0 |
| mssql_browser_timeout_seconds | SQL Server Browser UDP query timeout in seconds for named-instance resolution (default: 3) | BIGINT | GLOBAL | [] | 3 |
| mssql_catalog_cache_ttl | Metadata cache TTL in seconds (0 = manual refresh only) | BIGINT | GLOBAL | [] | 0 |
| mssql_catalog_native_types | Report MSSQL_VARCHAR(n)/MSSQL_NVARCHAR(n) for attached string columns instead of VARCHAR (default: true) | BOOLEAN | GLOBAL | [] | true |
| mssql_connection_cache | Enable connection pooling and reuse | BOOLEAN | GLOBAL | [] | true |
| mssql_connection_limit | Maximum connections per attached mssql database | BIGINT | GLOBAL | [] | 64 |
| mssql_connection_timeout | TCP connection and login timeout in seconds (0 = the default, 30) | BIGINT | GLOBAL | [] | 30 |
| mssql_convert_varchar_max | Convert VARCHAR(MAX) to NVARCHAR(MAX) in table scans for UTF-8 compatibility (default: true) | BOOLEAN | GLOBAL | [] | true |
| 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 | [] | 102400 |
| 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 | [] | 0 |
| mssql_copy_tablock | TABLOCK hint for COPY/BCP: 'auto' (by target shape — heap on, anything clustered off), 'true', or 'false' | VARCHAR | GLOBAL | [] | auto |
| mssql_ctas_drop_on_failure | Drop table if CTAS insert phase fails (default: false, table remains for debugging) | BOOLEAN | GLOBAL | [] | false |
| 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 | [] | NVARCHAR |
| mssql_ctas_use_bcp | Use BCP protocol for CTAS data transfer (default: true, 2-10x faster than INSERT) | BOOLEAN | GLOBAL | [] | true |
| mssql_default_string_length | Length for an unannotated VARCHAR column created by CTAS/COPY (0 = MAX, the default) | BIGINT | GLOBAL | [] | 0 |
| mssql_default_table_kind | Shape of a table created by CTAS/COPY: HEAP (default) or COLUMNSTORE | VARCHAR | GLOBAL | [] | HEAP |
| mssql_dml_batch_size | Maximum rows per UPDATE/DELETE batch (default: 500, affects parameter count) | BIGINT | GLOBAL | [] | 500 |
| mssql_dml_max_parameters | Maximum parameters per UPDATE/DELETE statement (SQL Server limit ~2100) | BIGINT | GLOBAL | [] | 2000 |
| mssql_dml_pushdown | Send an UPDATE / DELETE the remote-pushdown rewriter can render to SQL Server as one statement (default: true) | BOOLEAN | GLOBAL | [] | true |
| mssql_dml_stage_threshold | Rows up to which an UPDATE/DELETE on a keyed table is sent as VALUES-join statements; past it the rows go through a #stage table (0 = always stage, default: 1000) | BIGINT | GLOBAL | [] | 1000 |
| mssql_ducklake_async_statistics | Set AUTO_UPDATE_STATISTICS_ASYNC ON on a DuckLake catalog's SQL Server database when the catalog is shaped; a query that finds stale statistics no longer waits for their update | BOOLEAN | GLOBAL | [] | true |
| 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 | [] | true |
| mssql_enable_statistics | Enable statistics collection from SQL Server for query optimizer | BOOLEAN | GLOBAL | [] | true |
| mssql_exec_invalidate_cache | Invalidate the catalog cache after DDL executed via mssql_exec() (CREATE/DROP/ALTER/TRUNCATE/RENAME/EXEC) | BOOLEAN | GLOBAL | [] | false |
| mssql_idle_timeout | Idle connection timeout in seconds (0 = no timeout) | BIGINT | GLOBAL | [] | 300 |
| mssql_insert_batch_size | Maximum rows per INSERT statement (SQL Server limit: 1000) | BIGINT | GLOBAL | [] | 1000 |
| mssql_insert_bcp_threshold | Rows up to which an INSERT is sent as statements rather than a bulk load | BIGINT | GLOBAL | [] | 1000 |
| mssql_insert_max_rows_per_statement | Hard cap on rows per INSERT statement (SQL Server limit: 1000) | BIGINT | GLOBAL | [] | 1000 |
| mssql_insert_max_sql_bytes | Maximum SQL statement size in bytes | BIGINT | GLOBAL | [] | 8388608 |
| mssql_insert_use_bcp | Load INSERT rows through the BCP protocol above mssql_insert_bcp_threshold rows (RETURNING and an explicit identity column always use statements) | BOOLEAN | GLOBAL | [] | true |
| mssql_login7_max_packet | TEST-ONLY: max LOGIN7 TDS packet size in bytes for integrated auth (0 = default 4096) | BIGINT | GLOBAL | [] | 0 |
| mssql_metadata_timeout | Metadata query timeout in seconds (default: 300, 0 = no timeout). Increase for very large catalogs | BIGINT | GLOBAL | [] | 300 |
| mssql_min_connections | Minimum connections to maintain per context | BIGINT | GLOBAL | [] | 0 |
| mssql_named_instance_resolution | Enable SQL Server Browser (UDP 1434) resolution of host\instance connection strings | BOOLEAN | GLOBAL | [] | true |
| mssql_order_pushdown | Enable ORDER BY pushdown to SQL Server (default: false) | BOOLEAN | GLOBAL | [] | false |
| mssql_pushdown_join_rows_threshold | Rows (cached count) from which a remote-pushdown join of uncertain gain stays with the catalog scan (0 = always push) | BIGINT | GLOBAL | [] | 1000000 |
| mssql_pushdown_min_rows | Rows (cached counts, summed over the tables a statement reads) below which remote pushdown leaves it to the catalog scans (0 = no floor) | BIGINT | GLOBAL | [] | 0 |
| mssql_query_timeout | Query execution timeout in seconds (0 = no timeout, default: 30) | BIGINT | GLOBAL | [] | 30 |
| mssql_remote_pushdown | Push whole SELECT statements to SQL Server through DuckDB's remote-pushdown rewriter; read at ATTACH (default: true) | BOOLEAN | GLOBAL | [] | true |
| 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 | [] | true |
| mssql_scan_parameterize_filters | Send pushed filter constants as sp_executesql parameters (one plan per shape) | BOOLEAN | GLOBAL | [] | true |
| mssql_statistics_cache_ttl_seconds | Statistics cache TTL in seconds | BIGINT | GLOBAL | [] | 300 |
| mssql_statistics_level | Statistics detail level: 0=row count, 1=+histogram min/max, 2=+NDV | BIGINT | GLOBAL | [] | 0 |
| mssql_statistics_use_dbcc | Allow DBCC SHOW_STATISTICS for column statistics (requires permissions) | BOOLEAN | GLOBAL | [] | false |
| mssql_tds_packet_size | TDS frame size in bytes requested at login, clamped to [512, 32767] (default: 16384) | BIGINT | GLOBAL | [] | 16384 |
| mssql_test_dml_platform | TEST ONLY: the platform whose DML forms an ATTACH uses ('' = by host, 'sqlserver', 'fabric', 'synapse'); read at ATTACH | VARCHAR | GLOBAL | [] | |
| mssql_test_fail_metadata_after_rows | TEST ONLY: make a metadata query fail after this many rows (0 = off) | BIGINT | GLOBAL | [] | 0 |
| mssql_test_fail_parse_after_tokens | TEST ONLY: put a DML response parser into Error after this many tokens (0 = off) | BIGINT | GLOBAL | [] | 0 |
| mssql_test_force_intersect_join_form | TEST ONLY: match a keyless UPDATE / DELETE through EXISTS (… INTERSECT …) even where the server has IS NOT DISTINCT FROM | BOOLEAN | GLOBAL | [] | false |
| 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 | [] | Latin1_General_100_BIN2_UTF8 |
| 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 | [] | true |
| mssql_warn_non_utf8_collation | Log a warning when a CHAR/VARCHAR/TEXT column arrives with a non-UTF-8 collation, since its bytes are returned verbatim into a DuckDB VARCHAR (default: true) | BOOLEAN | GLOBAL | [] | true |
| temp_directory | Set the directory to which temporary files are written. | VARCHAR | GLOBAL | [] | post.db.tmp |