Error MAX Column in a Memory-Optimized Columnstore Index.
Applies to: Azure SQL Database - Premium service.
Date created: September 28, 2026.
Problem Description.
While testing columnstore indexes on an Azure
SQL Database Premium P1 database, I created a memory-optimized table containing
an NVARCHAR(MAX) column. The table was created successfully, but adding a
columnstore index failed.
The following statements reproduce the problem:
WITH
(
MEMORY_OPTIMIZED = ON,
DURABILITY = SCHEMA_AND_DATA
);
GO
ALTER TABLE dbo.SQLCoffee_InMemory_Max
ADD INDEX CCI_SQLCoffee_InMemory_Max
CLUSTERED COLUMNSTORE;
GO
The index creation returned these errors:
Msg 35343, Level 16,
State 1, Line 141
The statement failed. Column 'Notes' has a data type that cannot participate in
a columnstore index.
Msg 1750, Level 16, State 1, Line 141
Could not create constraint or index. See previous errors.
Cause.
Memory-optimized tables support NVARCHAR(MAX), VARCHAR(MAX), and VARBINARY(MAX).
However, these large object (LOB) columns are stored off-row, meaning their
contents are stored separately from the main row. A columnstore index on a
memory-optimized table requires every column to fit in-row. This makes the
combination unsupported.
The columnstore index must also include every column in the memory-optimized
table. Therefore, leaving Notes out of the index is not an option.
Solution/Workaround.
When the application does not require a MAX column, use an appropriate
bounded-length datatype instead. For this empty demonstration table, the
proposed workaround is to change Notes to NVARCHAR(200) and then retry the index
creation. This removes the off-row LOB requirement from this small table.
ALTER TABLE dbo.SQLCoffee_InMemory_Max
ALTER COLUMN Notes nvarchar(200) NULL;
GO
ALTER TABLE dbo.SQLCoffee_InMemory_Max
ADD INDEX CCI_SQLCoffee_InMemory_Max
CLUSTERED COLUMNSTORE;
GO
The length 200 is only an example. Before shortening a production column, verify
that the chosen length accommodates both existing data and future application
requirements. Also plan for an interruption: ALTER TABLE operations on
memory-optimized tables are offline and block access to the table while they
run.
When MAX is necessary, consider moving that column to a separate related table
without a columnstore index, or keeping the original memory-optimized table
without columnstore. Separating the LOB column allows the remaining table to
meet the in-row requirement, provided its other columns are compatible.
Important: This restriction concerns columnstore indexes on memory-optimized
tables. It should not be generalized to all columnstore indexes: disk-based
clustered columnstore indexes in Azure SQL Database support these MAX datatypes.
![[Company Logo Image]](images/SQLCofee.jpg)
