Updating partition key column
By creating a partition key that is flexible (via an algorithm) we can adjust performance for particular clients, without redefining partition ranges. it is recommended to run runstats regulary in order to keep the statistics updated, but you should consider the beneift when doing so.
if you only move 5% of the rows from one partition to another partition, then maybe you should not run runstats, especially if your partitions are very big, and runstats will consume alote of resources.
this is a better way to do the update if you are about to update many rows, because when you update many rows using a sql statement you will cause massive logging, you will probably hit the maxium lock allowed and lock the entire partition, and it's a bit complex to implement commit logic into this massive update.
A list partition where the partition key is a specific value is a good example of this.
So, I think you would be advised to rethink your strategy, your heading into a deep abyss from which there is no return. Does not #2 above imply that a rebind of plans/packages would also be required, to take into consideration the new stats? We do not anticipate frequent updates of the partition key.
Ok, please confirm my point-by-point understanding below... Updating the partition key will cause the rows to redistribute (i.e., move) immediately. A RUNSTATS would be required because the data is now located in different partitions. However, it is possible that after some time we may discover that a client is now sending us more data than we anticipated, and that we would want to relocate his data into a an empty partition (assuming he originally shared a partition with other small clients), or spread his data over two or more parititions.
There are many types of scenarios where partitioning scheme does exactly follows the primary key's first column - for instance in a data warehouse scenario where the snapshot date of a fact table is usually the partition column as well as the first column in the primary key.
But equally, in OLTP environments where the PK is an IDENTITY or other surrogate key, it makes little sense to use this for the partition, since partitioning on arbitrary numbers is not normally terribly useful.
Or two offices in the same city (from different groups or business units within the same company) merge into one location?