SQL Server中如何为IDENTITY类型Unique_ID添加自定义前缀？

用户提供的建表语句如下：

CREATE TABLE [dbo].[my_table]
(
    [V01_V] [real] NULL,
    [V02_V] [real] NULL,
    [V03_V] [real] NULL,
    [V04_V] [real] NULL,
    [V05_V] [real] NULL,
    [Date_Inserted] [datetime] NOT NULL,
    [Unique_ID] [int] IDENTITY(1,1) NOT NULL,

    CONSTRAINT [PK_row_id] 
        PRIMARY KEY CLUSTERED ([Unique_ID] ASC)
                    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                          IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                          ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

当前Unique_ID是IDENTITY(1,1)类型的整数列，仅作为行号。为了识别数据来源表，需要将其格式改为04cust_45（前缀04cust_+原整数ID），询问如何实现。

解决方案 #

方案1：添加计算列（推荐，不破坏原有逻辑） #

IDENTITY列作为自增主键，直接修改其类型或值会破坏原有自增机制，推荐通过计算列实现需求，既保留原ID的功能，又能生成带前缀的标识：

ALTER TABLE [dbo].[my_table]
ADD Formatted_Unique_ID AS '04cust_' + CAST(Unique_ID AS VARCHAR(10)) PERSISTED;

• PERSISTED关键字会将计算结果存储在表中，提升查询性能，适合频繁使用该字段的场景
• 计算列会自动根据Unique_ID的值更新，无需手动维护

方案2：修改原有列（不推荐，需谨慎操作） #

如果必须替换原有Unique_ID列，需先移除IDENTITY属性和主键约束，再修改列类型并更新值：

1. 移除主键约束

ALTER TABLE [dbo].[my_table]
DROP CONSTRAINT [PK_row_id];

2. 过渡移除IDENTITY属性

-- 添加临时列存储原ID值
ALTER TABLE [dbo].[my_table]
ADD Temp_Unique_ID INT;

UPDATE [dbo].[my_table]
SET Temp_Unique_ID = Unique_ID;

-- 删除原有IDENTITY列
ALTER TABLE [dbo].[my_table]
DROP COLUMN Unique_ID;

-- 重命名临时列为Unique_ID并修改类型
EXEC sp_rename '[dbo].[my_table].Temp_Unique_ID', 'Unique_ID', 'COLUMN';

ALTER TABLE [dbo].[my_table]
ALTER COLUMN Unique_ID VARCHAR(20) NOT NULL;

3. 更新所有行的带前缀值

UPDATE [dbo].[my_table]
SET Unique_ID = '04cust_' + CAST(Unique_ID AS VARCHAR(10));

4. 重新添加主键约束（如果需要）

ALTER TABLE [dbo].[my_table]
ADD CONSTRAINT [PK_row_id] PRIMARY KEY CLUSTERED (Unique_ID ASC);

⚠️ 注意：此方案会完全破坏原有IDENTITY自增特性，后续插入数据需手动维护ID值，极易引发主键冲突，仅在特殊场景下使用。

方案3：触发器维护新列 #

若需要新插入数据自动生成带前缀ID，同时保留原IDENTITY列，可添加普通字符串列并通过触发器赋值：

1. 添加新列

ALTER TABLE [dbo].[my_table]
ADD Formatted_Unique_ID VARCHAR(20) NOT NULL;

2. 创建插入触发器

CREATE TRIGGER trg_Generate_Formatted_ID
ON [dbo].[my_table]
AFTER INSERT
AS
BEGIN
    UPDATE [dbo].[my_table]
    SET Formatted_Unique_ID = '04cust_' + CAST(i.Unique_ID AS VARCHAR(10))
    FROM [dbo].[my_table] t
    JOIN inserted i ON t.Unique_ID = i.Unique_ID;
END;

3. 更新现有数据

UPDATE [dbo].[my_table]
SET Formatted_Unique_ID = '04cust_' + CAST(Unique_ID AS VARCHAR(10));

这种方式兼顾了原ID的自增功能和带前缀标识的需求，适合需要同时保留两种ID形式的场景。

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