MSBI (SSIS/SSRS/SSAS) Online Training

Tuesday, March 15, 2011

Table Partition and Archive the OLD data from a Table - Sql Server

Hi Friendz,
Here i would like to introduce a concept called 'Partition On Table' in Sql server 2005/2008.

I am using SQL Server 2008 and I have table called, ‘Monthly Product’ (it contains data from 2010-January to till) every month data load approximately 30 million records, I only need to read last 12 months data for my report. Because of huge data my table response is too bad.

Then my option is -
Partition the table based on monthly wise,
Clustered Indexes then
Archive the OLD data (<12 months) into a Text file.
You can see better performance on your query using partition table.


Here i will run the package on monthly basis.
 The first task removes the Partitions on the table and re-create for last 12 months.
 The second task will Archive the Old data (< 12 months)
 The third task deletes the old data from the Report table.

Read More about Partitions:
http://msdn.microsoft.com/en-us/library/ms345146(v=sql.90).aspx
You can Download The sample package from the below link.
http://www.ziddu.com/download/14205160/IntegrationServicesProject2.zip.html

Thanks, Rav's

1 comment:

akhila priya said...

I really enjoy the blog.Much thanks again. Really Great MSBI online training Bangalore