不删除百万级数据表，将现有列修改为Primary Key和NOT NULL

Alright, let's tackle this problem step by step—since you're dealing with a table with millions of records, we need to prioritize data integrity, minimize downtime, and avoid locking up the table for too long. Here's a safe, actionable plan:

1. 先验证目标列的当前状态 #

Before making any changes, you need to confirm two critical things about the column you want to set as the primary key:

• No NULL values: Primary keys can't contain NULLs.
• No duplicate values: Primary keys must be unique.

Run these queries to check:

-- Check for NULL values in your target column (replace [TargetColumn] with actual column name)
SELECT COUNT(*) AS NullCount
FROM dbo.RefDetails
WHERE [TargetColumn] IS NULL;

-- Check for duplicate values in your target column
SELECT [TargetColumn], COUNT(*) AS DuplicateCount
FROM dbo.RefDetails
GROUP BY [TargetColumn]
HAVING COUNT(*) > 1;

2. 修复数据问题（如果存在） #

如果有NULL值： #

你需要将这些NULL更新为有效的非空值，具体方案要贴合业务逻辑，以下是两种常用思路：

-- 方案1：生成唯一的占位值（适合无业务关联的场景）
UPDATE dbo.RefDetails
SET [TargetColumn] = 'DEFAULT_' + CAST(NEWID() AS VARCHAR(36)) -- 生成唯一字符串
WHERE [TargetColumn] IS NULL;

-- 方案2：基于其他列推导有效值（适合有业务关联的场景）
-- 示例：UPDATE dbo.RefDetails SET [TargetColumn] = REQUEST_ID + '_' + CROSS_REFERENCE WHERE [TargetColumn] IS NULL;

如果有重复值： #

重复值会破坏主键的唯一性要求，必须先处理。常用的方式是保留最新记录（基于RUN_DATE或RECORDSTARTDATE）并删除重复项：

-- 示例：删除重复项，保留RUN_DATE最新的记录
WITH DuplicateCTE AS (
    SELECT 
        [TargetColumn],
        RUN_DATE,
        ROW_NUMBER() OVER (PARTITION BY [TargetColumn] ORDER BY RUN_DATE DESC) AS RowNum
    FROM dbo.RefDetails
)
DELETE FROM DuplicateCTE
WHERE RowNum > 1;

注意：一定要先在备份表上测试该逻辑，确保不会误删关键数据。

3. 备份数据表（关键步骤！） #

在修改表结构前，创建备份以防操作失误：

-- 创建表的完整备份
SELECT * INTO dbo.RefDetails_Backup FROM dbo.RefDetails;

4. 修改列属性为NOT NULL #

数据清理完成后，修改列以强制非空约束：

-- 替换[TargetColumn]和对应的数据类型为你的实际列信息
ALTER TABLE dbo.RefDetails
ALTER COLUMN [TargetColumn] [varchar(24)] NOT NULL; -- 匹配CREATE TABLE脚本中的原始数据类型

5. 添加主键约束 #

最后创建主键约束。对于大表，若使用SQL Server企业版/开发版，建议加上ONLINE = ON选项，这样在创建索引期间仍允许读取操作，减少停机时间：

-- 创建聚集主键（多数场景下推荐）
ALTER TABLE dbo.RefDetails
ADD CONSTRAINT PK_RefDetails_[TargetColumn] PRIMARY KEY CLUSTERED ([TargetColumn])
WITH (ONLINE = ON, MAXDOP = 1); -- MAXDOP=1可避免繁忙服务器上的资源竞争

-- 如果表已有其他聚集索引，改用非聚集主键：
-- ALTER TABLE dbo.RefDetails
-- ADD CONSTRAINT PK_RefDetails_[TargetColumn] PRIMARY KEY NONCLUSTERED ([TargetColumn])
-- WITH (ONLINE = ON);

大表操作的注意事项： #

• 低峰期执行：百万级数据的结构修改耗时较长，建议在业务低峰时段操作。
• 监控锁状态：用SQL Server管理工具的活动监视器观察阻塞情况。
• 先在测试环境验证：在 staging 环境复刻表并执行所有步骤，确认无误后再推广到生产。

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