SQL MI Partition- Unable to update the partition Key in the writable Primary file group . It throws error for the read only filegroup

Question

Hi

I have a requirement to change the archived records within a table as read only. active and inactive data is residing in the same table as of now. An indicator column IsArchived and status column will determine the record is active or archived. it is an OLTP system. Hence the status update and flag updates happens regularly on txn and that particular row become archived status.

As a solution I have tried partition concept and make the archived data file group as read only mode to avoid any DML operation. However i am facing some challenges especially while updating the partition key in the primary file group. it is throwing the error indicating the index is on read only file group

below steps are followed

1. New file group and file named archived_date is created
2. Partition function and schema created for archive indicator column
3. made the archived_data file group as read only
4. update the status column and isarchive indicator column to change the record as archived- It is throwing error. --

Note: if the update is done on any non partition column it works fine provided in the 'where' clause explicitly mention the key columns

CREATE PARTITION FUNCTION myarchivePF (int)
AS RANGE LEFT FOR VALUES (0,1)
GO

CREATE PARTITION SCHEME myPartitionScheme
AS PARTITION myarchivePF TO ([PRIMARY],archived_data,[PRIMARY])
GO

CREATE TABLE dbo.TestPartition ON myPartitionScheme([IsArchived])

alter database [testing_partition_tiket] modify filegroup archived_data readonly

update dbo.[TestPartition]
set isarchived=1,status='Archived'
where id='22187F6D-7E13-4149-8B76-08D9FDFDBEC8' and isarchived=0
it is failing.

below updates works fine as partition column is not used in SET command

How do i resolve this issue? Is it not possible to update the partition key? How does the data movement from Primary/writable group to read only group happens?

Appreciate your help

Regards
Jasmin

Answer 1

All the rows with IsArchived=1 are on the same filegroup, so when you update a row to set IsArchived=1, that row moved to that filegroup. But that filegroup is read-only so you can't move the row to that filegroup.