Kako učinkovito dodati stolpec in posodobiti vrstice v SQL Server

Obvladovanje sprememb tabel v strežniku SQL: vodnik po korakih

Včasih se delo z velikimi nabori podatkov zdi, kot da bi poskušali žonglirati s sto nalogami hkrati. Pred kratkim sem se znašel v situaciji, ko sem moral dodati stolpec v tabelo, ki vsebuje več kot milijon vrstic. Čeprav se je to na prvi pogled zdelo preprosto opravilo, sem hitro naletel na oviro, s katero se srečujejo številni uporabniki strežnika SQL: strašna napaka »Neveljavno ime stolpca«. 🧐

Po več poskusih za skupno izvedbo ukazov ALTER TABLE in UPDATE sem ugotovil, da težava ni v logiki, temveč v zaporedju mojih poizvedb. SQL Server zahteva, da najprej dodate stolpec in potrdite to spremembo, preden jo posodobite s kakršnimi koli podatki. Če tega ne storite, pride do napake, ker sistem v času izvajanja posodobitve ne prepozna novo dodanega stolpca.

Na primer, predstavljajte si, da ste zadolženi za posodobitev zastavice "IS_CURRENT" na podlagi določenega datumskega praga za veliko podatkovno bazo strank. Če dodate stolpec in poskušate posodobiti vrstice v enem samem skriptu, lahko strežnik SQL vrže napako »Neveljavno ime stolpca«. To je zato, ker stolpec ni v celoti potrjen, preden ga poizvedba za posodobitev poskusi uporabiti. 🚀

V tem članku se bomo sprehodili skozi pravilno zaporedje za dodajanje stolpca in posodobitev vrstic, kar bo zagotovilo nemoteno izvajanje tudi pri velikih naborih podatkov. Poglobili se bomo tudi v nasvete za optimizacijo skriptov SQL za učinkovito obdelavo milijonov vrstic, s čimer zagotovimo, da vaše podatkovne operacije potekajo brez težav. Ostanite z nami, ko raziskujemo korake in na poti odpravljamo pogoste težave!

Razumevanje ukazov strežnika SQL za spreminjanje tabel in posodabljanje vrstic

Pri delu s strežnikom SQL Server, zlasti s tabelami, ki vsebujejo velike nabore podatkov, je ključnega pomena, da sledite urejenemu pristopu k spreminjanju tabele in posodabljanju njenih vrstic. Eden pogostih scenarijev je, da je treba v tabelo dodati nov stolpec in nato posodobiti vrstice na podlagi določenih pogojev, kot je nastavitev zastavice na podlagi datumov. Skript, ki sem ga zagotovil, prikazuje preprost pristop k temu, vendar poudarja ključne ukaze SQL Server, ki so bistveni za učinkovito doseganje teh nalog. The SPREMENI TABELO ukaz se uporablja za dodajanje novega stolpca v tabelo. Na primer, ko zaženemo `ALTER TABLE dbo.sample ADD IS_CURRENT BIT;`, spremenimo strukturo tabele, da uvedemo nov stolpec z imenom `IS_CURRENT` tipa `BIT` (logični tip, 0 ali 1).

Po dodajanju stolpca je naslednji korak posodobitev vrstic v tabeli glede na določene pogoje. To se doseže z uporabo POSODOBITEV ukaz. Na primer, poizvedba `UPDATE dbo.sample SET IS_CURRENT = 0 WHERE LOAD_DATE

V nekaterih primerih, še posebej, ko imate opravka z velikimi tabelami, ki vsebujejo milijone vrstic, je pomembno zagotoviti, da se ukazi SQL izvajajo učinkovito. Tukaj deluje kot DATEADD in DATEDIFF pridejo v poštev. Te funkcije vam omogočajo natančno obdelavo in primerjavo datumov. V drugi poizvedbi za posodobitev `DATEADD(month, DATEDIFF(month, 0, DATEADD(DAY, -60, GETDATE())), 0)` odšteje 60 dni od trenutnega datuma (`GETDATE()`) in ponastavi časa do začetka meseca. Z uporabo teh funkcij lahko definiramo bolj dinamična časovna obdobja, ki se prilagajajo s časom, kar zagotavlja, da podatki ostanejo aktualni, tudi ko se starajo.

Vendar pa lahko SQL Server ob kombiniranju stavkov `ALTER TABLE` in `UPDATE` v en skript včasih vrže napako »Neveljavno ime stolpca«. To se zgodi, ker stolpca, ki ga je dodal `ALTER TABLE`, morda ne bo v celoti potrdil ali prepoznal SQL Server med izvajanjem naslednjih poizvedb v istem paketu. Rešitev za to težavo je, da ločite stavek `ALTER TABLE` in ukaze `UPDATE`, s čimer zagotovite, da je sprememba tabele v celoti potrjena pred izvajanjem posodobitev. S tem bo imel SQL Server nov stolpec pravilno registriran v svoji shemi, kar bo omogočilo nemoteno posodabljanje tabele. Pri delu z velikimi nabori podatkov razmislite o izvajanju teh operacij v paketih ali uporabi transakcij, da zagotovite, da je postopek čim bolj učinkovit, in se izognete morebitnim časovnim omejitvam ali zaklepom. 🚀

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

Razlaga posebnih ukazov SQL, uporabljenih v skriptih

Optimiziranje skriptov SQL Server za velike tabele

Ko delate z velikimi tabelami v strežniku SQL Server, je pomembno, da pri spreminjanju strukture tabele in posodabljanju obstoječih vrstic upoštevate tehnike optimizacije delovanja. Ena najpogostejših težav, s katerimi se srečujete pri izvajanju skriptov v velikih tabelah, je čas, ki je potreben za dokončanje teh operacij, zlasti če tabela vsebuje več kot milijon vrstic. Zadevne operacije, kot je dodajanje stolpca z SPREMENI TABELO in posodabljanje vrstic na podlagi določenih datumskih pogojev lahko traja precej časa. Optimizacija teh operacij postane še bolj pomembna, ko delate na produkcijskih bazah podatkov, kjer je zmogljivost prednostna naloga. En sam skript lahko potencialno zaklene tabelo za daljša obdobja, kar vpliva na druge poizvedbe in uporabnike.

Za ublažitev težav z zmogljivostjo je eden najboljših pristopov razdelitev nalog na manjše korake. Na primer, namesto dodajanja stolpca in posodabljanja vseh vrstic v enem skriptu razmislite o zagonu SPREMENI TABELO ukaz ločeno, čemur sledi serijsko združevanje UPDATE operacije. S posodabljanjem zapisov v manjših kosih skript ne bo preobremenil strežnika. Lahko izkoristite UPDATE TOP ukaz za omejitev števila zadevnih vrstic v vsaki transakciji. Poleg tega je dobro ustvariti indekse za stolpce, ki se uporabljajo v vašem WHERE klavzule (kot npr LOAD_DATE), da pospešite postopek iskanja. Za velike nabore podatkov indeksi skrajšajo čas, ki je potreben za filtriranje vrstic glede na časovna obdobja.

Drug pomemben vidik je uporaba transakcij in obravnavanje napak za zagotovitev, da se operacije izvajajo atomsko. Z zavijanjem svojega UPDATE izjave znotraj a BEGIN TRANSACTION in COMMIT, zagotovite, da so spremembe narejene na varen in dosleden način. Če kateri koli del postopka ne uspe, lahko uporabite ROLLBACK za razveljavitev sprememb in preprečitev delnih posodobitev. Poleg tega izvajanje skriptov med urami izven obremenitve ali uporaba agenta SQL Server za načrtovanje teh operacij zagotavlja minimalen vpliv na zmogljivost sistema. S temi optimizacijami lahko varno izvajate zapletene spremembe na velikih tabelah, hkrati pa ohranjate celovitost sistema. 🖥️