locked
Automatic Partitioning By Month on SQL Server 2016 RRS feed

  • Question

  • Hi,

    I have a table that has 10 million records and I wanted to partition the table by months. Table has date/time column and values are between 2004 and 2017. If its possible I want to automatize the partitioning for every coming month and years like 2018, 2019..

    I googled a lot but All documents that i saw are creating filegroups etc. There are 60+ months and of course i shouldn't create 60+ filegroups.

    Could you please explain me how can i do it or if it is possible or not ?
    Thursday, April 13, 2017 12:42 PM

Answers

  • It should be okay if you create a filegroup per month. If you do not want to do that you can use a filegroup for all months in a year.

    A Fan of SSIS, SSRS and SSAS

    • Marked as answer by TemurKen Friday, April 14, 2017 7:09 AM
    Thursday, April 13, 2017 1:46 PM
  • >There are 60+ months and of course i shouldn't create 60+ filegroups.

    Just use a single filegroup.  You don't need a separate filegroup for each partition.

    And simply pre-create the partitions for the next few years.

    David


    Microsoft Technology Center - Dallas
    My blog

    • Marked as answer by TemurKen Friday, April 14, 2017 7:09 AM
    Thursday, April 13, 2017 2:38 PM

All replies

  • with autmatic sliding windows mechanism we can do this - please check this article for more

    https://technet.microsoft.com/en-us/library/aa964122(v=sql.90).aspx


    http://uk.linkedin.com/in/ramjaddu

    Thursday, April 13, 2017 1:41 PM
  • It should be okay if you create a filegroup per month. If you do not want to do that you can use a filegroup for all months in a year.

    A Fan of SSIS, SSRS and SSAS

    • Marked as answer by TemurKen Friday, April 14, 2017 7:09 AM
    Thursday, April 13, 2017 1:46 PM
  • >There are 60+ months and of course i shouldn't create 60+ filegroups.

    Just use a single filegroup.  You don't need a separate filegroup for each partition.

    And simply pre-create the partitions for the next few years.

    David


    Microsoft Technology Center - Dallas
    My blog

    • Marked as answer by TemurKen Friday, April 14, 2017 7:09 AM
    Thursday, April 13, 2017 2:38 PM
  • What is the purpose for partitioning? I.e., what benefits do you expect to see? For instance, if you don't want to backup the same "old" data over and over again, then you can do filegroup level backups of the old partitions, which of course requires several partitions (typically one per month). Of you want to achieve sliding window to facilitate adding and removing data instantaneously, then several filegroups isn't required. If you expect better performance in general, then I doubt you will see much benefits.

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Wednesday, April 19, 2017 12:59 PM