Cara Menambah Lajur dan Kemas Kini Baris dengan Berkesan dalam Pelayan SQL

Menguasai Pengubahsuaian Jadual dalam SQL Server: Panduan Langkah demi Langkah

Kadangkala, bekerja dengan set data yang besar boleh berasa seperti cuba menyesuaikan seratus tugas sekaligus. Baru-baru ini, saya mendapati diri saya berada dalam situasi di mana saya perlu menambah lajur pada jadual yang mengandungi lebih sejuta baris. Walaupun ini kelihatan seperti tugas mudah di permukaan, saya dengan cepat menghadapi sekatan jalan yang dihadapi oleh ramai pengguna SQL Server: ralat "Nama lajur tidak sah" yang digeruni. 🧐

Selepas menjalankan beberapa percubaan untuk melaksanakan arahan ALTER TABLE dan UPDATE saya bersama-sama, saya menyedari masalahnya bukan dengan logik tetapi dengan urutan pertanyaan saya. SQL Server memerlukan anda menambah lajur terlebih dahulu dan melakukan perubahan itu sebelum mengemas kininya dengan sebarang data. Kegagalan berbuat demikian mengakibatkan ralat kerana sistem tidak mengenali lajur yang baru ditambah pada masa kemas kini dilaksanakan.

Sebagai contoh, bayangkan anda ditugaskan untuk mengemas kini bendera "IS_CURRENT" berdasarkan ambang tarikh tertentu untuk pangkalan data pelanggan yang besar. Jika anda menambah lajur dan cuba mengemas kini baris dalam satu skrip, SQL Server mungkin membuang ralat "Nama lajur tidak sah". Ini kerana lajur tidak komited sepenuhnya sebelum pertanyaan kemas kini cuba menggunakannya. 🚀

Dalam artikel ini, kami akan menelusuri urutan yang betul untuk menambah lajur dan mengemas kini baris, memastikan pelaksanaan lancar walaupun dengan set data yang besar. Kami juga akan menyelami petua untuk mengoptimumkan skrip SQL untuk mengendalikan berjuta-juta baris dengan cekap, memastikan operasi data anda berjalan tanpa halangan. Nantikan semasa kami meneroka langkah-langkah dan menyelesaikan masalah biasa sepanjang perjalanan!

Memahami Perintah Pelayan SQL untuk Mengubah Jadual dan Mengemas kini Baris

Apabila bekerja dengan SQL Server, terutamanya dengan jadual yang mengandungi set data yang besar, adalah penting untuk mengikuti pendekatan yang teratur untuk mengubah jadual dan mengemas kini barisnya. Satu senario biasa perlu menambah lajur baharu pada jadual dan kemudian mengemas kini baris berdasarkan syarat tertentu, seperti menetapkan bendera berdasarkan tarikh. Skrip yang saya sediakan menunjukkan pendekatan mudah untuk ini, tetapi ia menyerlahkan arahan SQL Server utama yang penting dalam mencapai tugas ini dengan cekap. The UBAH JADUAL arahan digunakan untuk menambah lajur baharu pada jadual. Sebagai contoh, apabila kami menjalankan `ALTER TABLE dbo.sample ADD IS_CURRENT BIT;`, kami mengubah suai struktur jadual untuk memperkenalkan lajur baharu bernama `IS_CURRENT` jenis `BIT` (jenis boolean, sama ada 0 atau 1).

Selepas menambah lajur, langkah seterusnya ialah mengemas kini baris dalam jadual berdasarkan syarat tertentu. Ini dicapai menggunakan KEMASKINI perintah. Contohnya, pertanyaan `KEMASKINI dbo.sample SET IS_CURRENT = 0 WHERE LOAD_DATE

Dalam sesetengah kes, terutamanya apabila berurusan dengan jadual besar yang mengandungi berjuta-juta baris, adalah penting untuk memastikan arahan SQL dilaksanakan dengan cekap. Di sinilah berfungsi seperti DATEADD dan DATEDIFF mula bermain. Fungsi ini membolehkan anda memanipulasi dan membandingkan tarikh dengan ketepatan. Dalam pertanyaan kemas kini kedua, `DATEADD(bulan, DATEDIFF(bulan, 0, DATEADD(DAY, -60, GETDATE())), 0)` menolak 60 hari dari tarikh semasa (`GETDATE()`) dan menetapkan semula masa untuk permulaan bulan. Dengan menggunakan fungsi ini, kami boleh mentakrifkan julat tarikh yang lebih dinamik yang melaraskan mengikut peredaran masa, memastikan data kekal terkini walaupun semakin tua.

Walau bagaimanapun, apabila menggabungkan kedua-dua pernyataan `ALTER TABLE` dan `KEMASKINI` ke dalam satu skrip, SQL Server kadangkala boleh membuang ralat "Nama lajur tidak sah". Ini berlaku kerana lajur yang ditambahkan oleh `ALTER TABLE` mungkin tidak komited sepenuhnya atau diiktiraf oleh SQL Server semasa pelaksanaan pertanyaan berikutnya dalam kelompok yang sama. Penyelesaian kepada isu ini adalah untuk memisahkan penyataan `ALTER TABLE` dan arahan `KEMASKINI`, memastikan bahawa perubahan jadual dilakukan sepenuhnya sebelum melakukan kemas kini. Dengan berbuat demikian, SQL Server akan mempunyai lajur baharu didaftarkan dengan betul dalam skemanya, membolehkan kemas kini lancar pada jadual. Apabila mengendalikan set data yang besar, pertimbangkan untuk melaksanakan operasi ini secara berkelompok atau menggunakan urus niaga untuk memastikan proses itu secekap mungkin, mengelakkan kemungkinan tamat masa atau kunci. 🚀

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 Khusus yang Digunakan dalam Skrip

Mengoptimumkan Skrip Pelayan SQL untuk Jadual Besar

Apabila bekerja dengan jadual besar dalam SQL Server, adalah penting untuk mempertimbangkan teknik pengoptimuman prestasi apabila mengubah struktur jadual dan mengemas kini baris sedia ada. Salah satu isu yang paling biasa dihadapi semasa menjalankan skrip pada jadual besar ialah masa yang diperlukan untuk operasi ini selesai, terutamanya apabila jadual mengandungi lebih sejuta baris. Operasi yang dipersoalkan, seperti menambah lajur dengan UBAH JADUAL perintah dan mengemas kini baris berdasarkan keadaan tarikh tertentu, boleh mengambil masa yang banyak. Mengoptimumkan operasi ini menjadi lebih penting apabila anda mengusahakan pangkalan data pengeluaran di mana prestasi menjadi keutamaan. Skrip tunggal berpotensi mengunci jadual untuk tempoh yang lama, menjejaskan pertanyaan dan pengguna lain.

Untuk mengurangkan isu prestasi, salah satu pendekatan terbaik ialah memecahkan tugas kepada langkah yang lebih kecil. Sebagai contoh, daripada menambah lajur dan mengemas kini semua baris dalam satu skrip, pertimbangkan untuk menjalankan UBAH JADUAL arahan secara berasingan, diikuti dengan batching the UPDATE operasi. Dengan mengemas kini rekod dalam bahagian yang lebih kecil, skrip tidak akan mengatasi pelayan. Anda boleh memanfaatkan UPDATE TOP perintah untuk mengehadkan bilangan baris yang terjejas dalam setiap transaksi. Selain itu, adalah idea yang baik untuk membuat indeks pada lajur yang digunakan dalam anda WHERE klausa (seperti LOAD_DATE) untuk mempercepatkan proses carian. Untuk set data yang besar, indeks mengurangkan masa yang diperlukan untuk menapis baris berdasarkan julat tarikh.

Satu lagi pertimbangan penting ialah penggunaan transaksi dan pengendalian ralat untuk memastikan operasi dilaksanakan secara atom. Dengan membungkus anda UPDATE pernyataan di dalam a BEGIN TRANSACTION dan COMMIT, anda memastikan bahawa perubahan dibuat dengan cara yang selamat dan konsisten. Jika mana-mana bahagian proses gagal, anda boleh gunakan ROLLBACK untuk mengembalikan perubahan, menghalang kemas kini separa. Selain itu, menjalankan skrip semasa waktu luar puncak atau menggunakan Ejen Pelayan SQL untuk menjadualkan operasi ini memastikan kesan minimum pada prestasi sistem. Dengan pengoptimuman ini, anda boleh melaksanakan pengubahsuaian kompleks dengan selamat pada jadual besar sambil mengekalkan integriti sistem. 🖥️