STRING_AGG 8000-byte overflow in `sqlserver__describe_indexes` breaks wide-table incremental/table/snapshot builds (1.10.1rc1)

[joshmarkovic]

Summary

On dbt-sqlserver 1.10.1rc1, any incremental, table, or snapshot model whose target table has a wide index fails during materialization. The failure comes from the new index-reconciliation introspection (sqlserver__reconcile_indexes → sqlserver__describe_indexes), which concatenates each index's column names with STRING_AGG(col.[name], ', ') without casting the input to a LOB type. When an index spans enough columns that the concatenated name list exceeds 8000 bytes, SQL Server raises error 9829 and the whole model rolls back.

We hit this on a table configured with as_columnstore=true: the clustered columnstore index spans all 534 columns of the table, so the aggregated column-name list blows past the 8000-byte cap. Measured on the failing table, the index's concatenated column-name list is ~12,772 characters (~25,544 bytes as nvarchar) — roughly 3x over the limit.

This is a regression introduced with the index feature in #714 and it is still present on master.

Environment

• dbt-sqlserver: 1.10.1rc1
• dbt-core: 1.11.12
• dbt-adapters: 1.24.2
• ODBC Driver 17 for SQL Server
• SQL Server 2017+ (the adapter's STRING_AGG floor)

Symptom

The model runs for its full duration (in our case ~177s — the data load actually succeeds), then errors at the very end during index reconciliation:

203 of 735 ERROR creating sql incremental model innocap.position_map_daily ..... [ERROR in 176.97s]

The summary line carries no message, so at first glance it looks like a silent failure. The real error is only visible in logs/dbt.log:

sqlserver adapter: Database error: ('42000', '[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]STRING_AGG aggregation result exceeded the limit of 8000 bytes. Use LOB types to avoid result truncation. (9829) (SQLExecDirectW)')

Root cause

sqlserver__describe_indexes (dbt/include/sqlserver/macros/adapters/indexes.sql) builds the column lists for each index using STRING_AGG(col.[name], ', '). col.[name] is sysname (nvarchar(128)), so STRING_AGG's result type is capped at 8000 bytes. There is no CAST(... AS NVARCHAR(MAX)), so as soon as one index's concatenated column names exceed that cap, SQL Server throws error 9829 and aborts.

The three affected aggregates (current master, ~lines 382 / 390 / 398; identical in 1.10.1rc1):

select string_agg(col.[name], ', ') within group (order by ic.key_ordinal) as cols  -- key columns
...
select string_agg(col.[name], ', ') as cols                                          -- included columns
...
select string_agg(col.[name], ', ') as cols                                          -- descending columns

The overflow condition is met by any table where an index's column-name list concatenates to more than 8000 bytes. The most likely trigger in practice is as_columnstore=true on a wide table: the clustered columnstore index spans every column, so a few hundred columns with realistic names is enough. A rowstore nonclustered index with a large INCLUDE list hits it the same way.

Confirmed on our failing table (the only index present is the columnstore index created by as_columnstore=true):

index_id | index_name | type_desc             | col_count | approx_chars | approx_bytes
1        | cci        | clustered columnstore | 534       | 12772        | 25544

A clustered columnstore index has index_id = 1 and populates sys.index_columns with one row per table column, so every column of the table feeds the aggregate.

Affected code paths

sqlserver__describe_indexes is reached through sqlserver__reconcile_indexes, which runs from three materializations:

• macros/materializations/models/incremental/incremental.sql
• macros/materializations/models/table/table_dml_refresh.sql
• macros/materializations/snapshots/snapshot.sql

So this is not incremental-specific; any persistent-table build that reconciles indexes on a sufficiently wide table is affected.

Reproduction

Create a wide table with a clustered columnstore index, then run the aggregate the macro uses:

-- 1. a table wide enough that its column names concatenate to > 8000 bytes
--    (a few hundred columns with realistic names; here we generate 500)
DECLARE @cols nvarchar(max) = '';
SELECT @cols = @cols + ', col_' + RIGHT('000' + CAST(n AS varchar(3)), 3)
             + '_reasonably_descriptive_name int'
FROM (SELECT TOP (500) ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1 AS n
      FROM sys.all_objects) x;
EXEC('CREATE TABLE dbo.wide_cci_repro (id int' + @cols + ')');
CREATE CLUSTERED COLUMNSTORE INDEX cci_wide_repro ON dbo.wide_cci_repro;

-- 2. the failing aggregate from sqlserver__describe_indexes
SELECT STRING_AGG(col.[name], ', ') AS cols
FROM sys.index_columns ic
INNER JOIN sys.columns col
    ON col.object_id = ic.object_id AND col.column_id = ic.column_id
WHERE ic.object_id = OBJECT_ID('dbo.wide_cci_repro');
-- Msg 9829: STRING_AGG aggregation result exceeded the limit of 8000 bytes.

The equivalent dbt-level repro: an incremental, table, or snapshot model with as_columnstore=true on a table with a few hundred columns.

Proposed fix

Cast the aggregated expression to a LOB type at all three call sites, exactly as the error message recommends. STRING_AGG then returns nvarchar(max) and no longer truncates:

-        select string_agg(col.[name], ', ') within group (order by ic.key_ordinal) as cols
+        select string_agg(cast(col.[name] as nvarchar(max)), ', ') within group (order by ic.key_ordinal) as cols

-        select string_agg(col.[name], ', ') as cols
+        select string_agg(cast(col.[name] as nvarchar(max)), ', ') as cols

(applied to the included-columns and descending-columns aggregates as well).

Notes

• Introduced in #535 Postgres style indexes #714 (#535 Postgres style indexes, merged 2026-06-20), commit 7f1c3a654, which added sqlserver__describe_indexes and all three STRING_AGG calls. Still unfixed on master as of 2026-07-07.
• The index macros were originally ported from dbt-fabric; if Fabric carries the same describe_indexes shape it likely has the same overflow.
• Minor UX point: because the failure surfaces only in logs/dbt.log and the run-summary line is blank, this reads as a silent/opaque failure. Surfacing the adapter error on the summary line would have made this much faster to diagnose.

[axellpadilla]

Just in time to avoid releasing before fixing this thanks, will you be able to send that fix? sounds good

[joshmarkovic]

@Benjamin-Knight thanks for the index-reconciliation work in #714, it's a really useful addition. Would you be open to finishing it off with the small fix above? The three STRING_AGG calls in sqlserver__describe_indexes just need their input cast to nvarchar(max) so they don't hit the 8000-byte cap on wide tables. Happy to open a PR with the change plus a regression test if that's easier on your end. cc @axellpadilla as the merger.

[Feckinotter]

I am on holiday, hence the personal account, I can look next week, if you don't want to block rc1 you'll need to do the pull but it looks simple and correct, the only suggestion I can think of is that do we need to test clustered columstores columns at all, only one can exist so all that work suggests a missing shortcut .