none
Switching out partitioned related tables(parent child) to staging tables not working. RRS feed

  • Question

  • I have parent table(File) and child child(FilePlan) partitioned on Fileid which is primary key of parent table(File) as well as FK of child table.

    Primary Key of Parent - FileId
    Partition key - FileId
    Primary Key of Child  - FilePlanId 
    Foreign Key of Child - FileId

    The partitioning is done on the FileId 

    After partitioning, trying to switch out a partition to staging tables , assuming that the data will be moved to both(parent & child) tables when the switch out is issued to parent, it didn't work.

    here is my question  

    1. Will the data be moved automatically to staging when you switch out partition on parent or we have to switch out partition separately for each table with separate partition key based on PK of each table.

    Tuesday, February 11, 2020 10:03 PM

Answers

All replies

  • I think you need two SWITCH PARTITION statements:

    ALTER TABLE FilePlan SWITCH PARTITION [PARTITION_NUMBER] TO StagingFilePlan;
    ALTER TABLE File SWITCH PARTITION [PARTITION_NUMBER] TO StagingFile;


    A Fan of SSIS, SSRS and SSAS

    Tuesday, February 11, 2020 10:25 PM
  • Hi Sundar12345,

    It may not be an auto-process. When using partitions in SQL Server, partition switching is a great feature to quickly truncate partitions or to load data in parallel.

    Partition switching is a metadata-only operation that updates the location of the data, no data is physically moved. And use the ALTER TABLE SWITCH PARTITION statement, specifying you are switching from the source partitioned table to the staging table.  This moves the data of the source partitioned table to the staging table, effectively truncating the source partitioned table. 

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, February 12, 2020 3:32 AM
  • 1. Will the data be moved automatically to staging when you switch out partition on parent or we have to switch out partition separately for each table with separate partition key based on PK of each table.

    Related data will not be moved automatically so you need separate SWITCH operations, one for the child partition and another for the parent partition. Furthermore, SWITCH is not permitted with a trusted foreign key constraint if referential integrity might be violated (e.g. switching parent table to parent staging could leave orphaned child rows). So you need to temporarily disable the constraint during the SWITCH.

    You probably don't really need the foreign key on the child staging table since RI was already enforced on the source. Below is an example script. Also see this answer.

    --create example tables
    CREATE PARTITION FUNCTION PF_FileId(int)
    	AS RANGE RIGHT FOR VALUES();
    CREATE PARTITION SCHEME PS_FileId 
    	AS PARTITION PF_FileId ALL TO([PRIMARY]);
    CREATE TABLE Parent(
    	FileId int NOT NULL CONSTRAINT PK_Parent PRIMARY KEY
    ) ON PS_FileId(FileId);
    CREATE TABLE Child(
    	FileId int NOT NULL
    		CONSTRAINT FK_Child_Parent FOREIGN KEY REFERENCES Parent(FileId)
    ) ON PS_FileId(FileId);
    CREATE TABLE ParentStaging(
    	FileId int NOT NULL
    		CONSTRAINT PK_ParentStaging PRIMARY KEY
    ) ON PS_FileId(FileId);
    CREATE TABLE ChildStaging(
    	FileId int NOT NULL --no foreign key constraint on child staging table
    ) ON PS_FileId(FileId);
    GO
    
    --example SWITCH
    SET XACT_ABORT ON;
    DECLARE @FileIdPartitionBoundary int = 1;
    BEGIN TRY
    	BEGIN TRAN;
    	ALTER TABLE dbo.Child
    		SWITCH PARTITION $PARTITION.PF_FileId(@FileIdPartitionBoundary)
    		TO dbo.ChildStaging PARTITION $PARTITION.PF_FileId(@FileIdPartitionBoundary);
    	--allow SWITCH of Parent to staging
    	ALTER TABLE dbo.Child
    		NOCHECK CONSTRAINT FK_Child_Parent;
    	ALTER TABLE dbo.Parent 
    		SWITCH PARTITION $PARTITION.PF_FileId(@FileIdPartitionBoundary) 
    		TO dbo.ParentStaging PARTITION $PARTITION.PF_FileId(@FileIdPartitionBoundary);
    	--switch into ChildStaging allowed without FK on ChildStaging
    	ALTER TABLE dbo.Child
    		SWITCH PARTITION $PARTITION.PF_FileId(@FileIdPartitionBoundary) 
    		TO dbo.ChildStaging PARTITION $PARTITION.PF_FileId(@FileIdPartitionBoundary);
    	--trust constraint after SWITCH
    	ALTER TABLE dbo.Child
    		CHECK CONSTRAINT FK_Child_Parent;
    	COMMIT;
    END TRY
    BEGIN CATCH
    	IF @@TRANCOUNT > 0 ROLLBACK;
    	THROW;
    END CATCH;
    GO



    Dan Guzman, Data Platform MVP, http://www.dbdelta.com


    Wednesday, February 12, 2020 12:51 PM
    Moderator
  • Dan, thank you so much for your help. In this case, The child table has its own primary key which i need to drop before switching, correct?

    Have one more question on your switching statement. I don't quite understand  the first switch(child) statement which is below.  

    ALTER TABLE dbo.Child
    SWITCH PARTITION $PARTITION.PF_FileId(@FileIdPartitionBoundary)
    TO dbo.ChildStaging PARTITION $PARTITION.PF_FileId(@FileIdPartitionBoundary);
    --allow SWITCH of Parent to staging 

    Could you please shed some light on this .

    Thanks for your time in advance.  
    Wednesday, February 12, 2020 8:24 PM
  • Dan, thank you so much for your help. In this case, The child table has its own primary key which i need to drop before switching, correct?

    The source and target table must have the same schema and indexes, including primary, key before the SWITCH.

    Have one more question on your switching statement. I don't quite understand  the first switch(child) statement which is below.  

    ALTER TABLE dbo.Child
    SWITCH PARTITION $PARTITION.PF_FileId(@FileIdPartitionBoundary)
    TO dbo.ChildStaging PARTITION $PARTITION.PF_FileId(@FileIdPartitionBoundary);

    The expression "$PARTITION.PF_FileId(@FileIdPartitionBoundary)" returns the partition number of the partition containing the @FileIdPartitonBoundary value (partition number with value 1 in the example). This is functionally identical to hard-coding the partition number (e.g. "ALTER TABLE dbo.Child SWITCH PARTITION 1 TO dbo.ChildStaging PARTITION 1;") but provides more flexibility when you want to use the same script for different partitions without needing to find the actual partition number beforehand.

    The target partition must of course be empty.



    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    • Proposed as answer by Lily Lii Thursday, February 13, 2020 6:26 AM
    Wednesday, February 12, 2020 11:20 PM
    Moderator
  • Thank you Dan. I didn't pose my question clearly. The same statement(below) repeated twice, we we need the first one with comment(--allow SWITCH of Parent to staging) 

    ALTER TABLE dbo.Child
    		SWITCH PARTITION $PARTITION.PF_FileId(@FileIdPartitionBoundary)
    		TO dbo.ChildStaging PARTITION $PARTITION.PF_FileId(@FileIdPartitionBoundary);
    	--allow SWITCH of Parent to staging

    	ALTER TABLE dbo.Child
    		SWITCH PARTITION $PARTITION.PF_FileId(@FileIdPartitionBoundary) 
    		TO dbo.ChildStaging PARTITION $PARTITION.PF_FileId(@FileIdPartitionBoundary);
    	--trust constraint after SWITCH

    Thursday, February 13, 2020 4:02 PM
  • I think if there is no FK constraint PK_Child_Parent on the ChildStaging table, you only need two SWITCH statements:

    SET XACT_ABORT ON;
    DECLARE @FileIdPartitionBoundary int = 1;
    BEGIN TRY
    	BEGIN TRAN;
    		-- First switch the partition from Child to ChildStaging 
    		ALTER TABLE dbo.Child
    		SWITCH PARTITION $PARTITION.PF_FileId(@FileIdPartitionBoundary)
    		TO dbo.ChildStaging PARTITION $PARTITION.PF_FileId(@FileIdPartitionBoundary);
    		-- And then switch the partition from Parent to ParentStaging 
    		ALTER TABLE dbo.Parent 
    		SWITCH PARTITION $PARTITION.PF_FileId(@FileIdPartitionBoundary) 
    		TO dbo.ParentStaging PARTITION $PARTITION.PF_FileId(@FileIdPartitionBoundary);
    	COMMIT;
    END TRY
    BEGIN CATCH
    	IF @@TRANCOUNT > 0 ROLLBACK;
    	THROW;
    END CATCH;


    A Fan of SSIS, SSRS and SSAS

    Thursday, February 13, 2020 4:36 PM
  • I am not sure why Dan has two SWTICH statements for the child, but I spotted this, and I think this is holding its promise:

    --trust constraint after SWITCH
         ALTER TABLE dbo.Child
              CHECK CONSTRAINT FK_Child_Parent;

    This enables the constraints on dbo.Child, but it does perform validation on existing values, so the constraint will remain untrusted.
    The correct way to do the above would be

      ALTER TABLE dbo.Child WITH CHECK CHECK Constraint FK_Child_Parent

    Although this will make the operation take a longer time, since the Child table has to be scanned in its entirety.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, February 13, 2020 10:12 PM
  • Thanks Dan and Erland. I really appreciate it.
    Friday, February 14, 2020 1:01 AM
  • Hi Sundar12345,

    So glad that you have solved the confusion, you could kindly mark Dan's and Erland's posts as answers. This would benefit the community, and also encourage the community member to keep working on your issues.

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, February 14, 2020 1:33 AM
  • I am not sure why Dan has two SWTICH statements for the child

    This was an error on my part. I had planned to include scripts with and without the staging table FK for illustration but accidently left the redundant statement when I posted only the version without the FK.

    The correct way to do the above would be

      ALTER TABLE dbo.Child WITH CHECK CHECK Constraint FK_Child_Parent

    Good catch. The missing CHECK is almost as bad as a missing not ;-)


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Friday, February 14, 2020 11:15 AM
    Moderator
  • Thanks again Dan and Erland. I really appreciate your quick response.

    • Marked as answer by Sundar12345 Friday, February 14, 2020 3:42 PM
    Friday, February 14, 2020 3:42 PM