Clustered ColumnStore index fragmentation

Question

In an existing clustered columnstore index expanding one of the existing cols. - from int to bigint - with values remaining same - would it lead to any fragmentation in the existing data that could cause any perf. issues for queries.

 

The table is mostly a read-only one with data being added every day and is partitioned month-wise.

 

The existing columnstore table is quite big (approx 400+ GB) already.

 

The need to expand the col. from int to bigint is due to the values for the new data rows coming from an upstream application will roll-past the INT value limit.

The INT col. in question is a non-key column

Answer 1

If it would change the rowgroups, it could maybe affect performance. But I think it does not affect the rowgroups.

Have a look at this example.

use tempdb
go

CREATE TABLE [dbo].[cci_test_table](
	[cci_test_key] [bigint] IDENTITY(1,1) NOT NULL,
	[last_modified] [datetime2](7) NOT NULL,
	[business_key] [int] not NULL,
 CONSTRAINT [XPK_cci_test_table] PRIMARY KEY NONCLUSTERED 
(
	[cci_test_key] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF, DATA_COMPRESSION = PAGE) ON [PRIMARY]
) ON [PRIMARY];
GO

CREATE CLUSTERED COLUMNSTORE INDEX [CCI_test] ON [dbo].[cci_test_table] WITH (DROP_EXISTING = OFF, COMPRESSION_DELAY = 0, DATA_COMPRESSION = COLUMNSTORE) ON [PRIMARY];
GO


-- Insert 10 Mio. rows
With cte_1 (n) as (
	Select 1 as n
	UNION ALL
	Select n+1 as na 
	from cte_1
	where n < 1000),
cte_2 (n) as (
	Select 1 as n
	UNION ALL
	Select n+1 as na 
	from cte_2
	where n < 1000)
Insert into dbo.cci_test_table(last_modified, business_key) 
Select getdate(), a.n + b.n 
from cte_1 as a, cte_2 as b
OPTION (MAXRECURSION 10000);

go 10


select * from sys.column_store_row_groups;

ALTER INDEX cci_test on dbo.cci_test_table REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

select * from sys.column_store_row_groups;

Alter table dbo.cci_test_table alter column [business_key] bigint not null;

select * from sys.column_store_row_groups;


go

drop TABLE [dbo].[cci_test_table];