I have a table with approximately 1.4 bn rows; currently just one partition - but I would like to split the data into multiple partitions. I haven't been using partitions before, so I am not quite sure how best practice is.
The table contains monthly financial information for our customers and will increase with approximately 35 million new rows each month. Each row will have a YearMonth_Id and a Customer_Id as well as a lot of columns containing financials.
I am thinking to do the partition by YearMonth_Id (maybe grouped by quarters?).
I'm no dba, just a finance analyst - but since we don't have qualified dba to maintain this table regularly, it comes down to me to do it; hence I want to have as little maintenece on the table as possible.
My question therefore is as follows. When working with partitions and incremental values (YearMonth_Id), how should I structure the partitions on the table? I read that usually you would leave an empty partition to the far right (if working with left range) that you can split each month (or quarter). But I am thinking of creating, say 12 empty partitions for future values, so the next time I need to do a split will be in a years time; so that the table is not dependent on me every month.
Is this a big no-no or would it be an okay way to go?
I know it is a long post to arrive at a simple question - but I hope it gave a better understanding of what I am after.
I am running SQL Server 2014.