Support online rebuild of Columnstore indexes

[rossdargan]

Azure SQL now supports online rebuilding of column store indexes (https://azure.microsoft.com/en-us/blog/transforming-your-data-in-azure-sql-database-to-columnstore-format/).

ALTER INDEX [IX_BUNKER] ON [ReportingDB].[dbo].[BunkerReading] REBUILD WITH (ONLINE = OFF)

is what is produced with the following:

EXECUTE dbo.IndexOptimize  @Databases = 'USER_DATABASES',  @FragmentationLow = NULL,  @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',  @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',  @FragmentationLevel1 = 5,  @FragmentationLevel2 = 30,  @UpdateStatistics = 'ALL', @LogToTable = 'Y',  @OnlyModifiedStatistics = 'Y', @Resumable='Y' RETURN 0

I would expect this instead now:

ALTER INDEX [IX_BUNKER] ON [ReportingDB].[dbo].[BunkerReading] REBUILD WITH (ONLINE = ON)

[eric-rubeck]

As of version 2019-01-13 13:51:41 this issue is caused by this code on line 1852:

AND ((@CurrentIndexType = 1 AND @CurrentIsImageText = 0 AND @CurrentIsNewLOB = 0)
          OR (@CurrentIndexType = 2 AND @CurrentIsNewLOB = 0)
          OR (@CurrentIndexType = 1 AND @CurrentIsImageText = 0 AND @CurrentIsFileStream = 0 AND @Version >= 11)
          OR (@CurrentIndexType = 2 AND @Version >= 11))
          AND (@CurrentIsColumnStore = 0 OR @Version < 11)

This code will allow Clustered Columnstore online rebuilds in SQL Server 2016, and Clustered/Nonclustered Columnstore online rebuilds in SQL Server 2017:

AND ((@CurrentIndexType = 1 AND @CurrentIsImageText = 0 AND @CurrentIsNewLOB = 0)
          OR (@CurrentIndexType = 2 AND @CurrentIsNewLOB = 0)
          OR (@CurrentIndexType = 1 AND @CurrentIsImageText = 0 AND @CurrentIsFileStream = 0 AND @Version >= 11)
          OR (@CurrentIndexType = 2 AND @Version >= 11)
          OR (@CurrentIndexType = 5 AND @Version >= 13)
          OR (@CurrentIndexType = 6 AND @Version >= 14))

Note, the line 'AND (@CurrentIsColumnStore = 0 OR @Version < 11)' must be removed after the complex OR, otherwise the suggested change will not work.

[rossdargan]

Thanks for this, but I had to change it to this to make it work with Azure SQL:

AND ((@CurrentIndexType = 1 AND @CurrentIsImageText = 0 AND @CurrentIsNewLOB = 0)
          OR (@CurrentIndexType = 2 AND @CurrentIsNewLOB = 0)
          OR (@CurrentIndexType = 1 AND @CurrentIsImageText = 0 AND @CurrentIsFileStream = 0 AND @Version >= 11)
          OR (@CurrentIndexType = 2 AND @Version >= 11)
          OR (@CurrentIndexType = 5 AND @Version >= 12)
          OR (@CurrentIndexType = 6 AND @Version >= 14))