partition table: transactionHistory/ transactionHistoryArchive scenario

Question

Hi    I am tying to understand how partition table works  I am using this link of TeckNet:

It says:   transactionHistory table holds  data partitioned by month for all transactions  during and after september first  and not earlier;  and that each end of month  the partition that holds the data for the month is transferred in archive table ( transactionHistoryArchive)

My understanding is that [ transactionHistory] table will hold only data for the current month so why it needs to be partitioned?

Answer 1

My understanding is that [ transactionHistory] table will hold only data for the current month so why it needs to be partitioned?

The TransactionHistory table in this example is partitioned by month and holds the 12 most recent months.  The objective is to move the oldest month into the TransactionHistoryArchive table.  However, the TransactionHistoryArchive table contains only 2 partitions after the monthly maintenance.  The first partition contains all of the oldest data and the second partition is empty in preparation for the next monthly cycle.

Each month, data from the oldest month is moved from TransactionHistory into TransactionHistoryArchive (with SWITCH), the empty partition is removed from TransactionHistory (with MERGE), and a partition for the new monthly TransactionHistory partition is created (with SPLIT).  So the example begins with September 2003 through August 2004 in the TransactionHistory table and, after the monthly maintenance, the table contains partitions for October 2003 through September 2004.  The TransactionHistoryArchive table begins with all data prior September 2003 (which could be many years) and ends with all data prior to October 2003.

I don't like this example because it merges non-empty partitions of the TransactionHistoryArchive table.  Logging due to partition maintenance DDL is about 4 times that of normal DML.  I think a better example would be to avoid the TransactionHistoryArchive table merge entirely and just keep many historical partitions, one per month.

---

Dan Guzman, SQL Server MVP, http://www.dbdelta.com

Answer 2

thanks Dan

so  moving  data from transactionHistory table did not begin until the table contained all data for months from septempber 2003 until august 2004 ; this makes sense to me now.

Answer 3

My understanding is that [ transactionHistory] table will hold only data for the current month so why it needs to be partitioned?

The TransactionHistory table in this example is partitioned by month and holds the 12 most recent months.  The objective is to move the oldest month into the TransactionHistoryArchive table.  However, the TransactionHistoryArchive table contains only 2 partitions after the monthly maintenance.  The first partition contains all of the oldest data and the second partition is empty in preparation for the next monthly cycle.

Each month, data from the oldest month is moved from TransactionHistory into TransactionHistoryArchive (with SWITCH), the empty partition is removed from TransactionHistory (with MERGE), and a partition for the new monthly TransactionHistory partition is created (with SPLIT).  So the example begins with September 2003 through August 2004 in the TransactionHistory table and, after the monthly maintenance, the table contains partitions for October 2003 through September 2004.  The TransactionHistoryArchive table begins with all data prior September 2003 (which could be many years) and ends with all data prior to October 2003.

I don't like this example because it merges non-empty partitions of the TransactionHistoryArchive table.  Logging due to partition maintenance DDL is about 4 times that of normal DML.  I think a better example would be to avoid the TransactionHistoryArchive table merge entirely and just keep many historical partitions, one per month.

---

Dan Guzman, SQL Server MVP, http://www.dbdelta.com

Made you an Answerer. Thanks!

---

Ed Price, Azure Development Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

Answer an interesting question? Create a wiki article about it!