Columnstore Indexes

SQL Server Administration
1

I have a script that runs and fails as we have columnstore indexes

UPDATE STATISTICS failed because statistics cannot be updated on a columnstore index. UPDATE STATISTICS is valid only when used with the STATS_STREAM option. [SQLSTATE 42000] (Error 35337) 1 UPDATE STATISTICS sometable CCI__abc WITH FULLSCAN [SQLSTATE 01000] (Error 0).

The script that is used to do the update statistics is
SELECT
(OBJECT_SCHEMA_NAME(st.object_id) + '.' + OBJECT_NAME(st.object_id)) AS TableName
,st.name AS StatName
,STATS_DATE(st.object_id, st.stats_id) AS StatDate
,has_filter
,ps.row_count AS TableRows
FROM sys.stats st WITH (nolock)
INNER JOIN sys.dm_db_partition_stats AS ps ON st.object_id = ps.object_id
AND (ps.index_id=0 or ps.index_id=1)

The only thing I can see from here is that the st.name starts with CCI_
To not include cci indexes in the update status I could do where substring(st.name,1,3) <> 'CCI'

Is there a better way than doing the hard coding, i.e by a type or anything?

2

Yes. Use sys.indexes:

SELECT
(OBJECT_SCHEMA_NAME(st.object_id) + '.' + OBJECT_NAME(st.object_id)) AS TableName
,st.name AS StatName
,STATS_DATE(st.object_id, st.stats_id) AS StatDate
,ps.has_filter
,ps.row_count AS TableRows
FROM sys.stats st WITH (nolock)
INNER JOIN sys.dm_db_partition_stats AS ps ON st.object_id = ps.object_id
AND (ps.index_id=0 or ps.index_id=1) 
INNER JOIN sys.indexes si ON si.object_id = st.object_id 
AND si.index_id = ps.index_id AND si.type < 3 /*0=heap,1=clus,2=nonclus,5=cci*/
3

Thank you