Cara Efektif Menambahkan Kolom dan Memperbarui Baris di SQL Server

Menguasai Modifikasi Tabel di SQL Server: Panduan Langkah demi Langkah

Terkadang, bekerja dengan kumpulan data yang besar terasa seperti mencoba mengerjakan ratusan tugas sekaligus. Baru-baru ini, saya menemukan diri saya dalam situasi di mana saya perlu menambahkan kolom ke tabel yang berisi lebih dari satu juta baris. Meskipun ini tampak seperti tugas sederhana di permukaan, saya segera menemui hambatan yang dihadapi banyak pengguna SQL Server: kesalahan "Nama kolom tidak valid" yang ditakuti. 🧐

Setelah menjalankan beberapa upaya untuk menjalankan perintah ALTER TABLE dan UPDATE secara bersamaan, saya menyadari masalahnya bukan pada logikanya tetapi pada urutan pertanyaan saya. SQL Server mengharuskan Anda menambahkan kolom terlebih dahulu dan melakukan perubahan itu sebelum memperbaruinya dengan data apa pun. Gagal melakukannya akan mengakibatkan kesalahan karena sistem tidak mengenali kolom yang baru ditambahkan pada saat pembaruan dijalankan.

Misalnya, bayangkan Anda ditugaskan untuk memperbarui tanda "IS_CURRENT" berdasarkan ambang batas tanggal tertentu untuk database pelanggan yang besar. Jika Anda menambahkan kolom dan mencoba memperbarui baris dalam satu skrip, SQL Server mungkin memunculkan kesalahan "Nama kolom tidak valid". Hal ini karena kolom tersebut tidak sepenuhnya diterapkan sebelum kueri pembaruan mencoba menggunakannya. 🚀

Dalam artikel ini, kita akan membahas urutan yang tepat untuk menambahkan kolom dan memperbarui baris, memastikan kelancaran eksekusi bahkan dengan kumpulan data yang besar. Kami juga akan mempelajari tips mengoptimalkan skrip SQL untuk menangani jutaan baris secara efisien, memastikan operasi data Anda berjalan tanpa hambatan. Pantau terus kami saat kami mempelajari langkah-langkahnya dan memecahkan masalah umum selama prosesnya!

Memahami Perintah SQL Server untuk Mengubah Tabel dan Memperbarui Baris

Saat bekerja dengan SQL Server, terutama dengan tabel yang berisi kumpulan data besar, sangat penting untuk mengikuti pendekatan yang teratur dalam mengubah tabel dan memperbarui barisnya. Salah satu skenario umum adalah perlunya menambahkan kolom baru ke tabel dan kemudian memperbarui baris berdasarkan kondisi tertentu, seperti menyetel tanda berdasarkan tanggal. Skrip yang saya berikan menunjukkan pendekatan sederhana untuk ini, tetapi skrip ini menyoroti perintah-perintah utama SQL Server yang penting dalam mencapai tugas-tugas ini secara efisien. Itu MENGUBAH TABEL perintah digunakan untuk menambahkan kolom baru ke tabel. Misalnya, saat kita menjalankan `ALTER TABLE dbo.sample ADD IS_CURRENT BIT;`, kita memodifikasi struktur tabel untuk memperkenalkan kolom baru bernama `IS_CURRENT` dengan tipe `BIT` (tipe boolean, baik 0 atau 1).

Setelah menambahkan kolom, langkah selanjutnya adalah memperbarui baris-baris pada tabel berdasarkan kondisi tertentu. Hal ini dicapai dengan menggunakan MEMPERBARUI memerintah. Misalnya, kueri `UPDATE dbo.sample SET IS_CURRENT = 0 WHERE LOAD_DATE

Dalam beberapa kasus, terutama ketika berhadapan dengan tabel besar yang berisi jutaan baris, penting untuk memastikan bahwa perintah SQL dijalankan secara efisien. Di sinilah fungsinya TANGGALTAMBAHKAN Dan TANGGAL PERBEDAAN ikut bermain. Fungsi-fungsi ini memungkinkan Anda memanipulasi dan membandingkan tanggal dengan presisi. Dalam kueri pembaruan kedua, `DATEADD(bulan, DATEDIFF(bulan, 0, DATEADD(DAY, -60, GETDATE())), 0)` mengurangi 60 hari dari tanggal saat ini (`GETDATE()`) dan menyetel ulang waktu untuk awal bulan. Dengan menggunakan fungsi-fungsi ini, kita dapat menentukan rentang tanggal yang lebih dinamis yang menyesuaikan seiring berjalannya waktu, memastikan bahwa data tetap terkini meskipun seiring bertambahnya usia.

Namun, saat menggabungkan pernyataan `ALTER TABLE` dan `UPDATE` ke dalam satu skrip, SQL Server terkadang dapat memunculkan kesalahan "Nama kolom tidak valid". Hal ini terjadi karena kolom yang ditambahkan oleh `ALTER TABLE` mungkin tidak sepenuhnya dikomit atau dikenali oleh SQL Server selama eksekusi kueri berikutnya dalam batch yang sama. Solusi untuk masalah ini adalah dengan memisahkan pernyataan `ALTER TABLE` dan perintah `UPDATE`, memastikan bahwa perubahan tabel dilakukan sepenuhnya sebelum melakukan pembaruan. Dengan melakukan hal ini, SQL Server akan memiliki kolom baru yang terdaftar dengan benar dalam skemanya, memungkinkan pembaruan tabel yang lancar. Saat menangani kumpulan data yang besar, pertimbangkan untuk menjalankan operasi ini secara batch atau menggunakan transaksi untuk memastikan prosesnya seefisien mungkin, menghindari potensi waktu tunggu atau penguncian. 🚀

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';

Penjelasan Perintah SQL Tertentu yang Digunakan dalam Skrip

Mengoptimalkan Skrip SQL Server untuk Tabel Besar

Saat bekerja dengan tabel besar di SQL Server, penting untuk mempertimbangkan teknik pengoptimalan kinerja saat mengubah struktur tabel dan memperbarui baris yang ada. Salah satu masalah paling umum yang dihadapi saat menjalankan skrip pada tabel besar adalah waktu yang diperlukan untuk menyelesaikan operasi ini, terutama ketika tabel berisi lebih dari satu juta baris. Operasi yang dimaksud, seperti menambahkan kolom dengan MENGUBAH TABEL perintah dan memperbarui baris berdasarkan kondisi tanggal tertentu, dapat memakan banyak waktu. Mengoptimalkan operasi ini menjadi lebih penting ketika Anda mengerjakan database produksi yang mengutamakan kinerja. Satu skrip berpotensi mengunci tabel untuk waktu yang lama, sehingga memengaruhi kueri dan pengguna lain.

Untuk memitigasi masalah kinerja, salah satu pendekatan terbaik adalah dengan memecah tugas menjadi langkah-langkah yang lebih kecil. Misalnya, daripada menambahkan kolom dan memperbarui semua baris dalam satu skrip, pertimbangkan untuk menjalankan MENGUBAH TABEL perintah secara terpisah, diikuti dengan mengelompokkan UPDATE operasi. Dengan memperbarui catatan dalam potongan yang lebih kecil, skrip tidak akan membebani server. Anda dapat memanfaatkan UPDATE TOP perintah untuk membatasi jumlah baris yang terpengaruh dalam setiap transaksi. Selain itu, ada baiknya juga membuat indeks pada kolom yang digunakan di WHERE klausa (seperti LOAD_DATE) untuk mempercepat proses pencarian. Untuk kumpulan data besar, indeks mengurangi waktu yang diperlukan untuk memfilter baris berdasarkan rentang tanggal.

Pertimbangan penting lainnya adalah penggunaan transaksi dan penanganan kesalahan untuk memastikan bahwa operasi dijalankan secara atomik. Dengan membungkus Anda UPDATE pernyataan di dalam a BEGIN TRANSACTION Dan COMMIT, Anda memastikan bahwa perubahan dilakukan dengan cara yang aman dan konsisten. Jika ada bagian dari proses yang gagal, Anda dapat menggunakan ROLLBACK untuk mengembalikan perubahan, mencegah pembaruan parsial. Selain itu, menjalankan skrip di luar jam sibuk atau menggunakan Agen SQL Server untuk menjadwalkan operasi ini memastikan dampak minimal pada kinerja sistem. Dengan pengoptimalan ini, Anda dapat dengan aman menjalankan modifikasi kompleks pada tabel besar sambil menjaga integritas sistem. 🖥️