Answered by:
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?
Monday, September 28, 2015 7:58 PM
Answers
-
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
- Edited by Dan GuzmanMVP, Editor Tuesday, September 29, 2015 12:53 AM
- Proposed as answer by Ed Price - MSFTMicrosoft employee Saturday, July 9, 2016 2:10 AM
- Marked as answer by Ed Price - MSFTMicrosoft employee Sunday, October 30, 2016 1:49 AM
Tuesday, September 29, 2015 12:52 AMAnswerer
All replies
-
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
- Edited by Dan GuzmanMVP, Editor Tuesday, September 29, 2015 12:53 AM
- Proposed as answer by Ed Price - MSFTMicrosoft employee Saturday, July 9, 2016 2:10 AM
- Marked as answer by Ed Price - MSFTMicrosoft employee Sunday, October 30, 2016 1:49 AM
Tuesday, September 29, 2015 12:52 AMAnswerer -
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.
Tuesday, September 29, 2015 1:11 AM -
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
Ed Price, Azure Development Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)
Answer an interesting question? Create a wiki article about it!Saturday, July 9, 2016 2:11 AM