Never forget that what dictates the choice of a nonstandard storage option such as partitioning is the global improvement of business operations. It may mean improving a business process that is perceived as being of paramount importance to the detriment of some other processes. For instance, it makes sense to optimize transactional processing that takes place during business hours at the expense of a nightly batch job that has ample time to complete. The opposite may also be true, and we may decide that we can afford to have very slightly less responsive transactions if it allows us to minimize a critical upload time during which data is unavailable to users. It's a matter of balance.
In general, you should avoid unduly favoring one process over
another that needs to be run under similar conditions. In this regard,
any type of storage that positions data at different locations based on
the data value (for example both clustering indexes as well as
partitioning) are very costly when that value is updated. What would
have previously been an in situ update in a regular
table, requiring hardly more than perhaps changing and shifting a few
bytes in the table at an invariant physical address, becomes a
delete on one part of the disk, followed by an
insert somewhere else, with all the
maintenance operations usually associated with indexes for this type of
Having to move data when we update partition keys seems, on the surface, to be a situation ...