I'm developing a SQL Server table that will store audit trail data. I know that this table will get large as the system is used over time. Is it worth partitioning the table by year when the system is first configured? Or worth waiting a year or so in order to keep the performance as high as possible?

  • At start, otherwise you will need to rebuild the index and need the extra logspace to create the partitioning – Stijn Wynants Apr 18 '17 at 10:55
  • I don't recall partitioning having a significant performance impact. – Michael Green Apr 18 '17 at 12:16

This is obviously opinion based, but I'd go ahead and partition the table at creation time. I'd recommend RANGE RIGHT, as you apparently want to partition on a DATE and make sure you pre-define enough partitions before the minimum date you currently have and after the maximum date you currently have. This will mitigate massive data movement when adding additional partitions in the future.

Check out Dan Guzman's Table Partitioning Best Practices


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.