question

Kirman1 avatar image
Kirman1 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?
partitioningpartitiontable partition
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Please let us know your table structure..
0 Likes 0 ·

1 Answer

·
NeerajTripathi avatar image
NeerajTripathi answered
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.
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·
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.
0 Likes 0 ·
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.
0 Likes 0 ·
Sorry for the late reply. The index is already created on the Date field. But then too I want to create a partition can you please suggest the range as I want to check out the performance.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.