使用ROW_NUMBER()生成维度表主键是否合适？行序变更存隐患

我需要从无主键的数据仓库表OldTable创建维度表NewTable：

• OldTable的IdentifierCode字段值唯一，其他字段存在重复
• 需要调用3个函数添加报表上下文
• 希望将IdentifierCode_ID设为INT类型（原IdentifierCode是VARCHAR(6)）

现有疑问：
使用ROW_NUMBER()生成的IdentifierCode_ID是否具备合适的唯一性？担心后续插入行修复遗漏编码时，生产表的行序变化会导致编号重复或不一致。

补充信息：OldTable总计50万行，经WHERE子句过滤后为22.7万行。

附上SQL代码：

SELECT
    ROW_NUMBER() OVER (ORDER BY LoadDate, StartDate, Product, IdentifierCode) AS IdentifierCode_ID,
    LoadDate, 
    StartDate,
    EndDate,
    Product,
    IdentifierCode,
    OtherField1, OtherField2, OtherField3, OtherField4,
    Function1, Function2, Function3
INTO 
    NewTable
FROM 
    OldTable
WHERE
    GETDATE() BETWEEN StartDate AND EndDate

• 当前生成的IdentifierCode_ID具备唯一性：你的ORDER BY子句最后指定了唯一字段IdentifierCode，这会让ROW_NUMBER()为每一行生成唯一的INT值，不会出现重复。
• 但后续插入修复数据时存在风险：如果后续向OldTable插入新的遗漏编码数据，再次执行这段SQL时，新生成的IdentifierCode_ID可能和之前的编号冲突或顺序混乱——因为ROW_NUMBER()是基于查询结果的排序生成的，新行的加入会改变原有行的编号序列。

更可靠的替代方案： #

1. 直接转换原唯一字段为INT：如果IdentifierCode是纯数字格式的VARCHAR(6)，可以直接用CAST(IdentifierCode AS INT) AS IdentifierCode_ID，既保证唯一性，又和原唯一字段一一对应，后续插入时编号逻辑完全一致。
2. 使用IDENTITY列：创建NewTable时先定义自增主键，再插入数据。这样每次插入都会自动生成唯一递增的INT值，不受原表行序变化影响。示例：

CREATE TABLE NewTable (
    IdentifierCode_ID INT IDENTITY(1,1) PRIMARY KEY,
    LoadDate DATE,
    StartDate DATE,
    EndDate DATE,
    Product VARCHAR(50),
    IdentifierCode VARCHAR(6),
    OtherField1 VARCHAR(50),
    OtherField2 VARCHAR(50),
    OtherField3 VARCHAR(50),
    OtherField4 VARCHAR(50),
    Function1 VARCHAR(50),
    Function2 VARCHAR(50),
    Function3 VARCHAR(50)
)

INSERT INTO NewTable (LoadDate, StartDate, EndDate, Product, IdentifierCode, OtherField1, OtherField2, OtherField3, OtherField4, Function1, Function2, Function3)
SELECT
    LoadDate, 
    StartDate,
    EndDate,
    Product,
    IdentifierCode,
    OtherField1, OtherField2, OtherField3, OtherField4,
    Function1, Function2, Function3
FROM 
    OldTable
WHERE
    GETDATE() BETWEEN StartDate AND EndDate

3. 哈希函数生成固定唯一值：如果IdentifierCode不是纯数字，可以用CHECKSUM(IdentifierCode)或CAST(HASHBYTES('SHA1', IdentifierCode) AS INT)转换为INT，但这种方式存在极小的哈希碰撞概率，需要提前验证。

总结 #

如果要保证后续数据插入时IdentifierCode_ID的一致性和唯一性，优先选择转换原唯一字段为INT或使用IDENTITY列，比依赖ROW_NUMBER()的方案更稳定。

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