UPDATE STATISTICS failed because statistics cannot be updated on a columnstore index.UPDATE STATISTICS is valid only when used with the STATS_STREAM option.

Error:-

If we have clustered column store index on a table and when we update statistics with traditional command like

UPDATE Statistics <TableName> (Statisticsname ) with FULLSCAN

-->The update statistics command gets fail with the below-highlighted message

Msg 35337, Level 16, State 1, Line 23

UPDATE STATISTICS failed because statistics cannot be updated on a columnstore index.  UPDATE STATISTICS is valid only when used with the STATS_STREAM option.

use AdventureWork2014
go

CREATE CLUSTERED columnstore INDEX [PK_ErrorLog_ErrorLogID] ON [dbo].[Errorlog] WITH (DROP_EXISTING = OFF)

then Execute below command to retrieve

SELECT
OBJECT_NAME([sp].[object_id]) AS "Table",
[sp].[stats_id] AS "Statistic ID",
[s].[name] AS "Statistic",
[sp].[last_updated] AS "Last Updated",
[sp].[rows],
[sp].[rows_sampled],
[sp].[unfiltered_rows],
[sp].[modification_counter] AS "Modifications",
'UPDATE Statistics ' + OBJECT_NAME([sp].[object_id]) + ' ( ' + [s].[name] + ')' + ' with SAMPLE 30 percent'
FROM [sys].[stats] AS [s]
OUTER APPLY sys.dm_db_stats_properties ([s].[object_id],[s].[stats_id]) AS [sp]
WHERE [s].[object_id] = OBJECT_ID(N'[dbo].[Errorlog]');

go

execute the command 

UPDATE Statistics ErrorLog ( PK_ErrorLog_ErrorLogID) with SAMPLE 30 percent

you will get below error.

Msg 35337, Level 16, State 1, Line 23

UPDATE STATISTICS failed because statistics cannot be updated on a columnstore index.  UPDATE STATISTICS is valid only when used with the STATS_STREAM option.

Resolution:-

In order to resolve this issue:-

We have to export that table statistics to a temp table. That table looks like this. It matches the output of DBCC SHOW_STATISTICS WITH HISTOGRAM.  


if exist drop # table

1.
drop table #stats_with_stream
go

2. Create a  #table #stats_with_stream

  CREATE TABLE #stats_with_stream
(
       stream VARBINARY(MAX) NOT NULL
       , rows INT NOT NULL
       , pages INT NOT NULL
);
go

3. Insert stream, rows and number of pages to  #stats_with_stream

INSERT INTO #stats_with_stream --SELECT * FROM #stats_with_stream
EXEC ('DBCC SHOW_STATISTICS (N''Adventureworks2014.dbo.[Errorlog]'',PK_ErrorLog_ErrorLogID )
  WITH STATS_STREAM,NO_INFOMSGS');

4. retrieve and check #stats_with_stream

select * from #stats_with_stream

5. The final step is to create the SQL that updates the statistics of our target table, and then execute it.

  DECLARE @sql NVARCHAR(MAX);
SET @sql = (SELECT 'UPDATE STATISTICS Adventureworks2014.dbo.Errorlog(PK_ErrorLog_ErrorLogID) WITH
STATS_STREAM = 0x' + CAST('' AS XML).value('xs:hexBinary(sql:column("stream"))',
'NVARCHAR(MAX)') FROM #stats_with_stream );


--PRINT (@sql);
EXEC (@sql);

Hence in this way, we can update statistics of that index which has clustered index.

PS:- For nonclustered column store index, we do not have to do anything.

Thanks for Reading..