Columnstore Indexes – part 131 (“Rebuilding Rowstore Indexes ONLINE”)

Continuation from the previous 130 parts, the whole series can be found at https://www.nikoport.com/columnstore/.

This blogpost will be about older SQL Server versions (2016, 2017) and some implications that I have found that people rather do not understand, until they hit the problems in productions – the ONLINE creation & rebuild operations for the Rowstore indexes when having Columnstore Indexes on their tables.
As you should know by now – SQL Server 2017 & SQL Server 2019 respectively brought the ONLINE features support for the Columnstore Indexes (Nonclustered(2017) & Clustered(2019)) and I have blogged about those news in Columnstore Indexes – part 96 (“Nonclustered Columnstore Index Online Rebuild”) in 2017 and in Columnstore Indexes – part 123 (“Clustered Columnstore Index Online Rebuild”) in 2018.

What I honestly did not expect is the amount of the same question I have faced as in the last 3 months regarding the rebuild operations for the the ONLINE creation & rebuild operations for the Rowstore indexes when having Columnstore Indexes.

Clustered Columnstore Index on SQL Server 2017

Without much further to add, let’s create a plain table with a Clustered Columnstore Index:

CREATE TABLE dbo.TestMe(
	C1 INT NOT NULL,
	INDEX CCI_TestMe CLUSTERED COLUMNSTORE );

and now we can try to add a Nonclustered Rowstore Index … ONLNIE:

CREATE NONCLUSTERED INDEX IX_TestMe_C1
	ON dbo.TestMe(C1)
		WITH (DATA_COMPRESSION = PAGE, ONLINE = ON );

Msg 35352, Level 16, State 1, Line 6
create statement failed because the operation cannot be performed online on a table with a columnstore index. Perform the operation without specifying the ONLINE option or drop (or disable) the columnstore index before performing the operation using the ONLINE option.

For some reason people are being shocked, since they were expecting that the secondary Rowstore Indexes would function in an online fashion, even though the physical structure of the table would not support this function. It should not support it, because you simply can not guarantee the underlying operations.
Yeah, it would be wonderful. Such as the Resumable Online Columnstore Indexes operations … but here we are writing/reading about this in 2020…

Nonclustered Columnstore Index

Let’s try some scenarios with Nonclustered Columnstore Indexes, just in case … and let’s do it on the SQL Server 2016:

DROP TABLE IF EXISTS dbo.TestMeNCCI;

CREATE TABLE dbo.TestMeNCCI(
	C1 INT NOT NULL,
	INDEX CCI_TestMeNCCI NONCLUSTERED COLUMNSTORE(C1) );

And voilá!

CREATE NONCLUSTERED INDEX IX_TestMeNCCI_C1
	ON dbo.TestMeNCCI(C1)
		WITH (DATA_COMPRESSION = PAGE, ONLINE = ON );

The statement works without any problem at all. Since the base structure of our table is a HEAP and here is the code to try with a Clustered Rowstore Primary Key, which works fine as well:

DROP TABLE IF EXISTS dbo.TestMeNCCI;

CREATE TABLE dbo.TestMeNCCI(
	C1 INT NOT NULL PRIMARY KEY,
	INDEX CCI_TestMeNCCI NONCLUSTERED COLUMNSTORE(C1) );

CREATE NONCLUSTERED INDEX IX_TestMeNCCI_C1
	ON dbo.TestMeNCCI(C1)
		WITH (DATA_COMPRESSION = PAGE, ONLINE = ON );

Regarding the rebuilding operation of the secondary indexes – it will be unaffected as well:

ALTER INDEX IX_TestMeNCCI_C1
	ON dbo.TestMeNCCI
		REBUILD
		WITH (DATA_COMPRESSION = PAGE, ONLINE = ON );

and rebuilding the Clustered Columnstore Index won’t work on SQL Server 2016, because for that feature we would need a SQL Server 2017 with an Enterprise Edition:

ALTER INDEX CCI_TestMeNCCI
	ON dbo.TestMeNCCI
		REBUILD
		WITH (ONLINE = ON );

Msg 35328, Level 16, State 1, Line 16
ALTER INDEX REBUILD statement failed because the ONLINE option is not allowed when rebuilding a columnstore index. 
Rebuild the columnstore index without specifying the ONLINE option.

Even though if we re-create our table with an explicit Clustered Primary Key PK_TestMeNCCI:

DROP TABLE IF EXISTS dbo.TestMeNCCI;

CREATE TABLE dbo.TestMeNCCI(
	C1 INT NOT NULL,
	INDEX CCI_TestMeNCCI NONCLUSTERED COLUMNSTORE(C1),
	CONSTRAINT PK_TestMeNCCI PRIMARY KEY CLUSTERED(C1) );

The online index creation will obviously work

CREATE NONCLUSTERED INDEX IX_TestMeNCCI_C1
	ON dbo.TestMeNCCI(C1)
		WITH (DATA_COMPRESSION = PAGE, ONLINE = ON );

and the ONLINE table rebuild will work (as in the case with a HEAP):

ALTER TABLE dbo.TestMeNCCI
		REBUILD
		WITH (ONLINE = ON );

, so will be working an explicit forcing of the Primary Key Online Rebuild:

	ALTER INDEX PK_TestMeNCCI
	ON dbo.TestMeNCCI
		REBUILD
		WITH (ONLINE = ON );

, which means that the Nonclustered Columnstore Index shall get the same treatment … or will it ?

It won’t – because Nonclustered Columnstore won’t get affected by those operations and here is the proof:

DROP TABLE IF EXISTS dbo.TestMeNCCI;

CREATE TABLE dbo.TestMeNCCI(
	C1 INT NOT NULL,
	INDEX CCI_TestMeNCCI NONCLUSTERED COLUMNSTORE(C1),
	CONSTRAINT PK_TestMeNCCI PRIMARY KEY CLUSTERED(C1) );

Let’s insert 5 million rows into our table:

INSERT INTO dbo.TestMeNCCI WITH (TABLOCK)
SELECT t.RN
	FROM
	(
		SELECT TOP (5 * 1048576) ROW_NUMBER()
			OVER (ORDER BY (SELECT NULL)) RN
		FROM sys.objects t1
		CROSS JOIN sys.objects  t2
		CROSS JOIN sys.objects  t3
		CROSS JOIN sys.objects  t4
		CROSS JOIN sys.objects  t5    
	) t
	OPTION (MAXDOP 1);

and let’s check our Row Groups:

select *
	from sys.column_store_row_groups
	where object_id = object_id('TestMeNCCI');

Notice the second & the third Row Groups getting well over 1 million rows – I love the elasticity of the Nonclustered Columnstore Indexes!

Rebuilding the table or the Primary Clustered Key won’t change a thing for our Nonclustered Columnstore Index:

ALTER INDEX PK_TestMeNCCI
	ON dbo.TestMeNCCI
		REBUILD
		WITH (ONLINE = ON );

ALTER TABLE dbo.TestMeNCCI
		REBUILD
		WITH (ONLINE = ON );

select *
	from sys.column_store_row_groups
	where object_id = object_id('TestMeNCCI');

Oh yeah …

to be continued…