SQL Server: Index creation and conversion issues with columnstore indexes — Encountering internal query processor errors

Thanks for the repro! I was able to reproduce the error on SQL 2022 CU27 and SQL 2025 CU9, that is, the latest public builds. So this has to be considered to be a bug in the product - or possibly a limitation. This was on my machines at home, not an SQL Server VM in Azure.

It seems that the culprits are the nvarchar(4000) columns. When I remove these, I do not get the error. I was also able to run the script without error with only the column included_columns in the table.

One workaround is to first drop the index and the run CREATE INDEX, but this means that SQL Server has to double the work. First convert the columnstore index to a heap and then to a B-tree.

I tried the suggestions from Deepesh, and #1, #2 and #4 had no effect. However, MAXDOP = 1 seems to save the show. That is probably a better workaround than DROP + CREATE.

As for how to get this bug addressed, there are two ways to go. One is to report it on https://feedback.azure.com/d365community/forum/04fe6ee0-3b25-ec11-b6e6-000d3a4f0da0 This is only to let Microsoft know and they will address it as they see fit. Be sure to include the repro. I've included a slightly clean-up version below, so that you easily can copy and paste.

If this is an issue that is blocking you - that is, you cannot accept the workarounds - you will have to open a support case. Yes, I see that you have already opened a support case through the Azure portal. I'm uncertain how this works, but I suspect that the case you have opened is through a support plan you have with Azure, and this issue has nothing to do with Azure as such, but it is purely an SQL Server problem. So you may have to open a new case for SQL Server, all assuming that your organisation has support contract that permits this.

drop table if exists [dbo].[table]

CREATE TABLE [dbo].[table](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[server_name] [nvarchar](128) NOT NULL,
[collected_date] [datetimeoffset](7) NOT NULL,
[database_name] [nvarchar](128) NULL,
[table_name] [nvarchar](128) NULL,
[equality_columns] [nvarchar](4000) NULL,
[inequality_columns] [nvarchar](4000) NULL,
[included_columns] [nvarchar](4000) NULL,
[impact] [bigint] NULL,
[user_seeks] [bigint] NULL,
[query_hash] [nvarchar](64) NULL,
[query_plan_hash] [nvarchar](64) NULL,
[first_seen_date] [datetimeoffset](7) NULL,
[last_seen_date] [datetimeoffset](7) NULL,
[appearance_count] [int] NULL
) ON [PRIMARY]
GO
CREATE CLUSTERED COLUMNSTORE INDEX [CCI] ON [dbo].[table] 
   WITH (DROP_EXISTING = OFF, COMPRESSION_DELAY = 0, DATA_COMPRESSION = COLUMNSTORE) ON [PRIMARY]
GO
SET NOCOUNT ON

DECLARE @NumRegistros INT = 1000; -- Cantidad de registros a insertar
DECLARE @i INT = 0;
WHILE @i < @NumRegistros
BEGIN
   DECLARE @CollectedDate DATETIMEOFFSET(7) =
       DATEADD(DAY, -(ABS(CONVERT(BIGINT, CHECKSUM(NEWID()))) % 365), SYSDATETIMEOFFSET());
   DECLARE @FirstSeenDate DATETIMEOFFSET(7) =
       DATEADD(DAY, -(ABS(CONVERT(BIGINT, CHECKSUM(NEWID()))) % 90), @CollectedDate);
   DECLARE @LastSeenDate DATETIMEOFFSET(7) =

    DATEADD(DAY,
        ABS(CONVERT(BIGINT, CHECKSUM(NEWID()))) %
             (DATEDIFF(DAY, @FirstSeenDate, @CollectedDate) + 1),
              @FirstSeenDate);

INSERT INTO [dbo].[table]
(
    [server_name],
    [collected_date],
    [database_name],
    [table_name],
    [equality_columns],
    [inequality_columns],
    [included_columns],
    [impact],
    [user_seeks],
    [query_hash],
    [query_plan_hash],
    [first_seen_date],
    [last_seen_date],
    [appearance_count]
)
VALUES
(
    N'SQLSERVER-' + CAST(ABS(CHECKSUM(NEWID()) % 10) + 1 AS NVARCHAR(10)),
    @CollectedDate,
    N'Database_' + CAST(ABS(CHECKSUM(NEWID()) % 20) + 1 AS NVARCHAR(10)),
    N'Table_' + CAST(ABS(CHECKSUM(NEWID()) % 100) + 1 AS NVARCHAR(10)),
    -- Columnas de igualdad
    N'[Column_' + CAST(ABS(CHECKSUM(NEWID()) % 20) + 1 AS NVARCHAR(10)) + N']',
    -- Columnas de desigualdad
    N'[Column_' + CAST(ABS(CHECKSUM(NEWID()) % 20) + 1 AS NVARCHAR(10)) + N']',
    -- Columnas incluidas
    N'[Column_' + CAST(ABS(CHECKSUM(NEWID()) % 20) + 1 AS NVARCHAR(10)) +
    N'], [Column_' + CAST(ABS(CHECKSUM(NEWID()) % 20) + 1 AS NVARCHAR(10)) + N']',

    -- Impacto entre 1 y 100
    ABS(CHECKSUM(NEWID()) % 100) + 1,

    -- User seeks entre 1 y 100000
    ABS(CHECKSUM(NEWID()) % 100000) + 1,

    -- Hash de consulta
    CONVERT(NVARCHAR(64), NEWID()),

    -- Hash del plan
    CONVERT(NVARCHAR(64), NEWID()),

    @FirstSeenDate,
    @LastSeenDate,

    -- Número de apariciones entre 1 y 500

    ABS(CHECKSUM(NEWID()) % 500) + 1
);

SET @i += 1;

END;

PRINT CONCAT('Registros insertados: ', @NumRegistros);
go
go
CREATE CLUSTERED INDEX [CCI] 
ON [dbo].[table] ([Id])
WITH (DROP_EXISTING = ON, DATA_COMPRESSION = NONE, SORT_IN_TEMPDB = OFF)
ON [PRIMARY];

That message is error 8601. Microsoft has fixed several 8601 bugs involving columnstore in past CUs (KB2981764, KB3172959).

Converting a clustered columnstore index to a rowstore clustered index with DROP_EXISTING = ON is supported, so an internal QP error here is not expected behavior.

Tests to narrow it down

Retry the conversion with one change at a time:

1. Remove SORT_IN_TEMPDB = ON
2. Remove DATA_COMPRESSION = PAGE
3. Add MAXDOP = 1
4. First run ALTER INDEX [CCI] ON [dbo].[table] REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);, then retry

The reason for test 4: with this few rows, the data is in an open delta rowgroup, not compressed segments. You can confirm that with sys.dm_db_column_store_row_group_physical_stats. Whether that matters for the bug is untested.

To capture more detail

Run an Extended Events session on error_reported, filtered to error_number = 8601, with the tsql_stack action. Also check the SQL Server LOG folder for any SQLDump* files from the time of the failure.

Workaround to try

Use DROP INDEX [CCI], then a separate CREATE CLUSTERED INDEX ... WITH (DATA_COMPRESSION = PAGE).