SQL Partioning on date, how does it work for future records?

I am setting up a new system and I would like to partition the data for speed. Basically what I would like to do is have one database with all the latest information (e.g. last 3 months). then have some partitioned tables for data older than that. Have not chosen mysql or sql server express as not sure which one has the desired capabilities.

I have read some examples on how partitioning on ranges works as seen below:

CREATE TABLE SAMPLE_ORDERS 
(
    ORDER_NUMBER NUMBER,
    ORDER_DATE DATE,
    CUST_NUM NUMBER,
    TOTAL_PRICE NUMBER,
    TOTAL_TAX NUMBER,
    TOTAL_SHIPPING NUMBER
)
PARTITION BY RANGE(ORDER_DATE)
(
    PARTITION SO99Q1 VALUES LESS THAN TO_DATE(‘01-APR-2010’, ‘DD-MON-YYYY’),
    PARTITION SO99Q2 VALUES LESS THAN TO_DATE(‘01-JUL-2010’, ‘DD-MON-YYYY’),
    PARTITION SO99Q3 VALUES LESS THAN TO_DATE(‘01-OCT-2010’, ‘DD-MON-YYYY’),
    PARTITION SO99Q4 VALUES LESS THAN TO_DATE(‘01-JAN-2011’, ‘DD-MON-YYYY’),
    PARTITION SO00Q1 VALUES LESS THAN TO_DATE(‘01-APR-2011’, ‘DD-MON-YYYY’),
    PARTITION SO00Q2 VALUES LESS THAN TO_DATE(‘01-JUL-2011’, ‘DD-MON-YYYY’),
    PARTITION SO00Q3 VALUES LESS THAN TO_DATE(‘01-OCT-2011’, ‘DD-MON-YYYY’),
    PARTITION SO00Q4 VALUES LESS THAN TO_DATE(‘01-JAN-2012’, ‘DD-MON-YYYY’)
);

Read more at http://www.devarticles.com/c/a/Oracle/Partitioning-in-Oracle/1/#YCTAmfR0hkvo1at0.99

But this seems to be more for partitioning older data. What happens when it reaches newer data? Will it put the new data into a single partition?

I would rather have a small amount of data in the new table and dump all the old information.

Do I just need to create multiple partitions up to 2020, and keep manually adding them as the project runs?

Is there another way to do this?

I know I could achieve what I need by replicating all the data into another table, and cleaning up the old data every night, however I have had issues with this before and was wondering if partitioning would do a better job?

In SQL Server you have to split the partition at the head of the table:

ALTER PARTITION SCHEME partition_scheme_name NEXT USED [ filegroup_name ];
ALTER PARTITION FUNCTION partition_function_name() SPLIT RANGE ( '20120401');

This will add a new partition for new incoming data. You should always an empty partition at the head of the table to make the split a instant metadata-only operation. If you populated the last partition with rows then splitting has to move data.

StackExchange Database Administrators Q#23339, answer score: 6