none
archiving huge data RRS feed

Answers

  • You can use the SQL Server 2016 feature Temporal Tables. Partitioning would be complementary.
    Wednesday, May 29, 2019 8:29 AM
  • Yep, you can definitely use partition to archive the data. SWITCH command should be used

    Advantage of using partition is that there is no physical deletion of the data,

    the SWITCH statement will be a meta-data change which will take less than one second to complete.

    https://www.sqlshack.com/archiving-sql-server-data-using-partitions/


    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

    Wednesday, May 29, 2019 4:53 AM
    Moderator
  • If a table is partitioned by one column by creating an index on it, the rows are distributed over the partitions for this index but for other indexes, all rows lie in same partition. 

    when a table is partitioned this way, is it really partitioned?

    I am trying to partition the entire table so that old data lie in one partition and the new ones in the other.

    When a table and its indexes are partitioned differently (or not partitioned), then the table and its indexes are not aligned. This will prevent one from using SWITCH to efficiently archive data per partition.

    The data in your image (presumably from sys.partitions) suggests only one non-clustered index is partitioned and the table (clustered index) and other non-clustered indexes are not partitioned at all. You will need to partition all indexes similarly (i.e. same or equivalent partition function and scheme) in order to use SWITCH.

    Post DDL for your table and partition objects if you need help with a script to align the table and indexes. Note that this may require index key changes since the partitioning column must be part of the primary key, unique constraints, and unique indexes.


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

    Wednesday, May 29, 2019 10:29 AM
    Moderator
  • First of all, I want to know what's the meaning of the partitioning shown in the image.

    When indexes are partitioned differently, or not at all, that means the partitions are not aligned. Aligned indexes are most common with table partitioning but it is not a requirement. It is not possible to use partitioning features like SWITCH and partition level TRUNCATE unless indexes are aligned.


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

    Sunday, June 23, 2019 2:23 PM
    Moderator

All replies

  • Short question, short answer: In chunks.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, May 28, 2019 8:13 AM
    Moderator
  • Thank you.

    Can we use partitioning as an archiving approach?

    what are benefits and limitations of partitioning?

    Tuesday, May 28, 2019 8:22 AM
  • 

    If a table is partitioned by one column by creating an index on it, the rows are distributed over the partitions for this index but for other indexes, all rows lie in same partition. 

    when a table is partitioned this way, is it really partitioned?

    I am trying to partition the entire table so that old data lie in one partition and the new ones in the other.
    • Edited by Curendra Wednesday, May 29, 2019 4:07 AM
    Wednesday, May 29, 2019 4:03 AM
  • Yep, you can definitely use partition to archive the data. SWITCH command should be used

    Advantage of using partition is that there is no physical deletion of the data,

    the SWITCH statement will be a meta-data change which will take less than one second to complete.

    https://www.sqlshack.com/archiving-sql-server-data-using-partitions/


    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

    Wednesday, May 29, 2019 4:53 AM
    Moderator
  • Hi Curendra,

    Please refer to Archiving SQL Server data using Partitions.
    Hope this could help you.

    Best regards,
    Cathy Ji

    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, May 29, 2019 6:33 AM
  • You can use partition to archive the huge data, switch command should be used to do this. 
    Wednesday, May 29, 2019 6:44 AM
  • You can use the SQL Server 2016 feature Temporal Tables. Partitioning would be complementary.
    Wednesday, May 29, 2019 8:29 AM
  • If a table is partitioned by one column by creating an index on it, the rows are distributed over the partitions for this index but for other indexes, all rows lie in same partition. 

    when a table is partitioned this way, is it really partitioned?

    I am trying to partition the entire table so that old data lie in one partition and the new ones in the other.

    When a table and its indexes are partitioned differently (or not partitioned), then the table and its indexes are not aligned. This will prevent one from using SWITCH to efficiently archive data per partition.

    The data in your image (presumably from sys.partitions) suggests only one non-clustered index is partitioned and the table (clustered index) and other non-clustered indexes are not partitioned at all. You will need to partition all indexes similarly (i.e. same or equivalent partition function and scheme) in order to use SWITCH.

    Post DDL for your table and partition objects if you need help with a script to align the table and indexes. Note that this may require index key changes since the partitioning column must be part of the primary key, unique constraints, and unique indexes.


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

    Wednesday, May 29, 2019 10:29 AM
    Moderator
  • 

    If a table is partitioned by one column by creating an index on it, the rows are distributed over the partitions for this index but for other indexes, all rows lie in same partition. 

    when a table is partitioned this way, is it really partitioned?

    I am trying to partition the entire table so that old data lie in one partition and the new ones in the other.

    Thank you all for the replies.

    First of all, I want to know what's the meaning of the partitioning shown in the image.

    Tuesday, June 18, 2019 10:52 AM
  • First of all, I want to know what's the meaning of the partitioning shown in the image.

    When indexes are partitioned differently, or not at all, that means the partitions are not aligned. Aligned indexes are most common with table partitioning but it is not a requirement. It is not possible to use partitioning features like SWITCH and partition level TRUNCATE unless indexes are aligned.


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

    Sunday, June 23, 2019 2:23 PM
    Moderator