Partitioned Tables on SQL 2005
-
Hi Chaps,
I want to know if anyone has managed to implement partitioned tables on SQL 2005. I would like to ask some few questions about this:
They are as follows:
With partitioned tables, under what circumstances will you benefit from a performance gain ?
If you have multiple filegroups, can you delete the data in that filegroup and eventually drop the filegroup as well?
How easy is it to automate the maintenance of a partitioned table?
When creating partitioned tables, can they be based on a monthly basis, so a large table exists with millions of rows say 500m, will I be able to have a vertical partition based on the date, i.e have January marketing records, February and March as well.
Also, as I understand the indexes can also be partitioned, on my application data for the current month is the most used data, and once the data has been written onto the system, it doesn’t get updated again, can I partition my indexes so that for example I can drop the indexes of data that is over 2 months old, but for example have indexes on data for march and February?
I look forward to your replies on this, and should anyone have any questions, please ask them and I will reply ASAP.
-
Check this out. may it will answet your questions and give you few more ideas to implement.
-
1) Indexes are for the table, not for partitions of the table. So you can't keep the index for the last partition and drop the same index for the older partition.
2) Partitioned table is mainly for managing big tables e.g. backup a partition by backing up the filegroup that partition resides in. For performance, you may or may not benifit from partitions, it depends on how your app queries data.
3) You can use date as the partition column and create your partition function based on that. For the last partition, you can keep one month as a partition. For older partition, you need to merge them into bigger time range, say 6 months. Otherwise you will have too many partitions. Query performance will be degraded when the number of partition is increased
4) File gorup management is not changed.
-
1) Indexes are for the table, not for partitions of the table. So you can't keep the index for the last partition and drop the same index for the older partition.
2) Partitioned table is mainly for managing big tables e.g. backup a partition by backing up the filegroup that partition resides in. For performance, you may or may not benifit from partitions, it depends on how your app queries data.
3) You can use date as the partition column and create your partition function based on that. For the last partition, you can keep one month as a partition. For older partition, you need to merge them into bigger time range, say 6 months. Otherwise you will have too many partitions. Query performance will be degraded when the number of partition is increased
4) File gorup management is not changed.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply