causes SQL Server to think the 8060 is reached while it is not
If you are seeing this error message then you are wrong about that. Every time you drop and re-create the computed column the old column is marked as dropped in the metadata but the previous column values still exist in the data page.
When you recreate the column it is treated as a new column at the end of all existing columns. It can reclaim space from dropped columns at the end of the section but not if they are followed by another undropped column. So depending on the approach you take by the time you have done a few iterations of this the data page will be full of rubbish.
Approach 1 (offsets increase)
DROP TABLE IF EXISTS dbo.CostCenter
go
CREATE TABLE dbo.CostCenter
(
CcrChildBudgetAY DECIMAL(10,2),
CcrBudgetAY DECIMAL(10,2),
CcrTotBudgetAY AS (CONVERT(DECIMAL(21,5), CcrChildBudgetAY + CcrBudgetAY)) PERSISTED NOT NULL,
CcrTotBudgetAY2 AS (CONVERT(DECIMAL(21,5), CcrChildBudgetAY + CcrBudgetAY)) PERSISTED NOT NULL
);
INSERT INTO dbo.CostCenter VALUES (1,1);
ALTER TABLE dbo.CostCenter DROP COLUMN CcrTotBudgetAY;
ALTER TABLE dbo.CostCenter ADD CcrTotBudgetAY AS (CONVERT(DECIMAL(21,5), CcrChildBudgetAY + CcrBudgetAY)) PERSISTED NOT NULL;
ALTER TABLE dbo.CostCenter DROP COLUMN CcrTotBudgetAY2;
ALTER TABLE dbo.CostCenter ADD CcrTotBudgetAY2 AS (CONVERT(DECIMAL(21,5), CcrChildBudgetAY + CcrBudgetAY)) PERSISTED NOT NULL;
SELECT c.name, is_dropped, leaf_offset
FROM sys.system_internals_partition_columns pc
JOIN sys.partitions p on p.partition_id = pc.partition_id
LEFT JOIN sys.columns c on c.object_id = p.object_id and c.column_id = pc.partition_column_id
WHERE p.object_id = OBJECT_ID('dbo.CostCenter')
+------------------+------------+-------------+
| name | is_dropped | leaf_offset |
+------------------+------------+-------------+
| CcrChildBudgetAY | 0 | 4 |
| CcrBudgetAY | 0 | 13 |
| NULL | 1 | 22 |
| NULL | 1 | 35 |
| CcrTotBudgetAY | 0 | 48 |
| CcrTotBudgetAY2 | 0 | 61 |
+------------------+------------+-------------+
Approach 2 (offsets reused)
DROP TABLE IF EXISTS dbo.CostCenter
go
CREATE TABLE dbo.CostCenter
(
CcrChildBudgetAY DECIMAL(10,2),
CcrBudgetAY DECIMAL(10,2),
CcrTotBudgetAY AS (CONVERT(DECIMAL(21,5), CcrChildBudgetAY + CcrBudgetAY)) PERSISTED NOT NULL,
CcrTotBudgetAY2 AS (CONVERT(DECIMAL(21,5), CcrChildBudgetAY + CcrBudgetAY)) PERSISTED NOT NULL
);
INSERT INTO dbo.CostCenter VALUES (1,1);
ALTER TABLE dbo.CostCenter DROP COLUMN CcrTotBudgetAY, CcrTotBudgetAY2;
ALTER TABLE dbo.CostCenter ADD CcrTotBudgetAY AS (CONVERT(DECIMAL(21,5), CcrChildBudgetAY + CcrBudgetAY)) PERSISTED NOT NULL,
CcrTotBudgetAY2 AS (CONVERT(DECIMAL(21,5), CcrChildBudgetAY + CcrBudgetAY)) PERSISTED NOT NULL
SELECT c.name, is_dropped, leaf_offset
FROM sys.system_internals_partition_columns pc
JOIN sys.partitions p on p.partition_id = pc.partition_id
LEFT JOIN sys.columns c on c.object_id = p.object_id and c.column_id = pc.partition_column_id
WHERE p.object_id = OBJECT_ID('dbo.CostCenter')
+------------------+------------+-------------+
| name | is_dropped | leaf_offset |
+------------------+------------+-------------+
| CcrChildBudgetAY | 0 | 4 |
| CcrBudgetAY | 0 | 13 |
| NULL | 1 | 22 |
| NULL | 1 | 35 |
| CcrTotBudgetAY | 0 | 22 |
| CcrTotBudgetAY2 | 0 | 35 |
+------------------+------------+-------------+
The best alternative will be to come up with a solution that doesn't involve dropping and re-creating these columns daily as even in the best case this causes logged activity for all rows in the table that need to be rewritten as you add back the persisted computed columns.
