Partition or index

Question

Hi Expert,

i have 22 years data with with different dates in a year with multiple products , category,location etc

if i want fetch the data in 1 seconds for 2018 year with any 4 months. How i can add partition on date in a such way that i will get data in a second or any other approach i should do it in MS sql serer

Answer 1

Most of all you will need an index with the date as the leading column. All depending on your other queries against the table, this could be the clustered index on the table.

Partitioning is not primarily a performance feature, but mainly a management feature for very large tables (say at least 1TB in size). Applied wrongly, partitioning can degrade your performance.

Answer 2

Hi @Shambhu Rai

Using partitioning is only going to help your query performance if the partitioning scheme is built to serve your specific queries.

You have to review your query patterns and see how they are accessing the table in order to identify the best approach. The reason for this is you can only partition on a single column (the partitioning key) and this is what will be used for partition elimination.

One would usually partition a very large table for the purpose of:

• Distributing data between different types of disk so that more “active” data can be placed on faster, more expensive storage while less active data is placed on cheaper, slower storage. This is mostly a cost savings measure.
• Assisting in index maintenance for extremely large tables. Since you can rebuild partitions individually, this can assist in keeping indexes properly maintained with minimal impact.
• Leveraging partitioning for improved archival process. See sliding windows.

Refer to this similar thread for more details: Is table partitioning improving performance? Is it worth it?

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.