Cách thêm cột và cập nhật hàng hiệu quả trong SQL Server

Làm chủ các sửa đổi bảng trong SQL Server: Hướng dẫn từng bước

Đôi khi, làm việc với các tập dữ liệu lớn có thể giống như cố gắng thực hiện hàng trăm nhiệm vụ cùng một lúc. Gần đây, tôi thấy mình rơi vào tình huống cần thêm một cột vào bảng chứa hơn một triệu hàng. Mặc dù nhìn bề ngoài thì đây có vẻ là một nhiệm vụ đơn giản nhưng tôi nhanh chóng gặp phải một rào cản mà nhiều người dùng SQL Server gặp phải: lỗi "Tên cột không hợp lệ" đáng sợ. 🧐

Sau khi thử thực thi nhiều lệnh ALTER TABLE và UPDATE cùng nhau, tôi nhận ra vấn đề không nằm ở logic mà nằm ở chuỗi truy vấn của tôi. SQL Server yêu cầu bạn thêm cột trước và xác nhận thay đổi đó trước khi cập nhật cột đó với bất kỳ dữ liệu nào. Không làm như vậy sẽ dẫn đến lỗi do hệ thống không nhận dạng được cột mới được thêm vào thời điểm thực hiện cập nhật.

Ví dụ: hãy tưởng tượng bạn được giao nhiệm vụ cập nhật cờ "IS_CURRENT" dựa trên ngưỡng ngày cụ thể cho cơ sở dữ liệu khách hàng lớn. Nếu bạn thêm cột và cố gắng cập nhật các hàng trong một tập lệnh, SQL Server có thể đưa ra lỗi "Tên cột không hợp lệ". Điều này là do cột không được cam kết đầy đủ trước khi truy vấn cập nhật cố gắng sử dụng nó. 🚀

Trong bài viết này, chúng ta sẽ tìm hiểu trình tự thích hợp để thêm cột và cập nhật các hàng, đảm bảo thực thi suôn sẻ ngay cả với các tập dữ liệu lớn. Chúng tôi cũng sẽ đi sâu vào các mẹo tối ưu hóa tập lệnh SQL để xử lý hàng triệu hàng một cách hiệu quả, đảm bảo rằng hoạt động dữ liệu của bạn diễn ra suôn sẻ. Hãy chú ý theo dõi khi chúng tôi khám phá các bước và khắc phục sự cố thường gặp trong quá trình thực hiện!

Hiểu các lệnh máy chủ SQL để thay đổi bảng và cập nhật hàng

Khi làm việc với SQL Server, đặc biệt là với các bảng chứa tập dữ liệu lớn, điều quan trọng là phải tuân theo cách tiếp cận có trật tự để thay đổi bảng và cập nhật các hàng của bảng. Một trường hợp phổ biến là cần thêm cột mới vào bảng rồi cập nhật các hàng dựa trên các điều kiện cụ thể, chẳng hạn như đặt cờ dựa trên ngày tháng. Tập lệnh mà tôi cung cấp thể hiện một cách tiếp cận đơn giản để thực hiện việc này, nhưng nó nêu bật các lệnh SQL Server quan trọng cần thiết để đạt được các tác vụ này một cách hiệu quả. các BẢNG THAY ĐỔI lệnh được sử dụng để thêm một cột mới vào bảng. Ví dụ: khi chúng tôi chạy `ALTER TABLE dbo.sample ADD IS_CURRENT BIT;`, chúng tôi đang sửa đổi cấu trúc bảng để giới thiệu một cột mới có tên `IS_CURRENT` thuộc loại `BIT` (một loại boolean, 0 hoặc 1).

Sau khi thêm cột, bước tiếp theo là cập nhật các hàng trong bảng dựa trên các điều kiện nhất định. Điều này đạt được bằng cách sử dụng CẬP NHẬT yêu cầu. Ví dụ: truy vấn `UPDATE dbo.sample SET IS_CURRENT = 0 WHERE LOAD_DATE

Trong một số trường hợp, đặc biệt là khi xử lý các bảng lớn chứa hàng triệu hàng, điều quan trọng là phải đảm bảo rằng các lệnh SQL được thực thi hiệu quả. Đây là nơi có chức năng như DATEADD Và DATEDIFF đi vào chơi. Các hàm này cho phép bạn thao tác và so sánh ngày tháng một cách chính xác. Trong truy vấn cập nhật thứ hai, `DATEADD(tháng, DATEDIFF(tháng, 0, DATEADD(DAY, -60, GETDATE())), 0)` trừ 60 ngày kể từ ngày hiện tại (`GETDATE()`) và đặt lại thời điểm đến đầu tháng. Bằng cách sử dụng các hàm này, chúng tôi có thể xác định phạm vi ngày linh hoạt hơn và điều chỉnh theo thời gian, đảm bảo rằng dữ liệu vẫn cập nhật ngay cả khi dữ liệu cũ đi.

Tuy nhiên, khi kết hợp cả hai câu lệnh `ALTER TABLE` và `UPDATE` thành một tập lệnh duy nhất, SQL Server đôi khi có thể đưa ra lỗi "Tên cột không hợp lệ". Điều này xảy ra vì cột được thêm bởi `ALTER TABLE` có thể không được SQL Server cam kết hoặc nhận dạng đầy đủ trong quá trình thực hiện các truy vấn tiếp theo trong cùng một đợt. Giải pháp cho vấn đề này là tách câu lệnh `ALTER TABLE` và lệnh `UPDATE`, đảm bảo rằng việc thay đổi bảng được thực hiện đầy đủ trước khi thực hiện cập nhật. Bằng cách đó, SQL Server sẽ có cột mới được đăng ký chính xác trong lược đồ của nó, cho phép cập nhật bảng một cách suôn sẻ. Khi xử lý các tập dữ liệu lớn, hãy cân nhắc thực hiện các thao tác này theo đợt hoặc sử dụng giao dịch để đảm bảo quy trình hiệu quả nhất có thể, tránh nguy cơ hết thời gian chờ hoặc bị khóa. 🚀

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

Giải thích các lệnh SQL cụ thể được sử dụng trong tập lệnh

Tối ưu hóa tập lệnh SQL Server cho các bảng lớn

Khi làm việc với các bảng lớn trong SQL Server, điều quan trọng là phải xem xét các kỹ thuật tối ưu hóa hiệu suất khi thay đổi cấu trúc bảng và cập nhật các hàng hiện có. Một trong những vấn đề phổ biến nhất gặp phải khi chạy tập lệnh trên các bảng lớn là thời gian để hoàn thành các thao tác này, đặc biệt khi bảng chứa hơn một triệu hàng. Các thao tác được đề cập, chẳng hạn như thêm một cột với BẢNG THAY ĐỔI lệnh và cập nhật các hàng dựa trên các điều kiện ngày cụ thể, có thể mất một lượng thời gian đáng kể. Việc tối ưu hóa các hoạt động này càng trở nên quan trọng hơn khi bạn đang làm việc trên cơ sở dữ liệu sản xuất trong đó hiệu suất là ưu tiên hàng đầu. Một tập lệnh có thể khóa bảng trong thời gian dài, ảnh hưởng đến các truy vấn và người dùng khác.

Để giảm thiểu các vấn đề về hiệu suất, một trong những cách tiếp cận tốt nhất là chia nhỏ nhiệm vụ thành các bước nhỏ hơn. Ví dụ: thay vì thêm một cột và cập nhật tất cả các hàng trong một tập lệnh, hãy xem xét việc chạy lệnh BẢNG THAY ĐỔI lệnh riêng biệt, tiếp theo là phân nhóm UPDATE hoạt động. Bằng cách cập nhật các bản ghi theo từng phần nhỏ hơn, tập lệnh sẽ không làm máy chủ bị quá tải. Bạn có thể tận dụng UPDATE TOP lệnh để giới hạn số lượng hàng bị ảnh hưởng trong mỗi giao dịch. Ngoài ra, bạn cũng nên tạo chỉ mục trên các cột được sử dụng trong WHERE mệnh đề (chẳng hạn như LOAD_DATE) để tăng tốc quá trình tìm kiếm. Đối với các tập dữ liệu lớn, chỉ mục sẽ giảm thời gian cần thiết để lọc các hàng dựa trên phạm vi ngày.

Một cân nhắc quan trọng khác là việc sử dụng các giao dịch và xử lý lỗi để đảm bảo rằng các hoạt động được thực hiện một cách nguyên tử. Bằng cách gói của bạn UPDATE các câu lệnh bên trong một BEGIN TRANSACTION Và COMMIT, bạn đảm bảo rằng những thay đổi được thực hiện một cách an toàn và nhất quán. Nếu bất kỳ phần nào của quá trình không thành công, bạn có thể sử dụng ROLLBACK để hoàn nguyên các thay đổi, ngăn chặn cập nhật một phần. Ngoài ra, việc chạy tập lệnh trong giờ thấp điểm hoặc sử dụng SQL Server Agent để lên lịch cho các hoạt động này sẽ đảm bảo tác động tối thiểu đến hiệu suất hệ thống. Với những tối ưu hóa này, bạn có thể thực hiện các sửa đổi phức tạp một cách an toàn trên các bảng lớn trong khi vẫn duy trì tính toàn vẹn của hệ thống. 🖥️