SQL Server에서 효과적으로 열을 추가하고 행을 업데이트하는 방법

SQL Server의 테이블 수정 마스터링: 단계별 가이드

때로는 대규모 데이터 세트를 사용하여 작업하는 것이 한 번에 수백 가지 작업을 저글링하는 것처럼 느껴질 수 있습니다. 최근에 저는 백만 개가 넘는 행이 포함된 테이블에 열을 추가해야 하는 상황에 처했습니다. 표면적으로는 간단한 작업처럼 보였지만 많은 SQL Server 사용자가 직면하는 두려운 "잘못된 열 이름" 오류라는 장애물에 곧 부딪혔습니다. 🧐

ALTER TABLE 및 UPDATE 명령을 함께 실행하려고 여러 번 시도한 후에 문제는 논리가 아니라 쿼리 순서에 있다는 것을 깨달았습니다. SQL Server에서는 먼저 열을 추가하고 데이터로 업데이트하기 전에 해당 변경 사항을 커밋해야 합니다. 그렇지 않으면 업데이트가 실행될 때 시스템이 새로 추가된 열을 인식하지 못하기 때문에 오류가 발생합니다.

예를 들어, 대규모 고객 데이터베이스의 특정 날짜 임계값을 기반으로 "IS_CURRENT" 플래그를 업데이트하는 임무를 맡고 있다고 가정해 보겠습니다. 열을 추가하고 단일 스크립트에서 행을 업데이트하려고 하면 SQL Server에서 "잘못된 열 이름" 오류가 발생할 수 있습니다. 이는 업데이트 쿼리가 열을 사용하려고 시도하기 전에 열이 완전히 커밋되지 않았기 때문입니다. 🚀

이 문서에서는 열을 추가하고 행을 업데이트하는 적절한 순서를 살펴보고 대규모 데이터 세트에서도 원활한 실행을 보장합니다. 또한 수백만 개의 행을 효율적으로 처리하여 데이터 작업이 문제 없이 실행되도록 SQL 스크립트를 최적화하는 방법에 대해 자세히 알아봅니다. 단계를 탐색하고 그 과정에서 일반적인 문제를 해결하는 동안 계속 지켜봐 주시기 바랍니다!

테이블 변경 및 행 업데이트를 위한 SQL Server 명령 이해

SQL Server로 작업할 때, 특히 대규모 데이터 세트가 포함된 테이블을 사용하는 경우 테이블을 변경하고 해당 행을 업데이트하기 위해 질서 있는 접근 방식을 따르는 것이 중요합니다. 일반적인 시나리오 중 하나는 테이블에 새 열을 추가한 다음 날짜를 기반으로 플래그를 설정하는 것과 같이 특정 조건을 기반으로 행을 업데이트해야 하는 것입니다. 제가 제공한 스크립트는 이에 대한 간단한 접근 방식을 보여 주지만 이러한 작업을 효율적으로 수행하는 데 필수적인 주요 SQL Server 명령을 강조합니다. 그만큼 테이블 변경 명령은 테이블에 새 열을 추가하는 데 사용됩니다. 예를 들어 `ALTER TABLE dbo.sample ADD IS_CURRENT BIT;`를 실행하면 테이블 구조를 수정하여 `BIT` 유형(부울 유형, 0 또는 1)의 `IS_CURRENT`라는 새 열을 도입합니다.

열을 추가한 후 다음 단계는 특정 조건에 따라 테이블의 행을 업데이트하는 것입니다. 이는 다음을 사용하여 달성됩니다. 업데이트 명령. 예를 들어 쿼리 `UPDATE dbo.sample SET IS_CURRENT = 0 WHERE LOAD_DATE

어떤 경우에는 특히 수백만 개의 행이 포함된 대규모 테이블을 처리할 때 SQL 명령이 효율적으로 실행되는지 확인하는 것이 중요합니다. 이것은 다음과 같은 기능이 있는 곳입니다. 날짜추가 그리고 날짜 DATEIFF 플레이에 참여하세요. 이 함수를 사용하면 날짜를 정확하게 조작하고 비교할 수 있습니다. 두 번째 업데이트 쿼리에서는 `DATEADD(month, DATEDIFF(month, 0, DATEADD(DAY, -60, GETDATE())), 0)`는 현재 날짜(`GETDATE()`)에서 60일을 빼고 날짜를 재설정합니다. 달의 시작 시간. 이러한 기능을 사용하면 시간이 지남에 따라 조정되는 보다 동적인 날짜 범위를 정의하여 데이터가 오래되어도 최신 상태를 유지할 수 있습니다.

그러나 `ALTER TABLE` 및 `UPDATE` 문을 단일 스크립트로 결합하면 SQL Server에서 "잘못된 열 이름" 오류가 발생할 수 있습니다. 이는 `ALTER TABLE`에 의해 추가된 열이 동일한 일괄 처리에서 후속 쿼리를 실행하는 동안 SQL Server에서 완전히 커밋되거나 인식되지 않을 수 있기 때문에 발생합니다. 이 문제에 대한 해결책은 'ALTER TABLE' 문과 'UPDATE' 명령을 분리하여 업데이트를 수행하기 전에 테이블 변경이 완전히 커밋되었는지 확인하는 것입니다. 이렇게 하면 SQL Server의 스키마에 새 열이 제대로 등록되어 테이블을 원활하게 업데이트할 수 있습니다. 대규모 데이터세트를 처리할 때는 이러한 작업을 일괄적으로 실행하거나 트랜잭션을 사용하여 프로세스가 최대한 효율적이도록 하고 잠재적인 시간 초과나 잠금을 방지하는 것을 고려하세요. 🚀

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

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;

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

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

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 명령에 대한 설명

대규모 테이블에 대한 SQL Server 스크립트 최적화

SQL Server에서 대규모 테이블을 작업할 때 테이블 구조를 변경하고 기존 행을 업데이트할 때 성능 최적화 기술을 고려하는 것이 중요합니다. 대규모 테이블에서 스크립트를 실행할 때 직면하는 가장 일반적인 문제 중 하나는 특히 테이블에 백만 개 이상의 행이 포함된 경우 이러한 작업을 완료하는 데 걸리는 시간입니다. 문제의 작업(예: 테이블 변경 특정 날짜 조건에 따라 명령을 실행하고 행을 업데이트하는 데는 상당한 시간이 걸릴 수 있습니다. 성능이 최우선인 프로덕션 데이터베이스에서 작업할 때 이러한 작업을 최적화하는 것이 더욱 중요해집니다. 단일 스크립트는 잠재적으로 장기간 테이블을 잠가서 다른 쿼리와 사용자에게 영향을 미칠 수 있습니다.

성능 문제를 완화하기 위한 가장 좋은 접근 방식 중 하나는 작업을 더 작은 단계로 나누는 것입니다. 예를 들어 단일 스크립트에서 열을 추가하고 모든 행을 업데이트하는 대신 테이블 변경 별도로 명령을 내린 후 일괄 처리합니다. UPDATE 운영. 더 작은 청크로 레코드를 업데이트하면 스크립트가 서버를 압도하지 않습니다. 다음을 활용할 수 있습니다. UPDATE TOP 각 트랜잭션에 영향을 받는 행 수를 제한하는 명령입니다. 또한, 작업에 사용된 열에 인덱스를 생성하는 것도 좋은 생각입니다. WHERE 조항(예: LOAD_DATE) 검색 프로세스 속도를 높입니다. 대규모 데이터 세트의 경우 인덱스는 날짜 범위를 기준으로 행을 필터링하는 데 걸리는 시간을 줄여줍니다.

또 다른 중요한 고려 사항은 트랜잭션 및 오류 처리를 사용하여 작업이 원자적으로 실행되도록 하는 것입니다. 포장하여 UPDATE 내부의 진술 BEGIN TRANSACTION 그리고 COMMIT, 안전하고 일관된 방식으로 변경이 이루어졌는지 확인합니다. 프로세스의 일부가 실패하면 다음을 사용할 수 있습니다. ROLLBACK 변경 사항을 되돌리고 부분 업데이트를 방지합니다. 또한 사용량이 적은 시간에 스크립트를 실행하거나 SQL Server 에이전트를 사용하여 이러한 작업을 예약하면 시스템 성능에 미치는 영향을 최소화할 수 있습니다. 이러한 최적화를 통해 시스템 무결성을 유지하면서 대규모 테이블에서 복잡한 수정을 안전하게 실행할 수 있습니다. 🖥️