none
Question on table partitioning

    Question

  • Here is my scenario i have 2 tables Archive & Swap_In_Out table. Archive table is partition for each month on date column and was able to move data  In  &  Out b/w Archive & Swap_In_Out tables.

    I have couple of question on above process :

    What is the difference in having   same partition function,schema,Indexes,partitions on Swap_In_Out table as well,just like Archive  table for moving the data between the partitions 

    VS

    Moving data from partition table Archive  to Non partition table Swap_In_Out (which is on the same File Group)

     
    Tuesday, June 12, 2018 6:51 PM

All replies

  • Hi grdesai,

    In your scenario, you want to know the difference between move data from one partitioned table to another same partitioned table and move data from one partitioned table to another non partitioned table. Right?

    Per my understanding the difference will be the performance issue as move to a same partitioned table will be faster than move to a non partitioned table. For more details, check the Benefits of Partitioning.

    And since I don't have so much data to test, I would suggest you to use Performance Monitor to make a comparison. 

    Thanks,
    Xi Jin.


    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, June 13, 2018 8:27 AM
  • >>>What is the difference in having   same partition function,schema,Indexes,partitions on Swap_In_Out table as >>>well,just like Archive  table for moving the data between the partitions 

    Your data will be move moved to almost instantly (as it is metadata movement)  

    https://www.mssqltips.com/sqlservertip/2780/archiving-sql-server-data-using-partitioning/

    Make your archive table partitioned 


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, June 14, 2018 5:17 AM
    Moderator