如何在 SQL Server 中有效添加列和更新行

解决方案 1：更改表和更新行的标准方法

此解决方案涉及使用 SQL Server Management Studio (SSMS) 的标准方法，其中我们首先添加列，然后使用适当的条件更新行。我们运行 ALTER TABLE 语句并在执行任何更新之前提交它。

ALTER TABLE dbo.sample ADD IS_CURRENT BIT;
GO
UPDATE dbo.sample
SET IS_CURRENT = 0
WHERE LOAD_DATE < '2025-01-01';
GO
UPDATE dbo.sample
SET IS_CURRENT = 0
WHERE LOAD_DATE >= CAST(DATEADD(month, DATEDIFF(month, 0, DATEADD(DAY, -60, GETDATE())), 0) AS DATE);
GO

解决方案2：使用事务原子性的优化方法

该解决方案确保表修改和行更新以原子方式完成。通过将操作包装在事务中，我们可以确保一致性并在失败时进行回滚。

BEGIN TRANSACTION;
ALTER TABLE dbo.sample ADD IS_CURRENT BIT;
UPDATE dbo.sample
SET IS_CURRENT = 0
WHERE LOAD_DATE < '2025-01-01';
UPDATE dbo.sample
SET IS_CURRENT = 0
WHERE LOAD_DATE >= CAST(DATEADD(month, DATEDIFF(month, 0, DATEADD(DAY, -60, GETDATE())), 0) AS DATE);
COMMIT TRANSACTION;

解决方案 3：对大型数据集使用批处理的方法

当处理包含超过一百万行的表时，最小化锁定并减少事务大小至关重要。该解决方案以较小的批次处理更新，以提高性能并防止超时。

DECLARE @BatchSize INT = 10000;
DECLARE @RowCount INT;
SELECT @RowCount = COUNT(*) FROM dbo.sample WHERE IS_CURRENT IS ;
WHILE @RowCount > 0
BEGIN
    UPDATE TOP (@BatchSize) dbo.sample
    SET IS_CURRENT = 0
    WHERE LOAD_DATE < '2025-01-01' AND IS_CURRENT IS ;
    SET @RowCount = @RowCount - @BatchSize;
END

解决方案 4：使用索引视图来提高性能

为了提高查询大型数据集时的性能，您可以在 SQL Server 中创建索引视图。这种方法利用物化视图来存储复杂查询的结果，减少重复数据处理的需要。

CREATE VIEW dbo.Sample_View AS
SELECT LOAD_DATE, IS_CURRENT
FROM dbo.sample
WHERE LOAD_DATE < '2025-01-01';
GO
CREATE UNIQUE CLUSTERED INDEX idx_sample_view ON dbo.Sample_View (LOAD_DATE);
GO
UPDATE dbo.sample
SET IS_CURRENT = 0
FROM dbo.Sample_View v
WHERE dbo.sample.LOAD_DATE = v.LOAD_DATE;
GO

解决方案 5：使用 SQL Server 代理作业进行计划更新

如果需要定期更新表，可以使用 SQL Server 代理创建按特定时间间隔执行更新过程的作业，从而避免手动执行。

EXEC msdb.dbo.sp_add_job @job_name = 'Update IS_CURRENT Job';
EXEC msdb.dbo.sp_add_jobstep @job_name = 'Update IS_CURRENT Job',
@step_name = 'Update IS_CURRENT Step',
@subsystem = 'TSQL',
@command = 'UPDATE dbo.sample SET IS_CURRENT = 0 WHERE LOAD_DATE < ''2025-01-01'';',
@retry_attempts = 5, @retry_interval = 5;
EXEC msdb.dbo.sp_add_schedule @schedule_name = 'Daily Schedule',
@enabled = 1, @freq_type = 4, @freq_interval = 1, @active_start_time = 010000;
EXEC msdb.dbo.sp_attach_schedule @job_name = 'Update IS_CURRENT Job', @schedule_name = 'Daily Schedule';
EXEC msdb.dbo.sp_start_job @job_name = 'Update IS_CURRENT Job';

优化 SQL Server 中的大型表修改

使用 SQL Server 修改大型表时，分解操作是提高性能的关键。向包含数百万行的表添加列并根据特定条件更新数据可能是一项挑战。这需要战略性地执行命令，例如 修改表 和 UPDATE 以确保应用更改时不会使系统不堪重负。

此外，实施最佳实践（例如批量更新、使用索引以及在非高峰时段运行脚本）可以帮助防止表锁定和性能下降等问题。通过拆分工作负载和优化查询，您可以安全地进行大规模更改，而不会导致停机或“无效列名”等错误。 💻