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.