基于TenantId分区：触发器动态修改分区函数时遇执行错误

这个问题我之前做租户分区方案时也踩过坑，SQL Server的这个限制确实是个硬门槛——咱们先搞清楚为啥会报错：

Cannot execute ALTER PARTITION FUNCTION on/using table 'Tenant' since the table is the target table or part of cascading action...

原因很直白：你在AFTER INSERT触发器里执行ALTER PARTITION FUNCTION时，当前的INSERT事务还没结束，目标表Tenant正处于锁定状态。SQL Server不允许在事务内同时修改表的数据和它的分区结构，这会引发元数据与数据操作的冲突。

那该怎么解决呢？给你几个可行的方案：

方案1：改用异步批量处理（最常用） #

放弃在触发器里同步修改分区的思路，换成「队列+后台处理」的模式：

1. 创建分区队列表，用来记录需要新增的TenantId：

CREATE TABLE TenantPartitionQueue (
    QueueId INT IDENTITY(1,1) PRIMARY KEY,
    TenantId INT NOT NULL,
    IsProcessed BIT NOT NULL DEFAULT 0,
    CreatedTime DATETIME NOT NULL DEFAULT GETDATE()
);

2. 修改触发器，只把新增的TenantId插入队列，不直接操作分区函数：

ALTER TRIGGER trg_Tenant_AfterInsert
ON Tenant
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    -- 去重插入，避免重复处理已存在的分区值
    INSERT INTO TenantPartitionQueue (TenantId)
    SELECT DISTINCT i.TenantId
    FROM INSERTED i
    WHERE NOT EXISTS (
        SELECT 1 FROM sys.partition_range_values prv
        JOIN sys.partition_functions pf ON prv.function_id = pf.function_id
        WHERE pf.name = 'YourPartitionFunctionName' -- 替换成你的分区函数名
        AND prv.value = i.TenantId
    );
END;

3. 创建SQL Server代理作业，定期扫描队列并处理分区：
   先写一个处理存储过程：

CREATE PROCEDURE ProcessTenantPartitions
AS
BEGIN
    SET NOCOUNT ON;
    -- 锁定待处理项，避免并发重复操作
    DECLARE @TenantIds TABLE (TenantId INT);
    INSERT INTO @TenantIds
    SELECT TenantId
    FROM TenantPartitionQueue WITH (UPDLOCK, READPAST)
    WHERE IsProcessed = 0;

    -- 批量处理每个需要新增的分区值
    DECLARE @CurrentTenantId INT;
    DECLARE TenantCursor CURSOR FOR SELECT TenantId FROM @TenantIds;
    OPEN TenantCursor;
    FETCH NEXT FROM TenantCursor INTO @CurrentTenantId;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        BEGIN TRY
            ALTER PARTITION FUNCTION YourPartitionFunctionName()
            SPLIT RANGE (@CurrentTenantId);
            
            -- 标记为已处理
            UPDATE TenantPartitionQueue
            SET IsProcessed = 1
            WHERE TenantId = @CurrentTenantId;
        END TRY
        BEGIN CATCH
            -- 可新增错误日志表记录异常
            INSERT INTO PartitionErrorLog (TenantId, ErrorMessage, ErrorTime)
            VALUES (@CurrentTenantId, ERROR_MESSAGE(), GETDATE());
        END CATCH
        FETCH NEXT FROM TenantCursor INTO @CurrentTenantId;
    END;
    CLOSE TenantCursor;
    DEALLOCATE TenantCursor;
END;

然后把这个存储过程设为代理作业的执行步骤，比如每5分钟执行一次，根据你的业务频率调整即可。

方案2：预创建分区范围（更高效） #

如果你的TenantId是自增整数，可以提前预创建一批分区范围，比如当前最大TenantId是100，就直接把分区范围扩展到200。这样新增租户时只要TenantId在101-200之间，就不用修改分区函数，等快用完时再批量扩展。

这种方式能大幅减少ALTER PARTITION FUNCTION的执行次数——毕竟这个操作是日志密集型的，频繁执行会拖慢数据库性能。

方案3：重新评估分区策略（避免单值分区） #

如果你的租户数量很多，每个TenantId一个分区其实不是最优方案：SQL Server的分区数量过多（比如超过1000个）会导致元数据操作变慢，查询优化器的开销也会增加。

可以考虑按TenantId的范围分区（比如1-1000、1001-2000），或者结合租户的其他属性（比如注册日期）来分区，这样分区数量会少很多，维护成本也更低。

内容的提问来源于stack exchange，提问作者PicoDeGallo