title: sql server automated daily table partitioning
body: <p>I need to re design a table which holds daily logs and has hundreds of million rows. The concept is to keep data only for a one month period. The table will be partitioned per day, using a datetime field as the partition key. So for example for March i need to have 31 filegroups and 31 partitions. Once we get into April the process will have to insert into April 1st's partition and delete March 1st data and filegroup.</p>
  
  <p>I am comfortable with the partition and filegroup creation, i am looking for suggestions on how to create the automated deletion process. Maybe an sql job which will drop the partition using <code>dateadd(m,-1,getdate())</code>? Is there something else i am missing?</p>
  
  <p>Additional question: I will create year_month_day_partitions and the corresponding filegroups until 31-12-2020. Is there any way to create those automaticalluy or do we have to run the scripts manually when it is time?</p>