How To Split Non-Empty Partitions When A Clustered Columnstore Index Exists on The Table

Another pithily titled blog post, but this one revisits the feature of SQL Server 2014 that piqued my interest the most: Clustered Columnstore Indexes. I’ve been working with Clustered Columnstore Indexes for the past few months and recently a colleague had to split a non empty partition, only for the error message below to appear.

Msg 35346, Level 15, State 1, Line 390
SPLIT clause of ALTER PARTITION statement failed because the partition is not empty. Only empty partitions can be split in when a columnstore index exists on the table. Consider disabling the columnstore index before issuing the ALTER PARTITION statement, then rebuilding the columnstore index after ALTER PARTITION is complete.

Ok, so it looks like we cannot split non empty partitions with a Clustered Columnstore Index on the table, or at least an enabled clustered columnstore index. So the topic for todays post is to find out how can we split a non empty partition when a clustered columnstore index exists on the table. We can create a test database/tables/Clustered Columnstore Indexes and disable the indexes before we split to see if that is the solution, as suggested in the error that it might be. Let’s start at the beginning using the database I created for my previous post on SQL Server 2014 Clustered Columnstore Indexes and Partitioning. The script below will create the database and the objects necessary. I’m going to state the obvious, but read through the scripts first before you run them; things like file locations on boxes are always different:

/*
***************
CREATE DATABASE
***************
*/

CREATE DATABASE [Phoebix]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'Phoebix', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\Phoebix.mdf' , SIZE = 5120KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'Phoebix_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\Phoebix_log.ldf' , SIZE = 2048KB , FILEGROWTH = 10%)
GO
ALTER DATABASE [Phoebix] SET COMPATIBILITY_LEVEL = 120
GO
ALTER DATABASE [Phoebix] SET READ_WRITE
GO
ALTER DATABASE [Phoebix] SET RECOVERY SIMPLE
GO
ALTER DATABASE [Phoebix] SET MULTI_USER
GO

/*
***************
CREATE FILEGROUPS
***************
*/

USE [Phoebix]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [Phoebix] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO

use Phoebix
go

ALTER DATABASE Phoebix ADD FILEGROUP [FGPhoebix]

/*
***************
CREATE FILES
***************
*/

ALTER DATABASE Phoebix ADD FILE
(NAME = N'Phoebix_F01', FILENAME = N'S:\Data\PhoebixData\Phoebix1.ndf', SIZE = 5GB, FILEGROWTH = 1GB)
TO FILEGROUP [FGPhoebix]
ALTER DATABASE Phoebix ADD FILE
(NAME = N'Phoebix_F02', FILENAME = N'S:\Data\PhoebixData\Phoebix2.ndf', SIZE = 5GB, FILEGROWTH = 1GB)
TO FILEGROUP [FGPhoebix]

/*
***************
CREATE PARTITION FUNCTION
***************
*/

USE Phoebix
GO
CREATE PARTITION FUNCTION pfnPhoebix (int) AS RANGE LEFT FOR VALUES (12116, 12146, 12765, 13258, 13267, 13278, 13441)
GO

/*
***************
CREATE PARTITION SCHEMA
***************
*/

USE Phoebix
GO
CREATE PARTITION SCHEME pscPhoebix AS PARTITION pfnPhoebix ALL TO (FGPhoebix)
GO

/*
***************
CREATE TABLES
***************
*/

use Phoebix
go

CREATE TABLE dbo.B_Phoebix
(
Id INT NOT NULL,
MonthEndDate DATE NOT NULL,
memberId BIGINT NOT NULL,
BSKey VARCHAR (4) NOT NULL,
value FLOAT NOT NULL
) ON pscPhoebix (Id) WITH (DATA_COMPRESSION = PAGE);

GO

CREATE TABLE dbo.S_Phoebix
(
Id INT NOT NULL,
MonthEndDate DATE NOT NULL,
memberId BIGINT NOT NULL,
SbId INT NOT NULL,
flow FLOAT NOT NULL,
vesting FLOAT NULL
) ON pscPhoebix (Id) WITH (DATA_COMPRESSION = PAGE);
GO

/*
***************
CREATE CLUSTERED COLUMNSTORE INDEXES
***************
*/

CREATE CLUSTERED COLUMNSTORE INDEX [B_Custard] ON [dbo].[B_Phoebix] WITH (DROP_EXISTING = OFF)
GO
CREATE CLUSTERED COLUMNSTORE INDEX [S_Custard] ON [dbo].[S_Phoebix] WITH (DROP_EXISTING = OFF)
GO

This database has two tables sharing the same partition schema, which itself is on its own secondary filegroup. The script will create 7 partitions with distinct ranges. Let’s populate some of those partitions with data. This is going to load into staging tables and switch the tables into the partitions, so we can be confident that everything is set up correctly.

/*
****************
CREATE LOAD DATA
****************
*/

CREATE TABLE dbo.B_Phoebix_Load
(
Id INT NOT NULL,
MonthEndDate DATE NOT NULL,
memberId BIGINT NOT NULL,
BSKey VARCHAR (4) NOT NULL,
value FLOAT NOT NULL
) WITH (DATA_COMPRESSION = PAGE);

GO

CREATE TABLE dbo.S_Phoebix_Load
(
Id INT NOT NULL,
MonthEndDate DATE NOT NULL,
memberId BIGINT NOT NULL,
SbId INT NOT NULL,
flow FLOAT NOT NULL,
vesting FLOAT NULL
) WITH (DATA_COMPRESSION = PAGE);

GO
declare @i as int;
set @i = 1;
begin tran
while @i <= 3200000
begin
INSERT INTO dbo.B_Phoebix_Load (Id, monthEndDate, memberId, bsKey, value)
values
( 13258, GETDATE(), RAND ()*12, 'FFOS', RAND ()*13),
( 13267, DATEADD(MONTH,1,GETDATE()), RAND ()*12, 'FFOS', RAND ()*13),
( 13278, DATEADD(MONTH,2,GETDATE()), RAND ()*12, 'FFOS', RAND ()*13)
INSERT INTO dbo.S_Phoebix_Load (Id, monthEndDate, memberId, sbId, flow, vesting)
values
( 13258, GETDATE(), RAND ()*12, RAND ()*50, RAND ()*13, RAND ()*13),
( 13267, DATEADD(MONTH,1,GETDATE()), RAND ()*12, RAND ()*50, RAND ()*13, RAND ()*13),
( 13278, DATEADD(MONTH,2,GETDATE()), RAND ()*12, RAND ()*50, RAND ()*13, RAND ()*13)
set @i = @i + 1;
end;
commit;

/*
*************************
LOAD DATA INTO PARTITIONS USING STAGING TABLES
*************************
*/

/****
RUN 1
****/

-- create the empty rowset from the table we will switch into
select top 0 * into dbo.B_Phoebix_13258 from dbo.B_Phoebix
select top 0 * into dbo.S_Phoebix_13258 from dbo.S_Phoebix

--do ourselves a favour and make this temporary table as small as possible
--and add page compression
ALTER TABLE dbo.B_Phoebix_13258 REBUILD WITH (DATA_COMPRESSION = PAGE)
ALTER TABLE dbo.S_Phoebix_13258 REBUILD WITH (DATA_COMPRESSION = PAGE)

--empty rowset is created on primary by default
--move to the filegroup the partitioned table is on
ALTER TABLE dbo.B_Phoebix_13258 ADD CONSTRAINT CONS_dbo_B_Phoebix_13258 PRIMARY KEY (Id) on FGPhoebix
ALTER TABLE dbo.S_Phoebix_13258 ADD CONSTRAINT CONS_dbo_S_Phoebix_13258 PRIMARY KEY (Id) on FGPhoebix

-- we don't need it (or can even keep it as we are adding CCI)
ALTER TABLE dbo.B_Phoebix_13258 DROP CONSTRAINT CONS_dbo_B_Phoebix_13258
ALTER TABLE dbo.S_Phoebix_13258 DROP CONSTRAINT CONS_dbo_S_Phoebix_13258

--add constraint on temp table so that we can switch in to main table
ALTER TABLE dbo.B_Phoebix_13258 WITH CHECK ADD CONSTRAINT CK_B_Phoebix_13258 CHECK (([Id]=(13258)))
ALTER TABLE dbo.S_Phoebix_13258 WITH CHECK ADD CONSTRAINT CK_s_Phoebix_13258 CHECK (([Id]=(13258)))

--add data to temp table from loading tables
--remember IRL this would be SSIS or HPC or data loader of choice
INSERT INTO dbo.B_Phoebix_13258 (Id, monthEndDate, memberId, bsKey, value)
select Id, monthEndDate, memberId, bsKey, value
from dbo.B_Phoebix_Load WITH (NOLOCK)
where id = 13258

INSERT INTO dbo.S_Phoebix_13258 (Id, monthEndDate, memberId, sbId, flow, vesting)
select Id, monthEndDate, memberId, sbId, flow, vesting
from dbo.S_Phoebix_Load WITH (NOLOCK)
where id = 13258

--add CCI
CREATE CLUSTERED COLUMNSTORE INDEX [PK_B_Phoebix_13258] ON dbo.B_Phoebix_13258 WITH (DROP_EXISTING = OFF)
CREATE CLUSTERED COLUMNSTORE INDEX [PK_S_Phoebix_13258] ON dbo.S_Phoebix_13258 WITH (DROP_EXISTING = OFF)

--switch in to main table
ALTER TABLE dbo.B_Phoebix_13258 SWITCH TO dbo.B_Phoebix PARTITION $PARTITION.pfnPhoebix(13258)
ALTER TABLE dbo.S_Phoebix_13258 SWITCH TO dbo.S_Phoebix PARTITION $PARTITION.pfnPhoebix(13258)

DROP TABLE dbo.B_Phoebix_13258
DROP TABLE dbo.S_Phoebix_13258

/****
RUN 2
****/

-- create the empty rowset from the table we will switch into
select top 0 * into dbo.B_Phoebix_13267 from dbo.B_Phoebix
select top 0 * into dbo.S_Phoebix_13267 from dbo.S_Phoebix

--do ourselves a favour and make this temporary table as small as possible
--and add page compression
ALTER TABLE dbo.B_Phoebix_13267 REBUILD WITH (DATA_COMPRESSION = PAGE)
ALTER TABLE dbo.S_Phoebix_13267 REBUILD WITH (DATA_COMPRESSION = PAGE)

--empty rowset is created on primary by default
--move to the filegroup the partitioned table is on
ALTER TABLE dbo.B_Phoebix_13267 ADD CONSTRAINT CONS_dbo_B_Phoebix_13267 PRIMARY KEY (Id) on FGPhoebix
ALTER TABLE dbo.S_Phoebix_13267 ADD CONSTRAINT CONS_dbo_S_Phoebix_13267 PRIMARY KEY (Id) on FGPhoebix

-- we don't need it (or can even keep it as we are adding CCI)
ALTER TABLE dbo.B_Phoebix_13267 DROP CONSTRAINT CONS_dbo_B_Phoebix_13267
ALTER TABLE dbo.S_Phoebix_13267 DROP CONSTRAINT CONS_dbo_S_Phoebix_13267

--add constraint on temp table so that we can switch in to main table
ALTER TABLE dbo.B_Phoebix_13267 WITH CHECK ADD CONSTRAINT CK_B_Phoebix_13267 CHECK (([Id]=(13267)))
ALTER TABLE dbo.S_Phoebix_13267 WITH CHECK ADD CONSTRAINT CK_s_Phoebix_13267 CHECK (([Id]=(13267)))

--add data to temp table from loading tables
--remember IRL this would be SSIS or HPC or data loader of choice
INSERT INTO dbo.B_Phoebix_13267 (Id, monthEndDate, memberId, bsKey, value)
select Id, monthEndDate, memberId, bsKey, value
from dbo.B_Phoebix_Load WITH (NOLOCK)
where id = 13267

INSERT INTO dbo.S_Phoebix_13267 (Id, monthEndDate, memberId, sbId, flow, vesting)
select Id, monthEndDate, memberId, sbId, flow, vesting
from dbo.S_Phoebix_Load WITH (NOLOCK)
where id = 13267

--add CCI
CREATE CLUSTERED COLUMNSTORE INDEX [PK_B_Phoebix_13267] ON dbo.B_Phoebix_13267 WITH (DROP_EXISTING = OFF)
CREATE CLUSTERED COLUMNSTORE INDEX [PK_S_Phoebix_13267] ON dbo.S_Phoebix_13267 WITH (DROP_EXISTING = OFF)

--switch in to main table
ALTER TABLE dbo.B_Phoebix_13267 SWITCH TO dbo.B_Phoebix PARTITION $PARTITION.pfnPhoebix(13267)
ALTER TABLE dbo.S_Phoebix_13267 SWITCH TO dbo.S_Phoebix PARTITION $PARTITION.pfnPhoebix(13267)

DROP TABLE dbo.B_Phoebix_13267
DROP TABLE dbo.S_Phoebix_13267
/****
RUN 3
****/

-- create the empty rowset from the table we will switch into
select top 0 * into dbo.B_Phoebix_13278 from dbo.B_Phoebix
select top 0 * into dbo.S_Phoebix_13278 from dbo.S_Phoebix

--do ourselves a favour and make this temporary table as small as possible
--and add page compression
ALTER TABLE dbo.B_Phoebix_13278 REBUILD WITH (DATA_COMPRESSION = PAGE)
ALTER TABLE dbo.S_Phoebix_13278 REBUILD WITH (DATA_COMPRESSION = PAGE)

--empty rowset is created on primary by default
--move to the filegroup the partitioned table is on
ALTER TABLE dbo.B_Phoebix_13278 ADD CONSTRAINT CONS_dbo_B_Phoebix_13278 PRIMARY KEY (Id) on FGPhoebix
ALTER TABLE dbo.S_Phoebix_13278 ADD CONSTRAINT CONS_dbo_S_Phoebix_13278 PRIMARY KEY (Id) on FGPhoebix

-- we don't need it (or can even keep it as we are adding CCI)
ALTER TABLE dbo.B_Phoebix_13278 DROP CONSTRAINT CONS_dbo_B_Phoebix_13278
ALTER TABLE dbo.S_Phoebix_13278 DROP CONSTRAINT CONS_dbo_S_Phoebix_13278

--add constraint on temp table so that we can switch in to main table
ALTER TABLE dbo.B_Phoebix_13278 WITH CHECK ADD CONSTRAINT CK_B_Phoebix_13278 CHECK (([Id]=(13278)))
ALTER TABLE dbo.S_Phoebix_13278 WITH CHECK ADD CONSTRAINT CK_s_Phoebix_13278 CHECK (([Id]=(13278)))

--add data to temp table from loading tables
--remember IRL this would be SSIS or HPC or data loader of choice
INSERT INTO dbo.B_Phoebix_13278 (Id, monthEndDate, memberId, bsKey, value)
select Id, monthEndDate, memberId, bsKey, value
from dbo.B_Phoebix_Load WITH (NOLOCK)
where id = 13278

INSERT INTO dbo.S_Phoebix_13278 (Id, monthEndDate, memberId, sbId, flow, vesting)
select Id, monthEndDate, memberId, sbId, flow, vesting
from dbo.S_Phoebix_Load WITH (NOLOCK)
where id = 13278

--add CCI
CREATE CLUSTERED COLUMNSTORE INDEX [PK_B_Phoebix_13278] ON dbo.B_Phoebix_13278 WITH (DROP_EXISTING = OFF)
CREATE CLUSTERED COLUMNSTORE INDEX [PK_S_Phoebix_13278] ON dbo.S_Phoebix_13278 WITH (DROP_EXISTING = OFF)

--switch in to main table
ALTER TABLE dbo.B_Phoebix_13278 SWITCH TO dbo.B_Phoebix PARTITION $PARTITION.pfnPhoebix(13278)
ALTER TABLE dbo.S_Phoebix_13278 SWITCH TO dbo.S_Phoebix PARTITION $PARTITION.pfnPhoebix(13278)

DROP TABLE dbo.B_Phoebix_13278
DROP TABLE dbo.S_Phoebix_13278

Now we have data loaded, we need a method of viewing the partitions. The script below will create a stored proc that we can run to view the data by partition. This sproc is modified from my SQL Server Partitioning 101 post.

/****
************************************************
CREATE STORED PROC WE CAN VIEW THE PARTITIONS BY
************************************************
****/

USE Phoebix
GO
CREATE PROCEDURE [dbo].[LatestPartitionPreview]
(
@partitionName nvarchar(100),
@sampleTable sysname
)
AS

declare @sql nvarchar(max)
declare @pscName nvarchar(100)
declare @pfnName nvarchar(100)
SET @pscName = 'psc' + @partitionName
SET @pfnName = 'pfn' + @partitionName

declare @partitonedTableId int

SELECT @partitonedTableId = t.object_id
FROM sys.TABLES t with (nolock)
JOIN sys.indexes i with (nolock) ON t.object_id = i.object_id
JOIN sys.partition_schemes ps with (nolock) ON i.data_space_id = ps.data_space_id
WHERE i.index_id < 2 and ps.name = @pscName
and t.name like @sampleTable + '%'

declare @currentPartitions table(partitionNumber int, [rows] int, value sql_variant, ranking int)

;with ranges (boundaryId, value)
as
(
select prv.boundary_id, prv.value
from sys.partition_functions f with (nolock)
inner join sys.partition_schemes s with (nolock) on s.function_id = f.function_id
inner join sys.partition_range_values prv with (nolock) on prv.function_id = f.function_id
where f.name = @pfnName
)
INSERT INTO @currentPartitions
SELECT partition_number, [rows], [value], ROW_NUMBER() OVER (ORDER BY partition_number desc) ranking FROM
(select top 1000 p.partition_number, [rows], [value]
FROM sys.partitions p with (nolock)
left join
ranges on ranges.boundaryid = p.partition_number
where p.[object_id] = @partitonedTableId
order by p.partition_number desc
) latestPartitions order by partition_number desc

select * from @currentPartitions
GO

Execute the sproc to view the runs by partition:


/****
************************************************
EXECUTE SPROC
************************************************
****/

USE [Phoebix]
GO

EXEC [dbo].[LatestPartitionPreview]
@partitionName = N'Phoebix',
@sampleTable = N'B_Phoebix'
GO

The result will look the same as this:

2014-08-13 13_40_52-Split_CCI.sql - (local).Phoebix

Ok great, we’ve now got 2 partitioned tables stored as CCI. Now what happens when we try to split a non empty partition?


ALTER PARTITION SCHEME pscPhoebix NEXT USED [FGPhoebix];
ALTER PARTITION FUNCTION pfnPhoebix() SPLIT RANGE(13261);

2014-08-13 13_52_56-Split_CCI.sql - (local).Phoebix

I’ve omitted some of the error, it is the same as at the top of the page :

Msg 35346, Level 15, State 1, Line 390
SPLIT clause of ALTER PARTITION statement failed because the partition is not empty. Only empty partitions can be split in when a columnstore index exists on the table. Consider disabling the columnstore index before issuing the ALTER PARTITION statement, then rebuilding the columnstore index after ALTER PARTITION is complete.

Ok, we’ve got the same error message as before; this is expected as we know we cannot split non-empty partitions on tables that are stored as CCI. The syntax for disabling an index is straightforward, and we need to disable indexes on both tables.


ALTER INDEX [B_Custard] ON [dbo].[B_Phoebix] DISABLE
GO
ALTER INDEX [S_Custard] ON [dbo].[S_Phoebix] DISABLE
GO

We’ve disabled the index, so let’s try splitting the partition again:


ALTER PARTITION SCHEME pscPhoebix NEXT USED [FGPhoebix];
ALTER PARTITION FUNCTION pfnPhoebix() SPLIT RANGE(13261);

2014-08-13 13_56_53-Split_CCI.sql - (local).Phoebix

Ah, looks like disabling does not solve the issue. This is actually documented in the ‘Limitations and Restrictions’ section regarding altering partitions on  Books Online. So this somewhat contradicts the suggestion in the original error message. The error message I got is in full below.

Msg 7725, Level 16, State 1, Line 398
Alter partition function statement failed. Cannot repartition table ‘B_Phoebix’ by altering partition function ‘pfnPhoebix’ because its clustered index ‘B_Custard’ is disabled.

So, what are we to do? Well the first thing we should do is enable the index. Then we can look into workarounds.


ALTER INDEX [B_Custard] ON [dbo].[B_Phoebix] REBUILD
GO
ALTER INDEX [S_Custard] ON [dbo].[S_Phoebix] REBUILD
GO

There’s no way around it, we need to drop the index. Or do we? Seeing as we’re using partitioned tables here, the pragmatic, and faster, way of splitting a non-empty partition is to switch out the partition into a staging table, split the now empty partition, and switch the data back in. Really, it’s a case of modifying the import process above.

If you get any errors from running the script below, make sure you have rebuilt the indexes after disabling them.

-- create the empty rowset from the table we will switch into
select top 0 * into dbo.B_Phoebix_13267 from dbo.B_Phoebix
select top 0 * into dbo.S_Phoebix_13267 from dbo.S_Phoebix

--empty rowset is created on primary by default
--move to the filegroup the partitioned table is on
ALTER TABLE dbo.B_Phoebix_13267 ADD CONSTRAINT CONS_dbo_B_Phoebix_13267 PRIMARY KEY (Id) on FGPhoebix
ALTER TABLE dbo.S_Phoebix_13267 ADD CONSTRAINT CONS_dbo_S_Phoebix_13267 PRIMARY KEY (Id) on FGPhoebix

-- we don't need it (or can even keep it as we are adding CCI)
ALTER TABLE dbo.B_Phoebix_13267 DROP CONSTRAINT CONS_dbo_B_Phoebix_13267
ALTER TABLE dbo.S_Phoebix_13267 DROP CONSTRAINT CONS_dbo_S_Phoebix_13267

--add CCI
CREATE CLUSTERED COLUMNSTORE INDEX [PK_B_Phoebix_13267] ON dbo.B_Phoebix_13267 WITH (DROP_EXISTING = OFF)
CREATE CLUSTERED COLUMNSTORE INDEX [PK_S_Phoebix_13267] ON dbo.S_Phoebix_13267 WITH (DROP_EXISTING = OFF)

At this juncture we have a staging table that is exactly the same as the main table, so we can now switch the non empty partition we want to split into this new table.


ALTER TABLE [dbo].[B_Phoebix] SWITCH PARTITION $PARTITION.pfnPhoebix(13267) TO [dbo].[B_Phoebix_13267]
ALTER TABLE [dbo].[S_Phoebix] SWITCH PARTITION $PARTITION.pfnPhoebix(13267) To [dbo].[S_Phoebix_13267]

Running the stored proc again will confirm that the partition is now empty:


USE [Phoebix]
GO

EXEC [dbo].[LatestPartitionPreview]
@partitionName = N'Phoebix',
@sampleTable = N'B_Phoebix'
GO

2014-08-13 14_14_38-Split_CCI.sql

Great, split the partition and run the sproc again:


ALTER PARTITION SCHEME pscPhoebix NEXT USED [FGPhoebix];
ALTER PARTITION FUNCTION pfnPhoebix() SPLIT RANGE(13261);

USE [Phoebix]
GO

EXEC [dbo].[LatestPartitionPreview]
@partitionName = N'Phoebix',
@sampleTable = N'B_Phoebix'
GO

My result is below:

2014-08-13 14_16_36-Split_CCI.

We’re nearly done. All we need to do is switch in the staging table and we’ll be back the way we were.


ALTER TABLE dbo.B_Phoebix_13267 WITH CHECK ADD CONSTRAINT CK_B_Phoebix_13267 CHECK (([Id]=(13267)))
ALTER TABLE dbo.S_Phoebix_13267 WITH CHECK ADD CONSTRAINT CK_s_Phoebix_13267 CHECK (([Id]=(13267)))

--switch in to main table
ALTER TABLE dbo.B_Phoebix_13267 SWITCH TO dbo.B_Phoebix PARTITION $PARTITION.pfnPhoebix(13267)
ALTER TABLE dbo.S_Phoebix_13267 SWITCH TO dbo.S_Phoebix PARTITION $PARTITION.pfnPhoebix(13267)

Execute the sproc one last time to verify that the data has been switched in.

USE [Phoebix]
GO

EXEC [dbo].[LatestPartitionPreview]
@partitionName = N'Phoebix',
@sampleTable = N'B_Phoebix'
GO

2014-08-13 14_19_16-Split_CCI

So now we’ve been able to split the partition without having to drop the index. Great! And thanks to partitioning being so quick, it was a swift operation. It would get more complicated if you wanted to split the data in one partition into two partitions as you’d need to split the data out of the staging table into two staging tables, which won’t be as quick an operation as the example above.

At any rate, the example above shows that you can avoid having to drop clustered columnstore indexes on tables in order to split non-empty partitions.

Author: Richie Lee

Full time computer guy, part time runner. Full time Dad, part time blogger. Pokémon Nut. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.

2 thoughts on “How To Split Non-Empty Partitions When A Clustered Columnstore Index Exists on The Table”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s