Error Updating Record on Table with Partition on a Read-Only Filegroup
Ordinarily, SQL Server only checks for a read-only filegroup just before attempting to write to the partition. No error is thrown when reading from a read-only filegroup, as you would expect.
SQL Server can therefore touch any rows it likes, so long as only rows located on read-write filegroups qualify for the update. To put it another way: SQL Server can fully scan all partitions (e.g. using a Clustered Index Scan) looking for rows to update, so long as the only rows that arrive at the Clustered Index Update are on read-write partitions.
In this scenario, there are two separate storage engine rowsets involved: one for the read, and one for the update. The reading rowset will not throw an error if it encounters a read-only partition because it is only reading. The writing rowset will throw an error, because it is configured to perform changes.
The catch
In execution plan shapes where there is no blocking operator between reads and writes to the base table, SQL Server may apply an optimization (&quot;Rowset Sharing&quot;), whereby the two operators share a single storage engine rowset.
In this situation, reading from a read-only partition will throw the error reported in the question. The single rowset is configured for both read and write - touching a read-only partition results in the error.
Adding a key from the unique clustered index to the set clause means a split, sort, collapse combination is required to avoid transient key violations. As a side-effect, the sort (a blocking operator) prevents the rowset sharing optimization from being applied.
For more details, see my article Changes to a Writable Partition May Fail Unexpectedly