• The purpose of partitioning is manageability. Some examples:

    You want to archive old data, then you switch out the partition containing the old data with a metadata only operation, without executing huge delete queries.

    You want to append new data, then you bulk insert to a new partition and you switch it in with a metadata only operation (same as above).

    You can have performance benefits from partitioning when all the queries that access a partitioned table use the partitioning key as a filter predicate: the optimizer will eliminate the partitions filtered out by the predicate and work on a single partition rather than the whole set of partitions.

    Partitioning without ensuring that partition elimination can happen is often a no-go. Moreover, to ensure index alignment, you have to add the partitioning key to the indexes, often making them bigger, hence slower to scan/seek/update.

    Long story short: if your application was not designed from the start for partitioning, it will likely perform worse on a partioned table.

    -- Gianluca Sartori