none
sql server partition parent table and reference not partition child table RRS feed

  • Question

  •  

    Hi,

     

    I have two tables in SQL Server 2008 R2, Parent and Child Table.  

    Parent has date time, and it is partitioned monthly,  there is a Child table which just refer the Parent table using Foreign key relation.   

    is there any problem the non-partitioned child table referring to a partitioned parent table?

     

    Thanks,

    Areef

    Monday, February 10, 2014 1:09 PM

Answers

  • As long as you delete with the DELETE statement there is no problem. Either delete children first, or set up the constraint with ON CASCADE DELETE.

    On the other hand, if you delete with the DELETE statement, why do you have partitioning? If you want to delete with ALTER TABLE SWITCH, you need to parition both tables, and your operation needs to drop the FK constraints before the switches and delete after. (Personally, I think this reduces the value of the partitioning feature considerably.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, February 10, 2014 2:25 PM
  • The tables will need to be offline for the operation. "Offline" here, means that you wrap the entire operation in a transaction. Ideally, this transaction would:

    1) Drop the foreign key.
    2) Use ALTER TABLE SWITCH to drop the old data.
    3) Use ALTER PARTITION FUNCTION to drop the old empty partition.
    4) Use ALTER PARTITION FUNCTION to add a new empty partition.
    5) Reapply the foreign keys WITH CHECK.

    All but the last operation are metadata-only operation (provided that you do them right). To perform the last operation, SQL Server must scan the child tbale and verify that all keys are present in the parent table. This can take some time for larger tables. During the transaction, SQL Server holds Sch-M locks on the table, which means that are entirely inaccessible, even for queries running with NOLOCK.

    You avoid this the scan by applying the fkey constraint WITH NOCHECK, but this can have impact on query plans, as SQL Server will not consider the constraint as trusted.

    An alternative which should not be entirely dismissed is to use partitioned views instead. With partitioned views, the foreign keys are not an issue, because each partition is a pair of tables, with its own local fkey.

    As for the second question: it appears to be completely pointless to partition the parent, but not the child table. Or does the child table only have rows for a smaller set of the rows in the parent?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, February 11, 2014 2:57 PM

All replies

  • I do not see any problem :-)

    Monday, February 10, 2014 1:16 PM
  • Thank you so much Flemming!!

    Monday, February 10, 2014 1:19 PM
  • Depends on why you have partitioned the parent table. The most common reason to partition tables is that you want to be table to age out old partitions quickly. If that is the case, yes, you have a problem, and you need to redesign the child table to be partitioned in the same way as the parent.

    Now, I can guess that the child table does not have the date column that the parent table is partitioned by. To get this going, you would need to add the partitioning column to the child table. Or change the partitioning scheme to use the PK column, assuming that this is a montonically increasing id column.

    There will still be problems to perform a switch, since you will need to drop and recreate the foreign keys each time. But at least you will be able to recreate.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, February 10, 2014 1:22 PM
  • In my case, I have to delete data from both parent and child which is 3 months old. And add new partition everymonth (likely first sunday of each month). And delete old partiton which is not having records. Recursive operation everymonth. Can I cascade parent and Clild tables, when parent recrods are deleted clild data will be deleted automatically. How to achive this?
    Monday, February 10, 2014 2:12 PM
  • As long as you delete with the DELETE statement there is no problem. Either delete children first, or set up the constraint with ON CASCADE DELETE.

    On the other hand, if you delete with the DELETE statement, why do you have partitioning? If you want to delete with ALTER TABLE SWITCH, you need to parition both tables, and your operation needs to drop the FK constraints before the switches and delete after. (Personally, I think this reduces the value of the partitioning feature considerably.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, February 10, 2014 2:25 PM
  • Hi Team,

    I have a master and child tables with Foreign key relation,  I want to table partition both Master and Child tables monthly basic.

    Question 1: To add a new partition every month, downtime is required?  Disable foreign key relation and do SPLIT?

    Question 2: If Master table is portioned every month and Child is referencing Master table, on every month SPLIT, downtime is required?  Can a non-partitioned child table refer partitioned master table?

    Thanks, Areef

    • Merged by Papy Normand Tuesday, February 11, 2014 9:03 PM Same thread
    Tuesday, February 11, 2014 12:19 PM
  • Thanks for your valuable reply Erland!!

    Can you please clarify my below two questions?

    I have a master and child tables with Foreign key relation,  I want to partition both Master and Child tables monthly basic.

    Question 1: To add a new partition every month, downtime is required?  Disable foreign key relation and do SPLIT?

    Question 2: If Master table is portioned every month and Child is referencing Master table, on every month SPLIT, downtime is required?  Can a non-partitioned child table refer partitioned master table?

    Thanks, Areef

    Tuesday, February 11, 2014 2:00 PM
  • The tables will need to be offline for the operation. "Offline" here, means that you wrap the entire operation in a transaction. Ideally, this transaction would:

    1) Drop the foreign key.
    2) Use ALTER TABLE SWITCH to drop the old data.
    3) Use ALTER PARTITION FUNCTION to drop the old empty partition.
    4) Use ALTER PARTITION FUNCTION to add a new empty partition.
    5) Reapply the foreign keys WITH CHECK.

    All but the last operation are metadata-only operation (provided that you do them right). To perform the last operation, SQL Server must scan the child tbale and verify that all keys are present in the parent table. This can take some time for larger tables. During the transaction, SQL Server holds Sch-M locks on the table, which means that are entirely inaccessible, even for queries running with NOLOCK.

    You avoid this the scan by applying the fkey constraint WITH NOCHECK, but this can have impact on query plans, as SQL Server will not consider the constraint as trusted.

    An alternative which should not be entirely dismissed is to use partitioned views instead. With partitioned views, the foreign keys are not an issue, because each partition is a pair of tables, with its own local fkey.

    As for the second question: it appears to be completely pointless to partition the parent, but not the child table. Or does the child table only have rows for a smaller set of the rows in the parent?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, February 11, 2014 2:57 PM
  • Hello Areef,

    You have posted the same question in the SQL Server Database Engine Forum

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/6b206847-780b-480c-a9bc-a3d344b2d947/sql-server-partition-parent-table-and-reference-not-partition-child-table?forum=sqldatabaseengine

    This thread has not been answered for the moment but it includes already proposals of answer . I will do the merge.

    Have a nice day


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.


    • Edited by Papy Normand Tuesday, February 11, 2014 8:59 PM Warning of merge.
    Tuesday, February 11, 2014 8:57 PM
  • Hi Erland, Thank you so much for your reply!! Very informative. One small question, in Table Partition :- if I want to drop old partion and add new partiton everymonth, do I have to drop/diable foreign key relation between Parent & Child table? Assume both Parent and Child tables are partitoned monthly.
    • Edited by AreefAli Monday, February 17, 2014 7:28 AM
    Monday, February 17, 2014 6:59 AM
  • Yes, in your script that switches out the old data and switches in the new partition, you need to start with dropping the foreign keys and reapplying them at the end of the script. I don't recall for sure that you need to drop the constraints; it may suffice to only disable them and reenable them. As I said, I recommend that you enable then WITH CHECK, so that the constraints are evaluated when then are reenabled/reapplied, although this takes longer time.

    Before you do this in production, you should definitely play with this on your own, so that you understand the process.

    As for having to fiddle with the foreign keys, I think that is a big minus for the feature. It would be nice if we could switch several tables at the same time, so that the foreign keys could remain in place throughout.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, February 17, 2014 8:51 AM
  • Hi Erland,

    Thank you so much for the clarification.  You really helped me :)

      

    Monday, February 17, 2014 11:16 AM