SQL Server で列を効果的に追加し、行を更新する方法

SQL Server でのテーブルの変更をマスターする: ステップバイステップ ガイド

大規模なデータセットを扱うと、100 ものタスクを一度にこなしているように感じることがあります。最近、100 万行を超えるテーブルに列を追加する必要があるという状況に陥りました。これは表面的には簡単な作業のように見えましたが、多くの SQL Server ユーザーが直面する障害、つまり恐ろしい「列名が無効です」エラーにすぐに遭遇しました。 🧐

ALTER TABLE コマンドと UPDATE コマンドを一緒に実行する試みを何度か実行した後、問題はロジックではなくクエリのシーケンスにあることがわかりました。 SQL Server では、データで更新する前に、最初に列を追加し、その変更をコミットする必要があります。これを行わないと、更新の実行時にシステムが新しく追加された列を認識しないため、エラーが発生します。

たとえば、大規模な顧客データベースの特定の日付しきい値に基づいて「IS_CURRENT」フラグを更新するというタスクを任されていると想像してください。単一のスクリプトで列を追加し、行を更新しようとすると、SQL Server が「無効な列名」エラーをスローする可能性があります。これは、更新クエリが列を使用しようとする前に列が完全にコミットされていないためです。 🚀

この記事では、大規模なデータセットでもスムーズに実行できるように、列を追加して行を更新するための適切なシーケンスを説明します。また、SQL スクリプトを最適化して数百万行を効率的に処理し、データ操作を滞りなく実行するためのヒントについても詳しく説明します。手順を確認し、途中で一般的な問題のトラブルシューティングを行っていきますので、ご期待ください。

テーブルの変更と行の更新のための SQL Server コマンドについて

SQL Server を使用する場合、特に大規模なデータセットを含むテーブルを使用する場合は、テーブルの変更とその行の更新に秩序あるアプローチに従うことが重要です。一般的なシナリオの 1 つは、テーブルに新しい列を追加し、日付に基づいてフラグを設定するなど、特定の条件に基づいて行を更新する必要があることです。私が提供したスクリプトは、これに対する簡単なアプローチを示していますが、これらのタスクを効率的に実行するために不可欠な主要な 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 コマンドが効率的に実行されることが重要です。ここでは次のような機能があります 日付追加 そして 日付の差分 遊びに来てください。これらの関数を使用すると、日付を正確に操作および比較できます。 2 番目の更新クエリでは、`DATEADD(month, DATEDIFF(month, 0, DATEADD(DAY, -60, GETDATE())), 0)` は現在の日付 (`GETDATE()`) から 60 日を減算し、月初めまでの時間。これらの関数を使用すると、時間の経過に応じて調整されるより動的な日付範囲を定義でき、データが古くなっても最新の状態を保つことができます。

ただし、「ALTER TABLE」ステートメントと「UPDATE」ステートメントの両方を 1 つのスクリプトに結合すると、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 で大規模なテーブルを操作する場合、テーブル構造を変更したり既存の行を更新したりするときに、パフォーマンスの最適化手法を考慮することが重要です。大規模なテーブルでスクリプトを実行するときに直面する最も一般的な問題の 1 つは、特にテーブルに 100 万行を超える行が含まれている場合に、これらの操作が完了するまでに時間がかかることです。問題の操作 (列の追加など) テーブルの変更 コマンドを実行し、特定の日付条件に基づいて行を更新するには、かなりの時間がかかる場合があります。パフォーマンスが最優先される運用データベースで作業している場合、これらの操作の最適化はさらに重要になります。単一のスクリプトによってテーブルが長期間ロックされ、他のクエリやユーザーに影響を与える可能性があります。

パフォーマンスの問題を軽減するための最良のアプローチの 1 つは、タスクを小さなステップに分割することです。たとえば、単一のスクリプトで列を追加してすべての行を更新するのではなく、 テーブルの変更 コマンドを個別に実行した後、バッチ処理する UPDATE 操作。レコードを小さなチャンクで更新することにより、スクリプトがサーバーに負荷をかけなくなります。を活用できます。 UPDATE TOP コマンドを使用して、各トランザクションで影響を受ける行の数を制限します。さらに、使用する列にインデックスを作成することもお勧めします。 WHERE 条項（など） LOAD_DATE) 検索プロセスを高速化します。大規模なデータセットの場合、インデックスを使用すると、日付範囲に基づいて行をフィルタリングするのにかかる時間が短縮されます。

もう 1 つの重要な考慮事項は、操作が確実にアトミックに実行されるようにするためのトランザクションとエラー処理の使用です。ラッピングすることで、 UPDATE 内のステートメント BEGIN TRANSACTION そして COMMITを使用すると、変更が安全かつ一貫した方法で行われるようになります。プロセスの一部が失敗した場合は、次を使用できます。 ROLLBACK 変更を元に戻し、部分的な更新を防ぎます。さらに、オフピーク時間にスクリプトを実行するか、SQL Server エージェントを使用してこれらの操作をスケジュールすると、システム パフォーマンスへの影響を最小限に抑えることができます。これらの最適化により、システムの整合性を維持しながら、大規模なテーブルに対する複雑な変更を安全に実行できます。 🖥️