June 14, 2021

OrdersStaging ( OrderDate datetime2(0) not null

Menu.
Splitting and Merging Non-Empty Partitions in Columnstore Indexes.
we discussed how to implement Sliding Window pattern in the tables with columnstore indexes.
As I promised, today we are going to focus on partition management in such tables.
create partition function pfOrders(datetime2(0)) as range right for values (”2016-11-01”,”2016-12-01”,”2017-01-01”,”2017-02-01” ,”2017-03-01”,”2017-04-01”,”2017-05-01”,”2017-06-01” ,”2017-07-01”,”2017-08-01”,”2017-09-01”,”2017-10-01” ,”2017-11-01”,”2017-12-01”,”2018-01-01”); create partition scheme psOrders as partition pfOrders all to ([Primary]); create table dbo.

Orders ( OrderDate datetime2(0) not null

OrderId int not null, Placeholder char(100), ) on psOrders(OrderDate); create table dbo.

OrderLineItems ( OrderDate datetime2(0) not null

OrderId int not null, OrderLineItemId int not null, Placeholder char(100), ) on psOrders(OrderDate); go — Left-most and right-most are empty ;with N1(C) as (select 0 union all select 0) — 2 rows ,N2(C) as (select 0 from N1 as t1 cross join N1 as t2) — 4 rows ,N3(C) as (select 0 from N2 as t1 cross join N2 as t2) — 16 rows ,N4(C) as (select 0 from N3 as t1 cross join N3 as t2) — 256 rows ,N5(C) as (select 0 from N4 as t1 cross join N4 as t2) — 65,536 rows ,Ids(Id) as (select row_number() over (order by (select null)) from N5) insert into dbo.
Orders(OrderDate, OrderId) select dateadd(day,Id % 390,”2016-11-01”), ID from Ids; insert into dbo.

OrderLineItems(OrderDate,OrderId

OrderLineItemId) select OrderDate, OrderId, OrderId from dbo.
Orders; go create clustered columnstore index CCI_Orders on dbo.
Orders on psOrders(OrderDate); create clustered columnstore index CCI_OrderLineItems on dbo.

OrderLineItems on psOrders(OrderDate); go select t.object_id

i.index_id, i.name as [Index], p.partition_number, p.[Rows], p.data_compression_desc, fg.name as [Filegroup], sum(a.total_pages) as TotalPages, sum(a.used_pages) as UsedPages, sum(a.data_pages) as DataPages, sum(a.total_pages) * 8 as TotalSpaceKB, sum(a.used_pages) * 8 as UsedSpaceKB, sum(a.data_pages) * 8 as DataSpaceKB from sys.tables t with (nolock) join sys.indexes i with (nolock) on t.object_id = i.object_id join sys.partitions p with (nolock) on i.object_id = p.object_id AND i.index_id = p.index_id join sys.allocation_units a with (nolock) on p.partition_id = a.container_id join sys.filegroups fg with (nolock) on a.data_space_id = fg.data_space_id where t.object_id = object_id(N”dbo.
Orders”) group by t.object_id, i.index_id, i.name, p.partition_number, p.[Rows], p.data_compression_desc, fg.name order by i.index_id, p.partition_number; create partition function pfOrdersStaging(datetime2(0)) as range right for values (”2016-11-01”,”2016-12-01”,”2017-01-01”); create partition scheme psOrdersStaging as partition pfOrdersStaging all to ([Primary]); create table dbo.
OrdersStaging ( OrderDate datetime2(0) not null, OrderId int not null, Placeholder char(100), ) on psOrdersStaging(OrderDate); create clustered columnstore index CCI_OrdersStaging on dbo.

OrdersStaging on psOrdersStaging(OrderDate); create table dbo

OrderLineItemsStaging ( OrderDate datetime2(0) not null

OrderId int not null, OrderLineItemId int not null, Placeholder char(100), ) on psOrdersStaging(OrderDate); create clustered columnstore index CCI_OrderLineItemsStaging on dbo.

OrderLineItemsStaging on psOrdersStaging(OrderDate); alter table dbo

Orders switch partition 2 to dbo.
OrdersStaging partition 2; alter table dbo.
OrderLineItems switch partition 2 to dbo.
OrderLineItemsStaging partition 2; alter table dbo.
Orders switch partition 3 to dbo.
OrdersStaging partition 3; alter table dbo.
OrderLineItems switch partition 3 to dbo.
OrderLineItemsStaging partition 3; alter partition function pfOrders() merge range (”2016-12-01”); drop index CCI_OrdersStaging on dbo.
OrdersStaging; drop index CCI_OrderLineItemsStaging on dbo.
OrderLineItemsStaging; go — Offline with Sch-M alter partition function pfOrdersStaging() merge range (”2016-12-01”); go create clustered columnstore index CCI_OrdersStaging on dbo.
OrdersStaging on psOrdersStaging(OrderDate); create clustered columnstore index CCI_OrderLineItemsStaging on dbo.
OrderLineItemsStaging on psOrdersStaging(OrderDate); go — Switching partitions back alter table dbo.
OrdersStaging switch partition 2 to dbo.
Orders partition 2; alter table dbo.
OrderLineItemsStaging switch partition 2 to dbo.
OrderLineItems partition 2; — Spliting November 2017 partition (#13) — Recreating Staging objects first drop table if exists dbo.
OrdersStaging; drop table if exists dbo.
OrderLineItemsStaging; if exists(select * from sys.partition_schemes where name = ”psOrdersStaging”) drop partition scheme psOrdersStaging; if exists(select * from sys.partition_functions where name = ”pfOrdersStaging”) drop partition function pfOrdersStaging; go create partition function pfOrdersStaging(datetime2(0)) as range right for values (”2017-11-01”,”2017-12-01”); create partition scheme psOrdersStaging as partition pfOrdersStaging all to ([Primary]); create table dbo.
OrdersStaging ( OrderDate datetime2(0) not null, OrderId int not null, Placeholder char(100), ) on psOrdersStaging(OrderDate); create clustered columnstore index CCI_OrdersStaging on dbo.
OrdersStaging on psOrdersStaging(OrderDate); create table dbo.
OrderLineItemsStaging ( OrderDate datetime2(0) not null, OrderId int not null, OrderLineItemId int not null, Placeholder char(100), ) on psOrdersStaging(OrderDate); create clustered columnstore index CCI_OrderLineItemsStaging on dbo.
OrderLineItemsStaging on psOrdersStaging(OrderDate); — Switching partitions to the staging table alter table dbo.
Orders switch partition 13 to dbo.
OrdersStaging partition 2; alter table dbo.
OrderLineItems switch partition 13 to dbo.
OrderLineItemsStaging partition 2; go — Splitting partition in the main table alter partition scheme psOrders next used [PRIMARY]; alter partition function pfOrders() split range (”2017-11-15”); go — Now spllitting partition in the Staging table drop index CCI_OrdersStaging on dbo.
OrdersStaging; drop index CCI_OrderLineItemsStaging on dbo.
OrderLineItemsStaging; go — Offline with Sch-M alter partition scheme psOrdersStaging next used [PRIMARY]; alter partition function pfOrdersStaging() split range (”2017-11-15”); go create clustered columnstore index CCI_OrdersStaging on dbo.
OrdersStaging on psOrdersStaging(OrderDate); create clustered columnstore index CCI_OrderLineItemsStaging on dbo.
OrderLineItemsStaging on psOrdersStaging(OrderDate); — Switching partitions back alter table dbo.
OrdersStaging switch partition 2 to dbo.
Orders partition 13; alter table dbo.
OrdersStaging switch partition 3 to dbo.
Orders partition 14; alter table dbo.
OrderLineItemsStaging switch partition 2 to dbo.
OrderLineItems partition 13; alter table dbo.
OrderLineItemsStaging switch partition 3 to dbo.
OrderLineItems partition 14; — If data in the table is static — Let”s merge November-December 2016 and January 2017 drop table if exists dbo.
OrdersStaging; drop table if exists dbo.
OrderLineItemsStaging; if exists(select * from sys.partition_schemes where name = ”psOrdersStaging”) drop partition scheme psOrdersStaging; if exists(select * from sys.partition_functions where name = ”pfOrdersStaging”) drop partition function pfOrdersStaging; go create partition function pfOrdersStaging(datetime2(0)) as range right for values (”2016-11-01”,”2017-01-01”,”2017-02-01”); create partition scheme psOrdersStaging as partition pfOrdersStaging all to ([Primary]); create table dbo.
OrdersStaging ( OrderDate datetime2(0) not null, OrderId int not null, Placeholder char(100), ) on psOrdersStaging(OrderDate); create clustered columnstore index CCI_OrdersStaging on dbo.
OrdersStaging on psOrdersStaging(OrderDate); create table dbo.
OrderLineItemsStaging ( OrderDate datetime2(0) not null, OrderId int not null, OrderLineItemId int not null, Placeholder char(100), ) on psOrdersStaging(OrderDate); create clustered columnstore index CCI_OrderLineItemsStaging on dbo.
OrderLineItemsStaging on psOrdersStaging(OrderDate); — Copying data from Main to Staging tables insert into dbo.

OrdersStaging(OrderDate,OrderId,Placeholder) select OrderDate

OrderId, Placeholder from dbo.

Orders where $Partition.pfOrders(OrderDate) in (2,3); insert into dbo

OrderLineItemsStaging(OrderDate,OrderId,OrderLineItemId,Placeholder) select OrderDate, OrderId, OrderLineItemId, Placeholder from dbo.
OrderLineItems where $Partition.pfOrders(OrderDate) in (2,3); go — Merging partitions in Staging tables drop index CCI_OrdersStaging on dbo.
OrdersStaging; drop index CCI_OrderLineItemsStaging on dbo.
OrderLineItemsStaging; go alter partition function pfOrdersStaging() merge range (”2017-01-01”); go create clustered columnstore index CCI_OrdersStaging on dbo.
OrdersStaging on psOrdersStaging(OrderDate); create clustered columnstore index CCI_OrderLineItemsStaging on dbo.
OrderLineItemsStaging on psOrdersStaging(OrderDate); go — Creating temporary table to switch original — partitions from the main table create partition function pfOrdersTmp(datetime2(0)) as range right for values (”2016-11-01”,”2017-01-01”,”2017-02-01”); create partition scheme psOrdersTmp as partition pfOrdersTmp all to ([Primary]); create table dbo.
OrdersTmp ( OrderDate datetime2(0) not null, OrderId int not null, Placeholder char(100), .

) on psOrdersTmp(OrderDate); create clustered columnstore index CCI_OrdersTmp on dbo

OrdersTmp on psOrdersTmp(OrderDate); create table dbo.
OrderLineItemsTmp ( OrderDate datetime2(0) not null, OrderId int not null, OrderLineItemId int not null, Placeholder char(100), ) on psOrdersTmp(OrderDate); create clustered columnstore index CCI_OrderLineItemsTmp on dbo.
OrderLineItemsTmp on psOrdersTmp(OrderDate); — Final steps.
Let”s do it in transaction — All operations are on metadata level set xact_abort on begin tran — Switching original partitions out alter table dbo.
Orders switch partition 2 to dbo.
OrdersTmp partition 2; alter table dbo.
OrderLineItems switch partition 2 to dbo.
OrderLineItemsTmp partition 2; alter table dbo.
Orders switch partition 3 to dbo.
OrdersTmp partition 3; alter table dbo.
OrderLineItems switch partition 3 to dbo.
OrderLineItemsTmp partition 3; — Merge alter partition function pfOrders() merge range (”2017-01-01”); — Switching partitions from the staging table alter table dbo.
OrdersStaging switch partition 2 to dbo.
Orders partition 2; alter table dbo.
OrderLineItemsStaging switch partition 2 to dbo.
OrderLineItems partition 2; commit; go — Dropping temporary tables drop table dbo.
OrderLineItemsTmp; drop table dbo.
OrdersTmp; drop partition scheme psOrdersTmp; drop partition function pfOrdersTmp; Source code is available for.
This entry was posted in , , , , , and tagged , on by.
Implementing Sliding Windows Data Purge Pattern with Columnstore Indexes Locking in Microsoft SQL Server (Part 21) – Intro Into Transaction Management and Error Handling Leave a Reply Cancel reply.
Required fields are marked Name Email Subscribe Search for: November 2017 M T W T F S S  12345 6789101112 13141516171819 202223242526 272930.

Leave a Reply

Your email address will not be published. Required fields are marked *