使用标量值函数添加列值时批量插入生成重复值问题咨询

问题根因 #

你当前的实现存在事务一致性问题：同一次INSERT操作的所有自定义函数调用，都会在数据实际插入目标表前统一执行。此时dbo.tblabc中还没有本次待插入的记录，同一个代理人ID对应的历史计数固定不变，所以多条数据生成的编号完全重复。

优化方案 #

方案1：移除标量函数，用窗口函数生成编号（最推荐） #

直接在INSERT语句中完成编号计算，避免逐行调用函数的性能和一致性问题：

INSERT INTO dbo.tblabc (InvoiceNumber, AgentId)
SELECT 
    CONCAT(
        'ALD/SHJ/', YEAR(GETDATE()), '/INV/',
        -- 补零规则可根据每年最大序号量调整，当前示例支持最大9999个编号/代理人/年
        RIGHT('0000' + CAST((ISNULL(t.exist_cnt, 0) + ROW_NUMBER() OVER(PARTITION BY x.AgentId ORDER BY x.AgentId)) AS VARCHAR(4)), 4)
    ),
    x.AgentId
FROM dbo.tblxyz x
-- 预查询每个代理人当年已有的发票数量
LEFT JOIN (
    SELECT AgentId, COUNT(1) AS exist_cnt
    FROM dbo.tblabc
    WHERE YEAR(CreatedDate) = YEAR(GETDATE())
    GROUP BY AgentId
) t ON x.AgentId = t.AgentId

优势：

• 无重复风险：同批次同代理人的序号通过ROW_NUMBER()生成唯一值，不会出现重复
• 性能优异：仅执行一次聚合查询，数据量越大相比逐行调用函数的优势越明显
• 逻辑易维护：不需要单独维护自定义函数，编号规则直接在INSERT语句中可见可调

方案2：用自增列+持久化计算列自动生成编号 #

如果希望彻底避免手动维护编号逻辑，可以调整表结构由数据库自动生成唯一编号：

1. 调整tblabc表结构新增自增序列和计算列

ALTER TABLE dbo.tblabc
ADD 
    -- 全局自增序号
    InvoiceAutoId INT IDENTITY(1,1) NOT NULL,
    -- 自动生成固定格式的发票编号，持久化存储不需要手动维护
    InvoiceNumber AS CONCAT(
        'ALD/SHJ/', YEAR(CreatedDate), '/INV/', 
        RIGHT('0000' + CAST(InvoiceAutoId AS VARCHAR(4)), 4)
    ) PERSISTED

2. 插入语句不需要再处理InvoiceNumber字段，数据库会自动生成：

INSERT INTO dbo.tblabc (AgentId)
SELECT AgentId FROM dbo.tblxyz

优势：完全由数据库引擎保证编号唯一，无论单次插入多少数据都不会出现重复。

方案3：必须保留函数时改用序列对象 #

如果业务逻辑要求必须保留自定义函数的调用方式，可以创建全局序列代替查表计数，避免同批次查询的计数一致性问题：

-- 创建全局序列，起始值可根据当前最大编号调整
CREATE SEQUENCE dbo.InvoiceSeq
AS INT
START WITH 1
INCREMENT BY 1
CYCLE

-- 调整函数逻辑
ALTER function [dbo].[udf_GetInvoiceNumber](@AgentId int)
returns varchar(50)
begin
    DECLARE @SeqVal INT = NEXT VALUE FOR dbo.InvoiceSeq
    RETURN CONCAT('ALD/SHJ/', YEAR(GETDATE()), '/INV/', RIGHT('0000' + CAST(@SeqVal AS VARCHAR(4)), 4))
end

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