mssql-extension Development Guidelines

DuckDB extension for SQL Server via a custom TDS protocol implementation (no FreeTDS/ODBC).

Technology

• Language: C++17 (DuckDB extension standard)
• DuckDB: v2.0-cyanoptera, DuckDB's 2.0 branch (2.0 API track, spec 069). Tracked instead of main since the branch was cut on 2026-09-03; since v2.0.0 (2026-10-20) it carries the backports of the 2.0.x line, and the pin follows its head between releases (2026-11-08: 4569c0a1eeb, 880 commits past v2.0.0). A head that is not a release tag builds as -dev and loads into no released DuckDB, so a release waits for the 2.0.x tag (see Versioning). main (2.1-dev) is not tracked: measured 2026-11-08, it is mid-refactor (ClientContext giving way to Context / ExecutionServices, registered_state gone) and table functions default to stability = CONSISTENT_WITHIN_QUERY, which a side-effecting vehicle (mssql_dml) would have to opt out of. The 1.5.x world lives on branch duckdb-v1.5.5 for 0.2.x maintenance releases; main compiles against the pinned duckdb submodule SHA only (no dual-API shim since spec 069 retired mssql_compat.hpp)
• TLS: OpenSSL via vcpkg (statically linked, symbol visibility controlled)
• Platforms: Linux (GCC), macOS (Clang), Windows (MSVC, MinGW/Rtools 4.2)

Project Structure

src/
  azure/                # Azure AD authentication infrastructure
  catalog/              # DuckDB catalog integration + transaction manager
  codec/                # Per-type-family codec layer (spec 045): boolean/integer/
                        # float/decimal/money/string/binary/datetime/uuid. Each
                        # <family>_codec.cpp owns EncodeToBcp / DecodeFromTds /
                        # FormatSqlLiteral / FormatDdlTypeName for its types.
                        # literal_format.cpp + type_family.cpp are the dispatchers.
                        # code_page/ (spec 091): server-generated code-page tables
                        # (tools/code_pages/), the code-page -> UTF-8 kernels, the
                        # UTF-16 stage 1 (DecodeToUtf16) and the collation lookups
                        # (by CollationId, by name, by wire key -> code page).
  connection/           # Connection pooling, provider, settings
  dml/                  # DML operations
    insert/             # INSERT (batched VALUES, OUTPUT INSERTED for RETURNING)
    update/             # UPDATE (rowid-based, VALUES JOIN pattern)
    delete/             # DELETE (rowid-based, VALUES JOIN pattern)
  include/              # Headers (mirrors src/ layout)
  query/                # Query execution and result streaming
  table_scan/           # Table scan with filter/projection pushdown
  tds/                  # TDS protocol implementation
    auth/               # Authentication strategies (SQL auth, FEDAUTH)
    encoding/           # Type encoding (datetime, decimal, GUID, UTF-16)
    tls/                # TLS via OpenSSL with custom BIO callbacks
test/
  sql/                  # SQLLogicTest files (require SQL Server for most)
    attach/             # ATTACH/DETACH tests
    azure/              # Azure AD authentication tests (no SQL Server required)
    catalog/            # Catalog, DDL, filter pushdown, statistics
    copy/               # COPY TO MSSQL (BulkLoadBCP) tests
    ctas/               # CREATE TABLE AS SELECT tests
    dml/                # UPDATE and DELETE tests
    insert/             # INSERT tests
    integration/        # Core integration (pool, TLS, large data)
    query/              # Query-level tests
    rowid/              # Rowid pseudo-column tests
    tds_connection/     # TDS protocol tests
    transaction/        # Transaction management tests
  cpp/                  # C++ unit tests (no SQL Server required)
docs/                   # Architecture documentation (see docs/architecture.md)
docker/                 # SQL Server container and Linux CI build
tools/
  code_pages/           # Spec 091: generate.py asks a SQL Server how it reads every
                        # code-page byte (data/, committed); emit_tables.py checks the
                        # answers and writes src/codec/code_page/code_page_tables.cpp

Versioning

• The extension version is defined in CMakeLists.txt as MSSQL_EXTENSION_VERSION (e.g., set(MSSQL_EXTENSION_VERSION "0.1.10"))
• This is passed to C++ code via the MSSQL_VERSION compile definition and returned by mssql_version()
• When releasing a new version: update MSSQL_EXTENSION_VERSION in CMakeLists.txt and version in vcpkg.json
• DuckDB's own label: DuckDB 2.0's CMake calls every build v<X.Y>.0-dev<N> unless OVERRIDE_GIT_DESCRIBE is set, even on a release tag's commit. A -dev build looks for extensions under its source id, where DuckDB publishes nothing for a release, and its loadable loads into no released DuckDB. So when the duckdb pin IS a release tag (scripts/ci/duckdb_release_tag.sh), the build is labelled with it: checkout_duckdb.sh exports it in CI and release, and the Makefile sets it locally from the submodule's local tags (git -C duckdb fetch --tags after a bump). The release notes then say that the binaries load into a stock DuckDB of that version.

Commands

# Build
make                    # Release build (with TLS via vcpkg)
make debug              # Debug build
make clean              # Remove build artifacts

# Test
make test               # Unit tests (no SQL Server required)
make docker-up          # Start SQL Server container (no .env needed; `cp .env.example .env` to override port/password)
make integration-test   # Integration tests (requires SQL Server)
make test-all           # All tests
make test-debug         # Tests with debug build
make test-code-page     # Code-page kernels vs the committed server data (spec 091; no
                        # SQL Server, no DuckDB); test-code-page-portable = SWAR form
make bench-code-page    # Code-page kernel microbenchmark (manual, not in CI)
make code-pages-check-wire   # Every collation's wire key against a live server (spec 092 D1)
make bench-code-page-column  # One 2048-row column, staging to vector: DecodeColumn vs today's
                        # NVARCHAR path (needs a release build; CELL=thai filters cells)

# Docker
make docker-up          # Start SQL Server test container
make docker-down        # Stop container
make docker-status      # Check container health

# Load extension in DuckDB CLI
./build/release/duckdb
# Or dynamically:
duckdb --unsigned -c "INSTALL mssql FROM local_build_debug; LOAD mssql;"

Code Style

• C++17, follow DuckDB extension conventions
• Use clang-format (version 14): find src -name '*.cpp' -o -name '*.hpp' | xargs clang-format -i

Naming Conventions

Files

• Source files: mssql_<component>.cpp for extension code, tds_<component>.cpp for TDS protocol
• Headers mirror source layout in src/include/
• Test files: test_<component>.cpp (C++), <feature>_<scenario>.test (SQL)

Namespace Prefix Rule

Critical: The MSSQL or Tds prefix depends on namespace placement:

Classes and Structs

• PascalCase always. Prefix per namespace rule above.
• Info/metadata structs: PrimaryKeyInfo, ColumnInfo, TableMetadata, PoolStatistics
• Config structs: InsertConfig, DMLConfig, PoolConfig

Methods

• PascalCase (DuckDB convention): GetConnection(), SetPinnedConnection(), ExecuteBatch()
• Getters: Get<Property>() — Setters: Set<Property>()
• Boolean queries: Is<State>(), Has<Property>() (e.g., IsAlive(), HasPinnedConnection())

Variables

• Member variables: snake_case with trailing underscore: connection_pool_, schema_mutex_, transaction_descriptor_[8]
• Local variables: snake_case without underscore: row_count, schema_name
• Constants: constexpr with UPPER_SNAKE_CASE: TDS_VERSION_7_4, TDS_HEADER_SIZE, DEFAULT_IDLE_TIMEOUT

Enums

• enum class with PascalCase values: ConnectionState::Idle, PacketType::SQL_BATCH, MSSQLCacheState::LOADED
• All-caps for protocol constants: DONE_FINAL, ENCRYPT_ON

Namespaces

• duckdb — main extension entry points and DuckDB API overrides (prefixed)
• duckdb::tds — TDS protocol layer (no prefix)
• duckdb::tds::tls — TLS implementation (no prefix)
• duckdb::mssql — MSSQL-specific utilities (no prefix)
• duckdb::encoding — Type encoding/decoding (no prefix)

Test Naming

• SQL test files: # group: [sql], [mssql], [integration], [transaction], [dml]
• SQL test context names: unique per file, prefixed by operation (e.g., txtest, mssql_upd_scalar)
• Test tables: PascalCase (TestSimplePK, TxTestOrders) or snake_case for simple ones (tx_test)

Documentation

• DATAMODEL.md is the layered-architecture reference (TDS → connection pool → catalog → cache → codec) with Mermaid diagrams. On every PR, consider whether DATAMODEL.md needs updating — if the change adds/removes a layer component, alters an invariant (lifetime, cache invalidation, threading, ownership), or changes an end-to-end flow, update the relevant layer section (and its diagram) in the same PR.
• Keep architecture/internals diagrams in DATAMODEL.md; keep README.md user-facing (link into DATAMODEL.md rather than duplicating internals).
• Use GitHub-rendered mermaid fenced blocks for diagrams.

Key Architecture Concepts

• Custom TDS implementation: No external TDS/ODBC library. TDS v7.4 protocol with PRELOGIN, LOGIN7, SQL_BATCH, RPC, ATTENTION packet types. A request is a tds::Request (tds/tds_request.hpp): batch text, or an RPC body (tds::RpcRequestBuilder, tds/tds_rpc.hpp) with the batch text beside it for logs and errors; TdsConnection::ExecuteRequest sends either through one path (descriptor, RESET_CONNECTION, fragmentation).
• Connection pool: Thread-safe pool, owned per MSSQLCatalog (spec 047; since the issue #178 review a shared_ptr whose SOLE strong reference is the catalog — result streams hold weak_ptr handles for safe destructor-time release) — pool lifetime is bounded by catalog lifetime; no singleton, no cross-instance sharing. Two ATTACHes against the same DSN under different aliases get independent pools; DETACH tears down the pool deterministically via RAII. Idle timeout, background cleanup, configurable limits as before.
• Transaction support: Connection pinning maps DuckDB transactions to SQL Server transactions via 8-byte ENVCHANGE descriptors -- explicit transactions, and since issue #451 an autocommit statement that writes to the catalog (UPDATE / DELETE / MERGE; INSERT / CTAS when their use_bcp setting is off), which runs as a transaction of one statement. See docs/transactions.md.
• Catalog integration: DuckDB Catalog/Schema/Table APIs with incremental metadata cache (lazy loading, TTL-based expiration, point invalidation; the shared cache holds committed state only — inside an explicit transaction a miss loads on the pinned connection into the transaction's own MSSQLTransactionMetadata, the names the transaction changed are forgotten in the shared cache at COMMIT/ROLLBACK, and mssql_refresh_cache / mssql_preload_catalog are refused once the transaction has used any MSSQL catalog; issue #380; at COMMIT/ROLLBACK what the transaction loaded and did not change is published into the shared cache from memory, with no round trip (PublishTransactionMetadata; withheld after mssql_exec DDL, under READ UNCOMMITTED, for filter-hidden names, and entirely when the shared cache was invalidated while the transaction ran -- GetInvalidationEpoch, so any DDL through the catalog withholds it); inside a transaction the shared metadata cache is read into the transaction's own layer, and a miss there is never "does not exist"; issue #383, review of #386), rowid key discovery for rowid support — the primary key if it is usable, else a usable unique index (spec 077 W1: not filtered, not disabled, key columns NOT NULL and matchable by a literal; a SQL_VARIANT, time(7) or datetimeoffset(7) key is not, #354/#358 -- a DATETIME key is since spec 080 W3, compared in its own type --, and such a table falls through to another unique index or refuses by name).
• Read path (spec 075): mssql_scan describes at Bind and executes at InitGlobal; in a transaction every scan of a catalog materialises at init under the pinned connection's materialize lock (MSSQLCatalog::MaterializeMutexFor: the transaction's own, so other DuckDB connections' transactions run in parallel -- issue #409; the catalog's in autocommit on a pool of one), and the planner also materialises the catalog scans of any catalog the plan sinks into (COPY's BulkLoadBCP, INSERT, CTAS, and since spec 080 PR 1 UPDATE / DELETE / MERGE) — the sink and the scan feeding it share the ONE pinned connection. The per-table metadata queries (columns, PK, row count, table list) send the names as sp_executesql parameters, so one server plan serves every table (#334); since spec 076 the primary key rides in the same batch as the table's metadata, so a fresh table costs one round trip before its first row. Since spec 084 every metadata load computes a table's row count once per OBJECT (it used to sit beside the columns, evaluated per column row): a first touch is one batch of three result sets (object row, columns, keys, routed by ordinal); the bulk loads (preload, listing, mssql_refresh_cache; one function, LoadObjectsAndColumnsLocked) are objects / columns / keys, grouped on object_id, with the whole-catalog row counts from one sys.dm_db_partition_stats pass into @rc when unfiltered and permitted (VIEW DATABASE STATE; not on Fabric/Synapse, not in a transaction). Every load that publishes columns carries the rowid key, so a table entry is born with it (columns LOADED => pk_loaded, asserted) and nothing discovers it lazily.
• DML: an UPDATE / DELETE / INSERT … SELECT the remote-pushdown rewriter can write in T-SQL goes to the server whole as SELECT * FROM mssql_dml(…) (spec 080 PR 3c / 3d, mssql_dml_pushdown); the rest takes the paths below. The row-by-row INSERT / UPDATE / DELETE batches, INSERT … OUTPUT and the pushed DML read their response through ONE function, mssql::ExecuteDmlBatch (dml/mssql_dml_execute.hpp, spec 080 acceptance 7: five copies of the token loop before it; the staged path's #stage / JOIN statements, IDENTITY_INSERT and BEGIN / COMMIT still use MSSQLSimpleQuery): mssql_query_timeout per statement (it was a fixed 30 s; a pushed DML has none), 1 s slices that ask ClientContext::IsInterrupted (Ctrl-C cancels any of them; a read cut short by a signal is retried, not a timeout), the parse lever, the first ERROR as the cause, INFO tokens to the log, the socket drained to EOM after a server error, a desync, a timeout or an interrupt; a failed statement is a status, not an exception -- the caller words it and fails its statement. INSERT loads through INSERT BULK above mssql_insert_bcp_threshold rows (spec 062; staged in a MSSQLStagedRows buffer until the threshold decides, then the operator's BulkLoadSession on the pinned or a pool connection, parallel writers where locks allow) and as batched VALUES statements otherwise; UPDATE/DELETE use the rowid key (primary key, else a usable unique index) in VALUES JOIN statements up to mssql_dml_stage_threshold rows (held in MSSQLStageSwitch until the threshold decides), and past it -- and always for a table with no usable key, keyed by every column (spec 080 D3 rung 3: hidden virtual key columns at MSSQL_KEYLESS_KEY_START, catalog/mssql_keyless_key.hpp; refused by name unless the plan under the DML is projections over the table's own scan with every filter on the server and nothing volatile, and for a column that cannot be a key -- IsRoundTripExactForKey, shared with rung 2) -- through a session #stage_<uuid> filled by INSERT BULK on the statement's connection and ONE UPDATE/DELETE … JOIN #stage (MSSQLStagedDml, never batched; string key columns also matched by bytes; nullable ones by IS NOT DISTINCT FROM on 2022+ / Azure SQL, else EXISTS (… INTERSECT …)). A DATETIME key is compared in its own type (#358). The platform's DML forms are DmlCapabilities (spec 080 D0: host test, EngineEdition 6 = Synapse; Synapse refuses UPDATE/DELETE, its keys are NOT ENFORCED; Fabric, with no UPDATE/DELETE … FROM … JOIN, always stages and matches through WHERE EXISTS / a correlated TOP (1) subquery -- join_by_subquery -- not MERGE, whose 8672 would need a collation-folding DISTINCT). A MERGE whose source is in the catalog goes to the server whole since spec 080 PR 4a (NodeWriter::WriteMerge, mssql_dml kind 'merge'; T-SQL's shape only -- two WHEN MATCHED (UPDATE + DELETE, the first conditional), one NOT MATCHED INSERT, two NOT MATCHED BY SOURCE (UPDATE + DELETE, the first conditional), a trailing DO NOTHING dropped; a NOT MATCHED BY TARGET action is bound against the source alone and a BY SOURCE one against the target alone, as DuckDB binds them (merge_forbidden_first_/last_: an unqualified name skips the other side, a qualified one naming it is vetoed; USING (c) maps c to the side the rows have); no subquery in a MERGE expression, no join source, no identity column in the INSERT); every other MERGE is staged (PR 4b, dml/merge/): MSSQLOptimizer's pre-optimize hook (PreOptimizeRoot) replaces the binder's LogicalMergeInto with MSSQLLogicalStagedMerge (mssql::RewriteMerge) before DuckDB's optimizers take the join apart -- the join and the target's scan go, ON = the join's conditions + the filters the binder pushed onto the target's side, each action expression inlined through the binder's projection and split: a largest source-only part (any DuckDB function) becomes a #src column DuckDB computes under a projection over the untouched source, a part over no column is folded, what touches the target is written in T-SQL by FilterEncoder with a ColumnResolver for [t].[col] / [s].[cN] and widening_casts (an exact numeric widening cast as CAST(… AS …)), and the binder's assignment cast (integer / string) is dropped where the value reads the target; a source value compared directly with a target column is a like-target #src column (the target's type and collation, strings / binaries widened to MAX, a code-page char / varchar as nvarchar(max) so it compares in Unicode, rowversion as binary(8); a value between a temporal column's steps is rounded onto them, as the server compares), any other CTAS-typed (strings nvarchar(max) / varchar(max) COLLATE DATABASE_DEFAULT); a target column ON compares with the source must pass IsRoundTripExactForKey; the shapes are CheckMergeShape's (shared with 4a). MSSQLStagedMerge creates #src by SELECT … INTO … FROM (SELECT 1) d LEFT JOIN t ON 1 = 0 WHERE 1 = 0 UNION ALL … (measured: through the join alone an identity column came out NOT NULL IDENTITY), fills it through MSSQLSessionStage (D3's stage, shared with MSSQLStagedDml), runs the MERGE through ExecuteDmlBatch with no deadline, drops #src, commits; an empty source sends nothing unless a BY SOURCE clause exists. Refused by name, nothing sent: ERROR, RETURNING, an identity column in the INSERT, a subquery, a per-row value in BY SOURCE, Synapse, and (MSSQLCatalog::PlanMergeInto) any MERGE with DuckDB's optimizer off -- there is no native MERGE for a catalog table. A table with no usable key binds (GetRowIdColumns returns none; UPDATE / DELETE are refused by name at planning, PlanKeylessDml), so a MERGE into it runs. The VALUES batches are sent as the rows arrive in a transaction too: UPDATE / DELETE / a staged MERGE count as sinks, so the scan feeding them is materialised, and each batch on the pinned connection holds the transaction's materialize lock (MSSQLStatementConnection::LockPinned); RequireIdle refuses a batch by name when the connection is still busy; the optimizer that materialises the scan is skipped under SET enable_optimizer = false, so PlanUpdate / PlanDelete look for an unflagged catalog scan of the same catalog under the DML when the connection is shared (FeedsFromStreamingScan) and then hold the rows until Finalize -- defer_to_finalize on the VALUES path, hold_until_finalize on the staged one (review of #423) (spec 080 PR 1; until then they deferred every batch to Finalize). UPDATE / DELETE … RETURNING go through the staged operator whatever the row count (spec 080 PR 2b): the JOIN statement carries OUTPUT inserted.* / deleted.* INTO #out_<uuid> (created like the stage), read back with MSSQLColumnInfo::BuildReadExpression through a MSSQLResultStream into the logical operator's types -- a DELETE's RETURNING chunk appends the table's virtual columns in GetVirtualColumns() order (the rowid, scalar or STRUCT, or rung 3's hidden key columns); refused by name without output_into_table / a stage (Fabric, Synapse) and for DuckDB's capture_old_rows. An autocommit UPDATE / DELETE / MERGE commits and rolls back with DuckDB (issue #451): it runs on its catalog as a transaction of one statement, as postgres_scanner / mysql_scanner run every DuckDB transaction -- the sinks' GetGlobalSinkState (and mssql_dml's InitGlobal) call ConnectionProvider::JoinStatementTransaction, and so does every scan of that catalog at the top of its InitGlobal (joins_statement, set by MSSQLOptimizer -- DuckDB may initialise a join's build side or a MERGE's source before the sink, so whichever comes first pins; a pinned connection is recognised by identity, IsPinnedConnection, so an unmarked scan's pool connection still goes back), MSSQLTransaction::IsStatementJoined makes IsInTransaction / GetConnection / MaterializeMutexFor answer as in a transaction (one pinned connection, scans of that catalog materialised -- MSSQLOptimizer's joining catalogs), and MSSQLTransactionManager::CommitTransaction / RollbackTransaction end the server transaction (COMMIT / ROLLBACK read, not drained; no I/O under transaction_lock_; a server-refused ROLLBACK keeps the connection only when @@TRANCOUNT is 0). So an error after the write, Ctrl-C, or a result DuckDB aborts (QueryResult::Close before the query ended, DuckDB ef5b57f94ad) undoes it. An INSERT joins under mssql_insert_use_bcp = false, a CTAS under mssql_ctas_use_bcp = false (decided at planning: MSSQLInsertConfig::join_statement_transaction, CTASConfig::use_bcp); a COPY never. Error wording follows MSSQLStatementConnection::InOpenTransaction (an explicit transaction), not IsPinned. Otherwise every DML statement is atomic in autocommit (spec 062 W1c/W2, #344): one connection per statement (MSSQLStatementConnection), its batches bracketed in a server transaction by mssql::LoadTransaction, which captures the ENVCHANGE transaction descriptor a batch-sent BEGIN TRANSACTION answers with (every later request must carry it — error 3989 otherwise) and clears it at COMMIT/ROLLBACK. An INSERT that names the identity column (spec 077 W2) is bracketed on that same connection with SET IDENTITY_INSERT … ON before its first batch and OFF on every way out — COMMIT, Fail, the destructor — and a connection on which OFF was not confirmed is discarded rather than pooled; it stays on the statement path (tens of rows — loading many rows with their identity values is COPY's job, which keeps a source column named like the identity column). A NULL in a named identity column is refused before anything is sent: DuckDB hands DEFAULT and NULL to the extension identically. INSERT … RETURNING sends a bare OUTPUT INSERTED (MSSQLReturningParser keeps only the batch's LAST result set, so a trigger's own result set is not taken for RETURNING rows); refused with 334 beside an enabled trigger (a compile error: nothing ran, the statement's transaction intact), the batch is rebuilt from its kept literals (MSSQLInsertBatch::row_literals) as SELECT … INTO [#mssql_returning_<uuid>] / INSERT … OUTPUT … INTO it / SELECT * / DROP in one request, and MSSQLBatchBuilder::UseOutputInto builds the statement's later batches that way -- only where DmlCapabilities::output_into_table (SQL Server; issue #471). The table is typed by MSSQLColumnInfo::BuildReturningExpression, shared with the staged UPDATE / DELETE … RETURNING: the read expression, but rowversion as binary(8) (an OUTPUT cannot write a timestamp column) and char(n) as a trimmed NVARCHAR (the read's NCHAR(n) doubles a fixed width past the 8060-byte row). Not the default: a session table per batch measured 1.2 s for 10000 rows against 0.09 s bare.
• DDL: CREATE TABLE IF NOT EXISTS silently succeeds when table exists; CREATE OR REPLACE TABLE drops and recreates. Auto-TABLOCK enabled for new table creation (CTAS/COPY TO). Catalog DDL and CTAS run on a pool connection and autocommit, outside an explicit transaction (spec 057) — except on a pool of ONE connection (issue #380), where a CTAS in a transaction runs whole on the pinned connection (checks, CREATE, rows as INSERT statements) and rolls back with it, and so does any catalog DDL there (MSSQLCatalog::ExecuteDDL, issue #419: it used to wait out mssql_acquire_timeout for a second connection); on a pool of one in autocommit the optimizer materialises the scans of a catalog the plan sinks into (CTAS counts as a sink, like COPY and INSERT) as it does in a transaction, a raw mssql_scan / mssql_scan_params of it materialises at init the same way, and a COPY's / CTAS's bulk load keeps BCP but takes its connection on its FIRST chunk (BulkLoadSession::DeferAdoption / AdoptDeferred), after the source has given the connection back (review of #382: CTAS used to fall back to INSERT statements, and COPY and raw scans waited out the acquire timeout).
• ORDER BY / TOP pushdown (MSSQLOptimizer, opt-in mssql_order_pushdown): removes DuckDB's sort, so a key is pushed only when MSSQLColumnInfo::OrdersLikeDuckDB() holds — an allow-list (numeric, bit, date/time types, not datetime2(7), which the codec reads as TIMESTAMP_NS and NULLs past 2262; no string, no nvarchar, binary, uniqueidentifier, json, sql_variant; #362) — or, under a LIMIT only, OrdersLikeDuckDBAsBytes(): a bounded UTF-8 varchar (not char(n), stored padded and read trimmed; not MAX) ordered as CAST(col AS varbinary(n)) (as text the server pads with spaces: ab+TAB before ab; as bytes the only residual is a trailing NUL tie; not sargable, hence LIMIT only). A string-valued function key, and a date part of a datetimeoffset, are never pushed. A partial key list pushes nothing (the DuckDB sort stays anyway). Filter pushdown maps no string function (lower/upper/trim/length run client-side: the server's case mapping is not DuckDB's, and a pushed upper(x) = 'ẞ' lost rows) and no date part of a TIMESTAMP WITH TIME ZONE. A nullable key whose NULL placement differs from the server's (NULL lowest) is pushed only under a LIMIT, with a leading CASE WHEN k IS NULL … key; a plain ORDER BY keeps DuckDB's sort. TOP N is not pushed while any non-optional filter stays client-side (the server would take N rows before it). Spec 079's remote-pushdown writer is to use the same predicate (D4). Every identifier the extension sends goes through mssql::QuoteIdentifier (query/mssql_identifier.hpp, self-contained); a name inside a string literal through mssql::NVarcharLiteral (query/mssql_sql_params.hpp).
• Filter pushdown: DuckDB filter expressions translated to T-SQL WHERE clauses. A DATE / TIMESTAMP / TIME constant SQL Server cannot hold ('infinity', a year past 9999, TIME '24:00:00'; codec::datetime::ServerHoldsTemporal) is never pushed, rowid = included -- the filter runs in DuckDB -- and the BCP / RPC encoders refuse such a value by name (BCPRowEncoder::RequireServerDay / RequireFiniteTimestamp / RequireServerTime, and a 9999-12-31 value that rounds into the next day at the column's scale), as does the statement path's literal (FormatSqlLiteral); the error names the column (ScatterChunk* / EncodeToBcp add it, and since #460 so do the statement path's literals -- MSSQLValueSerializer::Serialize(…, column) for INSERT VALUES and the UPDATE / DELETE VALUES join -- and the parameters, (parameter @d), and the #stage names the target's column through BCPColumnMetadata::reported_name, not its own n0; one helper, codec::ThrowNamingColumn). The three-byte day count used to wrap it: 'infinity' to 1969-12-31, TIMESTAMP_NS ±infinity to 2262-04-11 (#447). Function mapping for common string/date/arithmetic operations. Since spec 076 the constants are @pN parameters of sp_executesql, declared from the column's SQL Server type (FilterEncoder::DeclarationForColumn), and the statement text is one fixed string per filter shape; since spec 083 the call is a TDS RPC request with the values as typed parameters (SqlParamSet::ExecuteSqlRequest, encoded from the declared type through the BCP value encoders), not a DECLARE …; EXEC sp_executesql batch, whose outer text the server compiled on every call (0.26-0.29 vs 0.06-0.07 ms server CPU per scan). A declaration with no RPC encoding (CLR, alias, sql_variant), or a value its declared type cannot take, keeps the batch form for the whole call. mssql_scan_parameterize_filters = false restores literals. A constant compared with a datetime column is the 1/300 s tick that decides the comparison, declared datetime (= / <> / IN the tick read as it, > / <= the tick at or below, >= / < at or above; DateTimeEncoding::DatetimeTickBound, the inverse of the read's truncation), so a value read back finds its row (#426: 200 of 603 did); where no tick decides it, a datetime2 value on the right side of every datetime at any compatibility level (below 130 the server converts a datetime to datetime2 at the millisecond). A datetime column against anything but constants and other datetime columns -- a datetime2 column, a CASE over it, a constant with it in its IN list -- is not pushed (LossyOperandsPushable). time(7) / datetimeoffset(7) are read with the 100 ns digit truncated (not one-to-one), so a comparison with a constant is rewritten over the range of stored values read as it (TruncatedReadComparison: = is [c, c + 1 us), > is >= c + 1 us, <= is < c + 1 us; at the last microsecond c + 1 us is never sent -- k <> k / k = k), and against any other column they are not pushed. TIME_NS would keep the digit but on the 2.0 pin it neither compares with TIME nor takes INTERVAL / strftime / date_trunc, so time(7) stays TIME.
• DuckDB API (2.0 track, spec 069): main targets duckdb main only — mssql_compat.hpp (spec 051) is retired; 1.5.x compatibility lives on branch duckdb-v1.5.5. The 2.0 contracts that bit during migration and MUST be honored by new code: producers filling vectors call DataChunk::SetChildCardinality (vectors carry their own size; the deprecated SetCardinality sets only the chunk count and every consumer keyed on Vector::size() sees an empty vector); scalar functions that throw at runtime declare SetFallible(); writes go through FlatVector::GetDataMutable/ValidityMutable (checked in release — hoist out of row loops; the per-value DecodeFromTds bodies use the *Unsafe forms, justified in specs/069-duckdb-v2-migration/bench-results.md); every TableFilter handed to a filter_pushdown scan must be applied by the scan (DuckDB does not re-check — refused filters run client-side via the net in table_scan.cpp); Identifier stays at the DuckDB API boundary, internal metadata/TDS/SQL text remain std::string.

Windows Build Support

• ssize_t: src/include/tds/tds_platform.hpp provides Windows-compatible typedef
• MSVC: x64-windows-static-release vcpkg triplet
• MinGW: x64-mingw-static triplet (Rtools 4.2, not 4.3 due to linker bugs)
• CI: Trigger Windows builds via Actions -> CI -> Run workflow -> Check "Run Windows build jobs"

Debug Environment Variables

Extension Settings (SET in DuckDB)

ATTACH Options & Secret Parameters (Catalog Filters)

Available in: ATTACH options, ADO.NET connection strings (SchemaFilter/TableFilter), URI query parameters, and MSSQL secrets. ATTACH options override secret/connection string values. A boolean ATTACH option (lazy_validation, catalog, order_pushdown, remote_pushdown, native_types, client_transcode, preload) also takes a string — 'true'/'false', 'yes'/'no', '1'/'0', DuckDB's own boolean cast — because DuckLake's METADATA_PARAMETERS is a MAP(VARCHAR, VARCHAR) and can send nothing else (issue #325).

Extension Functions

Every scalar and table function is registered through mssql::RegisterDocumentedFunction (src/mssql_function_docs.cpp, issue #371), which gives it the description, examples and category duckdb_functions() reports, and names the scalar parameters in the signature. Those names are API: the binder matches named arguments against them (mssql_exec(context := 'db', sql := '...')), so renaming one breaks callers. test/sql/mssql_function_docs.test fails a function registered without documentation. A table function's positional parameters stay col0, col1, … in duckdb_functions(): DuckDB names them by position whatever the registration says.

Active Technologies

• C++17 (DuckDB extension standard) + DuckDB (main branch), OpenSSL (vcpkg), Winsock2 (Windows system library) (019-fix-winsock-init)
• C++17 (DuckDB extension standard) + DuckDB (main branch), existing TDS layer (specs 001-019) (020-multi-statement-scan)
• In-memory (result streaming, connection pool state) (020-multi-statement-scan)
• C++17 (DuckDB extension standard) + DuckDB (main branch), OpenSSL (vcpkg), existing TDS protocol layer (022-mssql-ctas)
• SQL Server 2019+ (remote), in-memory (result streaming, connection pool state) (022-mssql-ctas)
• C++17 (DuckDB extension standard) + DuckDB (main branch), OpenSSL (via vcpkg for TLS) (023-pool-stats-validation)
• In-memory (connection pool state, metadata cache) (023-pool-stats-validation)
• C++17 (DuckDB extension standard) + DuckDB (main branch), TDS BulkLoadBCP protocol (0x07), OpenSSL (via vcpkg for TLS) (024-mssql-copy-bcp)
• SQL Server 2019+ (remote target), in-memory (batch buffering, connection pool state) (024-mssql-copy-bcp)
• SQL Server 2019+ (remote target), in-memory (connection pool state) (025-bcp-improvements)
• C++17 (DuckDB extension standard) + DuckDB (main branch), existing TDS protocol layer, OpenSSL (vcpkg) (026-varchar-nvarchar-conversion)
• SQL Server 2019+ (remote target), in-memory (batch buffering) (027-ctas-bcp-integration)
• C++17 (DuckDB extension standard) + DuckDB (main branch), OpenSSL (vcpkg for TLS), libcurl (vcpkg for OAuth2 HTTP), DuckDB Azure extension (runtime, for Azure secret management) (001-azure-token-infrastructure)
• In-memory (token cache, no persistence required) (001-azure-token-infrastructure)
• C++17 (DuckDB extension standard) + DuckDB (main branch), OpenSSL (vcpkg), libcurl (vcpkg for Azure OAuth2) (031-connection-fedauth-refactor)
• In-memory (connection pool state, token cache) (031-connection-fedauth-refactor)
• C++17 (DuckDB extension standard, but C++11 compatible for ODR) + libcurl (OAuth2 HTTP), OpenSSL (TLS), DuckDB Azure extension (secret management) (032-fedauth-token-provider)
• In-memory token cache (TokenCache singleton) (032-fedauth-token-provider)
• C++17 (C++11-compatible for ODR with DuckDB) + DuckDB (main branch), OpenSSL (vcpkg), TDS protocol layer (033-fix-catalog-scan)
• In-memory metadata cache (MSSQLMetadataCache) (033-fix-catalog-scan)
• C++17 (DuckDB extension standard, C++11-compatible for ODR on Linux) + DuckDB v1.5-variegata (6,275 commits ahead of v1.4.4), OpenSSL (vcpkg), libcurl (vcpkg) (034-duckdb-v15-upgrade)
• N/A (remote SQL Server via TDS protocol) (034-duckdb-v15-upgrade)
• C++17 (C++11-compatible for ODR on Linux) + DuckDB v1.5-variegata (extension API) (035-ddl-schema-support)
• C++17 (C++11-compatible for ODR on Linux) + DuckDB v1.5-variegata + DuckDB extension API, OpenSSL (vcpkg), libcurl (vcpkg) (036-azure-token-docs)
• C++17 (C++11-compatible for ODR on Linux) + DuckDB (main branch), OpenSSL (vcpkg), cpp-httplib (bundled in DuckDB third_party) (037-replace-libcurl-httplib)
• N/A (in-memory token cache, no change) (037-replace-libcurl-httplib)
• C++17 (C++11-compatible for ODR on Linux) + DuckDB (main branch), OpenSSL (vcpkg), existing TDS protocol layer (039-order-pushdown)
• C++17 (C++11-compatible for ODR on Linux) + DuckDB (main branch), OpenSSL (vcpkg), custom TDS protocol layer (040-fix-datetimeoffset-nbc)
• 042-integrated-authentication: Added integrated authentication (Kerberos on POSIX, SSPI on Windows)
  • POSIX: system GSSAPI (libgssapi_krb5 on Linux, GSS.framework on macOS via -framework GSS)
  • Windows: secur32.dll (Phase 4 — not yet implemented)
  • Connection-string keys (verbatim from microsoft/go-mssqldb): authenticator, krb5-configfile, krb5-keytabfile, krb5-credcachefile, krb5-realm, service_principal_name
  • Aliases: Trusted_Connection=yes, Integrated Security=SSPI/true

Azure AD Authentication

The extension supports Azure AD authentication for Azure SQL Database and Microsoft Fabric. Authentication is implemented using DuckDB's bundled cpp-httplib (with OpenSSL) for OAuth2 token acquisition (no Azure SDK or libcurl dependency).

Supported methods:

• Service Principal: Client credentials flow with tenant_id, client_id, client_secret
• Azure CLI: Uses az account get-access-token for developers with az login
• Device Code Flow: Interactive authentication for MFA-enabled accounts

Implementation files:

• src/azure/azure_http.cpp - HTTP client wrapper (single httplib compilation unit)
• src/azure/azure_token.cpp - OAuth2 token acquisition
• src/azure/azure_device_code.cpp - RFC 8628 device code flow
• src/azure/azure_secret_reader.cpp - Reads Azure secrets from DuckDB Azure extension
• src/azure/azure_test_function.cpp - mssql_azure_auth_test() function

See AZURE.md for user documentation.

Integrated Authentication (Kerberos / SSPI)

POSIX Kerberos and Windows SSPI integrated authentication, shipped via spec 042.

Supported credential modes (POSIX):

• CredCache (default): uses kinit ticket from KRB5CCNAME / /tmp/krb5cc_<uid>. Works on Linux and macOS.
• Keytab: krb5-keytabfile=/path/to.keytab + User Id=svc@REALM. Linux only (MIT Kerberos extensions required).
• Raw: secret-only — cleartext passwords are never accepted from a connection string. Linux only.

Implementation files:

• src/include/tds/auth/iauthenticator.hpp — three-method interface (InitialBytes / NextBytes / Free), modeled on microsoft/go-mssqldb's integratedauth.IntegratedAuthenticator
• src/tds/auth/krb5_authenticator.{hpp,cpp} — GSSAPI implementation (POSIX, compiled when MSSQL_ENABLE_KRB5 is defined). All gss_*/krb5_* calls go through the runtime shim (spec 053).
• src/tds/auth/gssapi_runtime.{hpp,cpp} — spec 053 (#161): lazy dlopen/dlsym loader for GSSAPI/krb5. On Linux the extension carries NO link-time (DT_NEEDED) dependency on libgssapi_krb5.so.2/libkrb5.so.3; the library is loaded on first authenticator=krb5 use (thread-safe via std::call_once). Throws Krb5RuntimeUnavailable (names the missing .so + install package) when absent. macOS fills the table with direct &gss_* addresses (system framework, always present). CMake keeps the GSSAPI/krb5 include dirs but drops target_link_libraries on Linux.
• src/tds/auth/winsspi_authenticator.{hpp,cpp} — Windows SSPI implementation via secur32.dll Negotiate package (compiled when MSSQL_ENABLE_SSPI is defined; CMake auto-enables on _WIN32)
• src/include/tds/auth/integrated_auth_strategy.hpp — adapter wrapping IAuthenticator in the existing AuthenticationStrategy interface
• src/tds/auth/auth_strategy_factory.cpp — AuthStrategyFactory::Create dispatches KRB5 / WINSSPI based on info.auth_method
• src/tds/tds_connection.cpp AuthenticateIntegrated() — SPNEGO continuation loop on 0xED SSPI tokens
• src/tds/tds_protocol.cpp BuildLogin7WithSSPI + BuildSSPIMessage — LOGIN7 with fIntSecurity bit (0x80) + SSPI Message packet type 0x11
• src/connection/mssql_pool_manager.cpp GetOrCreatePoolWithIntegratedAuth — pool factory builds a fresh authenticator per connection so kinit-refreshed tickets are picked up
• src/tds/auth/krb5_test_function.cpp — registers mssql_kerberos_auth_test(host[, port]) and mssql_kerberos_auth_test_secret(secret_name) scalar functions. Mirrors mssql_azure_auth_test for Azure; exercises Krb5Authenticator::InitialBytes() without connecting to SQL Server. Compiled with a no-op fallback so the functions are always registered (returns "compiled without Kerberos support" when MSSQL_ENABLE_KRB5 is undefined).

Test infrastructure: test/kerberos/ — self-contained docker-compose stack (KDC + SQL Server + test-client). No real Active Directory required:

cd test/kerberos
docker compose up -d --build
docker compose exec test-client /run-tests.sh
docker compose down -v

The test KDC's realm is EXAMPLE.COM, principal is testuser@EXAMPLE.COM (password testpass), SPN is MSSQLSvc/sql.example.com:1433. The test-client uses a multi-stage Dockerfile that builds the extension inside Linux, so the stack works on macOS hosts too.

Platform matrix:

Connection-string surface (verbatim from microsoft/go-mssqldb):

All also accepted on CREATE SECRET (with underscore naming: krb5_keytabfile, etc.).

Key design decisions (do NOT re-litigate — these were settled during ultrareview):

• Raw mode is SECRET-ONLY. The validator unconditionally rejects Password in any connection string when integrated auth is selected. Defends against cleartext passwords in connection-string logs.
• User Id requires either a keytab or a Password (in a secret). CredCache mode rejects bare User Id — was silently authenticating as the ambient ccache holder before fix.
• Default SPN form is MSSQLSvc/<fqdn>:<port> (canonical Kerberos principal-name form, matches AD default registration). Krb5Authenticator picks the gss_import_name name type based on whether the SPN contains /.
• No setenv() for per-connection overrides — uses gss_acquire_cred_from with cred_store elements (ccache, config). Thread-safe vs concurrent getenv on worker threads.
• macOS uses GSS.framework (Heimdal-derived subset) which lacks MIT extensions for keytab/raw modes; Krb5Authenticator constructor rejects those modes on macOS with a clear error pointing at the Linux container path.
• GSSAPI OIDs (SPNEGO, Kerberos, hostbased-service, krb5-principal-name) are constructed inline as gss_OID_desc literals — macOS's GSS.framework declares GSS_C_NT_HOSTBASED_SERVICE etc. as extern gss_OID in the header but does NOT export the symbols. Inline DER bytes avoid the link dependency on every platform.

See Kerberos.md for end-user documentation.

Build Troubleshooting

ODR (One Definition Rule) Errors on Linux

Symptom: Linux builds fail with "multiple definition of duckdb::LogicalType::BIGINT" and similar errors for constexpr static members.

Root Cause: DuckDB defaults to C++11. If the extension uses target_compile_features(... cxx_std_17), it gets compiled with C++17 while DuckDB remains C++11. The different handling of constexpr static members (external linkage in C++11 vs inline in C++17) causes ODR violations when linking.

What NOT to do in CMakeLists.txt:

# DO NOT USE - causes ODR errors on Linux when DuckDB is C++11:
set(CMAKE_CXX_STANDARD 17 CACHE STRING "..." FORCE)
target_compile_features(${EXTENSION_NAME} PRIVATE cxx_std_17)

Correct Solution: Don't force C++17 for the extension. Use DuckDB's default C++ standard (C++11) and avoid C++17-only features like structured bindings. The extension code should be compatible with C++11.

Note: This issue only manifests on GCC/Linux, not on Clang/macOS, because Clang is more lenient with ODR for constexpr static members.

Recent Changes

• 049-fix-partitioned-table-catalog: Partitioned tables were unreadable (#85). The three catalog metadata queries joined sys.partitions directly, and that view holds one row per partition — so on an N-partition table every object and every column came back N times, aborting catalog load with Column with name <x> already exists!. A plain clustered index reads fine; only partitioning triggers it. The same join silently took approx_rows from whichever partition surfaced first — typically an empty one — so the planner saw a partitioned table as nearly empty while the statistics provider (which already summed) disagreed. Both fixed by joining a pre-aggregated subquery. The subquery now also reports the object's physical shape — index_kind (MSSQLIndexKind: heap / clustered / clustered columnstore, from sys.indexes.type) and partition_count on MSSQLTableMetadata — at no extra round trip, which the write path needs: TABLOCK helps a heap and serialises a clustered rowstore index, and a clustered index that is not a primary key is invisible to PK discovery. Typed rather than a bool because a clustered columnstore also reports index_id = 1 and wants the opposite TABLOCK decision.
• 047-process-state-cleanup: Process-wide singleton cleanup + ATTACH credential validation + Azure TokenCache namespacing + custom Application Name (closes #96, #82; spawns #119 for future spec 049). Three singletons removed: MssqlPoolManager → per-MSSQLCatalog unique_ptr<ConnectionPool>; MSSQLContextManager (spec 045 band-aid) → direct Catalog::GetCatalog() lookup; MSSQLResultStreamRegistry → per-catalog RegisterStream / RetrieveStream methods on MSSQLCatalog. One singleton kept + deprecated: MSSQLConnectionHandleManager backs mssql_open / mssql_close / mssql_ping (no catalog discriminator on those APIs); marked [DEPRECATED] group with companion mssql_close_all() shutdown helper, scheduled for removal with the functions in a future major release. Security hardening: Azure TokenCache keyed by (uintptr_t(DatabaseInstance*), cache_key) so two instances sharing a secret name no longer alias (FR-012); ATTACH eagerly validates credentials by default with lazy_validation true opt-out (FR-011); ATTACH error path audited to never echo password (T028a); mssql_pool_stats redaction grep gate (SC-005); explicit noexcept on the teardown chain (~MSSQLCatalog / ~ConnectionPool / ~TdsConnection / ~TdsSocket / ~TlsTdsContext / ~TlsImpl) + debug-only D_ASSERT(active_connections_.empty()) invariant. Custom Application Name: Application Name=... / applicationname=... / secret application_name propagated to LOGIN7 program_name; visible as APP_NAME() / sys.dm_exec_sessions.program_name; 128-char client-side clamp matches SQL Server's own limit. See specs/047-process-state-cleanup/state_inventory.md for the post-spec process-wide-static classification.
• 042-integrated-authentication Phase 4: Added Windows SSPI authentication via secur32.dll's Negotiate package. WinSspiAuthenticator peer of Krb5Authenticator. Same IAuthenticator interface; shared SPNEGO continuation loop in TdsConnection::AuthenticateIntegrated.
• 045-type-codec-consolidation: Per-type encoding/decoding/literal/DDL logic consolidated into 9 family modules under src/codec/ (boolean/integer/float/decimal/money/string/binary/datetime/uuid). 5 LogicalType-side dispatch sites collapsed to family-dispatch (FamilyFromLogicalType switch or codec::FormatSqlLiteral one-liner). 762 LOC removed across dispatch sites (3243→2481, −23.5%). Bonus: TIMESTAMP_MS/NS/S/TZ now round-trip losslessly through SQL Server DATETIME2(3/7/0/7) with full type-transparency (catalog reports the variant, encode/decode preserves native precision). Bonus: stale-ATTACH ContextManager fix (sqllogictest --force-reload pointer-reuse). Closes issue #91 (BCP nvarchar character-vs-byte length); closes issue #89 (VIEW catalog-vs-runtime type divergence). No new vcpkg deps. Per-row bench (1M rows): 0.988–1.015× ratio vs spec-044 baseline (well within 5% gate).
• 044-codec-consolidation: Finishes the simdutf migration started in 043 — every legacy Utf16LE* call site moves to the simdutf-backed wrapper, the wrapper is renamed back to Utf16LE* (legacy file path resurrected with new implementation), and the legacy hand-rolled converter survives only as a private invalid-input fallback. Includes codec microbenchmark (make bench-utf16) and an end-to-end before/after benchmark (test/bench/bench_codec_e2e.sh, 100M rows) recorded into bench_results.md. No new vcpkg deps.
• 043-refactoring-foundation: Added C++ (C++11-compatible ABI) + DuckDB (main branch), simdutf (vcpkg, statically linked, MIT) for LOGIN7 non-ASCII fix; OpenSSL unchanged
• 042-integrated-authentication: Added Kerberos (POSIX) integrated authentication via system GSSAPI. SPNEGO + LOGIN7 fIntSecurity bit + 0xED SSPI continuation tokens. Self-contained test stack at test/kerberos/.
• 041-xml-type-support: Added C++17 (C++11-compatible for ODR on Linux) + DuckDB (main branch), OpenSSL (vcpkg), existing TDS protocol layer
• 040-fix-datetimeoffset-nbc: Added C++17 (C++11-compatible for ODR on Linux) + DuckDB (main branch), OpenSSL (vcpkg), custom TDS protocol layer
• 039-order-pushdown: Added C++17 (C++11-compatible for ODR on Linux) + DuckDB (main branch), OpenSSL (vcpkg), existing TDS protocol layer

Active spec: 068-login-routing-unification. See implementation plan at specs/068-login-routing-unification/plan.md for technical context, research findings, data model, contracts, and quickstart.