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?
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