Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


partitioning - swapping in from smaller higher grain to lower grain


partitioning - swapping in from smaller higher grain to lower grain

Author
Message
winston Smith
winston Smith
SSC Eights!
SSC Eights! (973 reputation)SSC Eights! (973 reputation)SSC Eights! (973 reputation)SSC Eights! (973 reputation)SSC Eights! (973 reputation)SSC Eights! (973 reputation)SSC Eights! (973 reputation)SSC Eights! (973 reputation)

Group: General Forum Members
Points: 973 Visits: 2040
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'.
*/


Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8249 Visits: 14368
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 details
SELECT 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
Attachments
partbounds.jpg (94 views, 157.00 KB)
james marriot
james marriot
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 26
invalid post
winston Smith
winston Smith
SSC Eights!
SSC Eights! (973 reputation)SSC Eights! (973 reputation)SSC Eights! (973 reputation)SSC Eights! (973 reputation)SSC Eights! (973 reputation)SSC Eights! (973 reputation)SSC Eights! (973 reputation)SSC Eights! (973 reputation)

Group: General Forum Members
Points: 973 Visits: 2040
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.
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8249 Visits: 14368
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
winston Smith
winston Smith
SSC Eights!
SSC Eights! (973 reputation)SSC Eights! (973 reputation)SSC Eights! (973 reputation)SSC Eights! (973 reputation)SSC Eights! (973 reputation)SSC Eights! (973 reputation)SSC Eights! (973 reputation)SSC Eights! (973 reputation)

Group: General Forum Members
Points: 973 Visits: 2040
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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search