SQL Server 自定義自增欄位格式：函數 + 預設約束實作

需求

資料表 SYS_Company 欄位 CompanyId 需自動生成格式：CY00000001（前綴 CY + 8 位流水號）。

實作方案

1. 建立產生函數

CREATE FUNCTION [dbo].[f_PrimaryCode_SYS_Company]()
RETURNS CHAR(10)
AS
BEGIN
    RETURN (
        SELECT 'CY' + RIGHT(100000001 + ISNULL(RIGHT(MAX(CompanyId), 8), 0), 8)
        FROM SYS_Company WITH (XLOCK, PAGLOCK)
    );
END

2. 加入預設約束

ALTER TABLE [dbo].[SYS_Company]
ADD CONSTRAINT DF_SYS_Company_CompanyId 
DEFAULT ([dbo].[f_PrimaryCode_SYS_Company]()) FOR [CompanyId];

3. 移除約束（如需修改）

ALTER TABLE SYS_Company DROP CONSTRAINT DF_SYS_Company_CompanyId;

效果展示

關鍵技術點

• WITH (XLOCK, PAGLOCK)：並發環境下防止重複鍵值
• RIGHT(MAX(CompanyId), 8)：提取現有最大流水號
• 100000001 + ...：確保補零至 8 位
• RIGHT(..., 8)：取右 8 位

擴展：通用自增函數模板

CREATE FUNCTION dbo.GenPrefixSeq(@Prefix VARCHAR(10), @Table VARCHAR(100), @Col VARCHAR(100), @Len INT)
RETURNS VARCHAR(50)
AS BEGIN
    DECLARE @MaxSeq VARCHAR(50);
    SELECT @MaxSeq = MAX(CASE WHEN @Col LIKE @Prefix + '%' THEN SUBSTRING(@Col, LEN(@Prefix)+1, @Len) END) FROM @Table;
    RETURN @Prefix + RIGHT(POWER(10, @Len) + ISNULL(CAST(@MaxSeq AS INT), 0) + 1, @Len);
END

注意事項

• 高併發建議改用 SEQUENCE (SQL 2012+) + 觸發器/預設值
• 鎖提示 XLOCK, PAGLOCK 可能影響並發效能，評估業務量級
• 前綴固定長度、流水號固定位數，便於排序與解析

總結

透過標量函數 + 預設約束實現自定義格式自增，適合中低併發、格式固定的業務場景。高併發推薦 SEQUENCE 方案。