partitioning - swapping in from smaller higher grain to lower grain
-
I have a table partitioned at the grain of Year.
I have a second table partitioned at the grain of month.
I could swap a partition from the table at the year grain (partition 3) to the table at the month grain (into partition 7) , but i cannot swap it back.
I get this error:
ALTER TABLE SWITCH statement failed. Range defined by partition 7 in table 'PartitionTest.dbo.swapouttable' is not a subset of range defined by partition 3 in table 'PartitionTest.dbo.orderdetail'.
I can post code to reproduce, but off hand, anyone know why i cannot swap back from the month table into the year table? Is it a one way trip without some extra steps?
EDIT: Below is a cheap n nasty script to repro the issue. NOTE - Im using sample data from Adventureworks2012
--create database
CREATE DATABASE PartitionTests
on Primary
(name = primary_data, filename = 'C:\SQLServerFiles\Partitioning\Files\db_1.mdf', size= 5MB, Filegrowth=500kb, MAXSIZE = 10MB),
FILEGROUP FG1
(NAME = FG1Data, filename = 'C:\SQLServerFiles\Partitioning\Files\FG_1.mdf', size= 500KB, Filegrowth=500kb, MAXSIZE = 10MB),
FILEGROUP FG2
(name = FG2Data, filename = 'C:\SQLServerFiles\Partitioning\Files\FG_2.mdf', size= 500KB, Filegrowth=500kb, MAXSIZE = 10MB)
LOG ON
(NAME = DB_LOG, filename = 'C:\SQLServerFiles\Partitioning\Files\log_1.ldf', size = 1MB, Filegrowth=500kb, MAXSIZE=5MB)
use partitiontests
--get sample data (used Adventureworks2012)]
select * into OrderDetail
from adventureworks2012.Purchasing.PurchaseOrderDetail
--create yearly partition function & scheme
create partition function YearlyPartitionFunction(datetime)
AS RANGE LEFT
FOR VALUES('2005-01-01','2006-01-01','2007-01-01','2008-01-01',
'2009-01-01', '2010-01-01')
CREATE PARTITION SCHEME YearlyPartitionScheme
AS PARTITION YearlyPartitionFunction TO
(FG2,FG2,FG2,FG2,FG2,FG2,FG2)
--create monthly partition and scheme
create partition function monthlyPartitionFunction(datetime)
AS RANGE LEFT
FOR VALUES('2005-05-01','2005-06-01','2005-07-01','2005-08-01',
'2005-09-01', '2005-10-01')
CREATE PARTITION SCHEME MonthlyPartitionScheme
AS PARTITION monthlyPartitionFunction TO
(FG2,FG2,FG2,FG2,FG2,FG2,FG2)
--partition source table
CREATE CLUSTERED INDEX ix_pk_pt on OrderDetail(PurchaseOrderID) on YearlyPartitionScheme(modifieddate)
--create swapout table
CREATE TABLE [dbo].[swapouttable](
[PurchaseOrderID] [int] NOT NULL,
[PurchaseOrderDetailID] [int] IDENTITY(1,1) NOT NULL,
[DueDate] [datetime] NOT NULL,
[OrderQty] [smallint] NOT NULL,
[ProductID] [int] NOT NULL,
[UnitPrice] [money] NOT NULL,
[LineTotal] [money] NOT NULL,
[ReceivedQty] [decimal](8, 2) NOT NULL,
[RejectedQty] [decimal](8, 2) NOT NULL,
[StockedQty] [decimal](9, 2) NOT NULL,
[ModifiedDate] [datetime] NOT NULL
)
--create clust index on swapout table to match that of source table
CREATE CLUSTERED INDEX ix_pk_pt2 on swapouttable(PurchaseOrderID)
on monthlyPartitionScheme(modifieddate)
--swapout rows - no problem
alter table orderdetail
switch partition 3 to swapoutTable Partition 7
--swap rows back - ERROR
alter table swapoutTable
switch partition 7 to orderdetail Partition 3
/*
ALTER TABLE SWITCH statement failed. Range defined by partition 7 in table 'PartitionTests.dbo.swapoutTable'
is not a subset of range defined by partition 3 in table 'PartitionTests.dbo.orderdetail'.
*/
-
Even though you have different partition functions you can swap the partition out because the structure is the same and the partition boundary in the source is a subset of the boundary in the destination. Running this query (credit to Dan Guzman) we can see the boundaries of each partition:
--paritioned table and index detailsSELECT OBJECT_NAME(p.object_id) AS ObjectName,
i.name AS IndexName,
p.index_id AS IndexID,
ds.name AS PartitionScheme,
p.partition_number AS PartitionNumber,
fg.name AS FileGroupName,
prv_left.value AS LowerBoundaryValue,
prv_right.value AS UpperBoundaryValue,
CASE pf.boundary_value_on_right
WHEN 1 THEN 'RIGHT'
ELSE 'LEFT'
END AS Range,
p.rows AS Rows
FROM sys.partitions AS p
JOIN sys.indexes AS i ON i.object_id = p.object_id
AND i.index_id = p.index_id
JOIN sys.data_spaces AS ds ON ds.data_space_id = i.data_space_id
JOIN sys.partition_schemes AS ps ON ps.data_space_id = ds.data_space_id
JOIN sys.partition_functions AS pf ON pf.function_id = ps.function_id
JOIN sys.destination_data_spaces AS dds2 ON dds2.partition_scheme_id = ps.data_space_id
AND dds2.destination_id = p.partition_number
JOIN sys.filegroups AS fg ON fg.data_space_id = dds2.data_space_id
LEFT JOIN sys.partition_range_values AS prv_left ON ps.function_id = prv_left.function_id
AND prv_left.boundary_id = p.partition_number - 1
LEFT JOIN sys.partition_range_values AS prv_right ON ps.function_id = prv_right.function_id
AND prv_right.boundary_id = p.partition_number
WHERE OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0
UNION ALL
--non-partitioned table/indexes
SELECT OBJECT_NAME(p.object_id) AS ObjectName,
i.name AS IndexName,
p.index_id AS IndexID,
NULL AS PartitionScheme,
p.partition_number AS PartitionNumber,
fg.name AS FileGroupName,
NULL AS LowerBoundaryValue,
NULL AS UpperBoundaryValue,
NULL AS Boundary,
p.rows AS Rows
FROM sys.partitions AS p
JOIN sys.indexes AS i ON i.object_id = p.object_id
AND i.index_id = p.index_id
JOIN sys.data_spaces AS ds ON ds.data_space_id = i.data_space_id
JOIN sys.filegroups AS fg ON fg.data_space_id = i.data_space_id
WHERE OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0
ORDER BY ObjectName,
IndexID,
PartitionNumber;
Notice that the source is a subset of the destination but the converse is not true. Since the partition swap is a DDL operation no data is evaluated to see if the data actually could conform to the boundary, it just says yes or no based on the metadata.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
invalid post
-
issue resolved with a constraint on the date column. seems the engine is happy to do the swap if the constraint enforces only rows that can only go into the destination partition.
-
winston Smith (1/28/2013)
issue resolved with a constraint on the date column. seems the engine is happy to do the swap if the constraint enforces only rows that can only go into the destination partition.That makes perfect sense since the swap must be carried out as purely a DDL operation and the check constraint can help the engine know the data in the partition subscribes to the destination partition boundaries without actually looking at the data during the switch.
For future onlookers it's worth expounding on the point of adding a check constraint. The constraint must be added WITH CHECK after the partition is switched into the swapouttable, i.e. having the check constraint in place before the switch is not good enough since the switch into that table is purely a DDL operation and so the data is not checked. This leaves the check constraint in a not trusted status per sys.check_constraints. Even when adding the constraint WITH NOCHECK before the switch and then CHECKing it after the swap the trusted status still cannot be attained.
@winston, just curious, why not simply define the swapouttable CI using the same partition scheme as the OrderDetail CI?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
My ideal choice is simplification, but we have a client who asked we investigate a bit of an over complex solution and this is one issue that cropped up.
Thanks for the help!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply