asked
Partition SQL Table
I want to partition a huge SQL Table. This table contains Daily and Month end data. Example : **Month End Data** 1. 2014-01-31 2. 2014-02-28 3. 2014-03-31 **Daily Data** 1. 2014-01-01 2. 2014-01-02 3. ….. 4. 2014-01-30 5. 2014-02-01 6. 2014-02-02 7. … 8. 2014-02-27 9. 2014-03-01 10. 2014-03-02 11. … 12. 2014-03-30 Single table contains all the data of the above dates. Based on the above table we create Daily and Monthly Reports. Daily Report will contain the query which will take ONLY the daily dates and Monthly Report will contain the query which will take ONLY the monthly dates. Now I want to partition the table based on Monthly and Daily Partition, so as when the Monthly Report gets executed the data of Monthly should only be touched and same with daily. Can you please suggest as how should I go with the Date Range while creating Partition Functions?
Hi Kirman, Can you please confirm the requirement of partitioning the table? If it is because of performance improvements by segregating the table into multiple filegroups then its better to retain the required data in the main table and archive the rest in some other table. Then, you can partition your main table on month, year as you need to keep in mind that the partition number should not exceed the provided limit and use the switch in and switch out functionality to other table and make sure the table/partition should be in same filegroup so that it wont take much time to switch out the partition.
Hi Neeraj, Thanks for the reply. We have to retain maximum 15 days daily data and all the month end data in the same table. So when Daily Report Executes it will get the data from the same table where monthly is also stored. I dont want to seperate out the table but want to use partitioning. We are already archiving the data which is beyond 15 days. Kindly help in setting up the range.
Hi Neeraj, Thanks for the reply. The table will contain 15 Days Daily Data & all the Month Ends Data. If Daily Reports gets executed it will hit the same table where we have both monthly and daily data. So can I segregate Monthly and Daily Data using partitions. So the report execution time can reduce as will get the data from the expected partition.
Hi Kirman, Then there is no need to partition the table as you can achieve the same with your query by applying the filter on Day/Month basis. And, you can create the required index so that the query optimizer will land on the data fastly.
16 People are following this question.
