Hi,
This is the continuation of my previous post where we tried to partition the parent table (sales.orders) inside world wide importers Database. Now we will see what happens if we do the partitioning for the child table (sales.orderlines).
Sales.Orderlines table is having primary key on the column OrderLineID where as Sales.Orders has it on OrderID.
This is the continuation of my previous post where we tried to partition the parent table (sales.orders) inside world wide importers Database. Now we will see what happens if we do the partitioning for the child table (sales.orderlines).
Sales.Orderlines table is having primary key on the column OrderLineID where as Sales.Orders has it on OrderID.
Imagine in the real world you have a scenario where you need to partition the existing table with many rows inside them and the primary Key being established on the column not you are intended to partition with. Hence keeping that in mind now I need to drop the constraint (To server the business context) and re-create it as NON clustered and then create the clustered index on OrderID (to achieve partitioning).
So I dropped and recreated the constraint(Primary key) as NON Clustered like below
USE [WideWorldImporters]
GO
/****** Object: Index [PK_Sales_OrderLines] Script Date: 28/05/2020 09:59:59 ******/
ALTER TABLE [Sales].[OrderLines] DROP CONSTRAINT [PK_Sales_OrderLines] WITH ( ONLINE = OFF )
GO
/****** Object: Index [PK_Sales_OrderLines] Script Date: 28/05/2020 09:59:59 ******/
ALTER TABLE [Sales].[OrderLines] ADD CONSTRAINT [PK_Sales_OrderLines] PRIMARY KEY NONCLUSTERED
(
[OrderLineID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [USERDATA]
GO
When I am trying to create Clustered Index I got the below error as there is one columstore index persisting
CREATE CLUSTERED INDEX PK_Sales_Orders_lines ON [Sales].[OrderLines] (orderid)
WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON funcscheme(orderid)
GO
Msg 35350, Level 16, State 1, Line 15
The statement failed because a columnstore index on a partitioned table must be partition-aligned with the base table. Consider dropping the columnstore index before creating a new clustered index.
As my focus is not on this I am dropping the Columstore index and proceeding further.
Note that the OrderId has duplicates hence the distribution of the rows would differ from partition of sales.orders.
Let's prepare the staging table ready to implement the metadata operation switch.
As there are non clustered indexes and constraints I disabled all of them similar to what I did in the previous post.
alter table sales.orderlines nocheck constraint all
alter index FK_Sales_OrderLines_OrderID on sales.orderlines disable
alter index FK_Sales_OrderLines_PackageTypeID on sales.orderlines disable
alter index IX_Sales_OrderLines_AllocatedStockItems on sales.orderlines disable
alter index IX_Sales_OrderLines_Perf_20160301_01 on sales.orderlines disable
alter index IX_Sales_OrderLines_Perf_20160301_02 on sales.orderlines disable
alter index PK_Sales_OrderLines on sales.orderlines disable
when I fired the switch the operation has been completed with out any issues.
alter table sales.OrderLines switch partition 1 to sales.OrderLines_load partition 1
So will now try to enable the constraints back and see if we have any problems. As expected no problems has been faced. If we look at the data now there are no rows in the child table for the orderid=20000 but it has rows in the parent table
Unlike the parent table partitioning where we end up with errors as a result of missing rows from the base table here even though we didn't face any errors but there is missing data inside the child tables.
My suggestion is not to partition the tables which has primary and foreign key relationships if you are intended to perform operations like Switch as the data consistency will not be maintained.
This is tricky subject hence please feel free to share your comments and correct me if I am wrong anywhere.
Comments