Kaip efektyviai pridėti stulpelį ir atnaujinti eilutes SQL serveryje

Lentelių modifikacijų įvaldymas SQL serveryje: nuoseklus vadovas

Kartais dirbant su dideliais duomenų rinkiniais gali atrodyti, kad vienu metu bandote žongliruoti šimtu užduočių. Neseniai atsidūriau situacijoje, kai į lentelę, kurioje yra daugiau nei milijonas eilučių, reikėjo pridėti stulpelį. Nors iš pirmo žvilgsnio tai atrodė paprasta užduotis, greitai susidūriau su kliūtimi, su kuria susiduria daugelis SQL serverio vartotojų: klaidą „Neteisingas stulpelio pavadinimas“. 🧐

Paleidęs keletą bandymų kartu vykdyti komandas ALTER TABLE ir UPDATE, supratau, kad problema kilo ne dėl logikos, o su mano užklausų seka. SQL serveris reikalauja, kad pirmiausia pridėtumėte stulpelį ir atliktumėte šį pakeitimą prieš atnaujindami jį bet kokiais duomenimis. Jei to nepadarysite, įvyksta klaida, nes sistema neatpažįsta naujai pridėto stulpelio tuo metu, kai vykdomas naujinimas.

Pavyzdžiui, įsivaizduokite, kad jums pavesta atnaujinti žymą „IS_CURRENT“, atsižvelgiant į konkrečią didelės klientų duomenų bazės datos slenkstį. Jei pridėsite stulpelį ir bandysite atnaujinti eilutes viename scenarijuje, SQL serveris gali pateikti klaidą „Neteisingas stulpelio pavadinimas“. Taip yra todėl, kad stulpelis nėra visiškai priskirtas prieš atnaujinimo užklausai bandant jį naudoti. 🚀

Šiame straipsnyje apžvelgsime tinkamą stulpelio pridėjimo ir eilučių atnaujinimo seką, kad būtų užtikrintas sklandus vykdymas net naudojant didelius duomenų rinkinius. Taip pat pasinersime į patarimus, kaip optimizuoti SQL scenarijus, kad būtų galima efektyviai apdoroti milijonus eilučių ir užtikrinti, kad duomenų operacijos vyktų be kliūčių. Sekite naujienas, kai tyrinėjame veiksmus ir šaliname įprastas problemas!

SQL serverio komandų, skirtų lentelių keitimui ir eilučių atnaujinimui, supratimas

Dirbant su SQL serveriu, ypač su lentelėmis, kuriose yra dideli duomenų rinkiniai, labai svarbu laikytis tvarkingo požiūrio keičiant lentelę ir atnaujinant jos eilutes. Vienas dažnas scenarijus – į lentelę reikia įtraukti naują stulpelį ir atnaujinti eilutes pagal konkrečias sąlygas, pvz., nustatyti vėliavėlę pagal datas. Mano pateiktame scenarijuje parodytas paprastas požiūris į tai, tačiau jame pabrėžiamos pagrindinės SQL serverio komandos, kurios yra būtinos norint efektyviai atlikti šias užduotis. The PAKEISTI LENTELĘ komanda naudojama norint į lentelę įtraukti naują stulpelį. Pavyzdžiui, kai vykdome 'ALTER TABLE dbo.sample ADD IS_CURRENT BIT;', modifikuojame lentelės struktūrą, kad pristatytume naują stulpelį, pavadintą 'IS_CURRENT', kurio tipas yra 'BIT' (loginis tipas, 0 arba 1).

Pridėjus stulpelį, kitas žingsnis yra atnaujinti lentelės eilutes pagal tam tikras sąlygas. Tai pasiekiama naudojant ATNAUJINTI komandą. Pavyzdžiui, užklausa „UPDATE dbo.sample SET IS_CURRENT = 0 WHERE LOAD_DATE

Kai kuriais atvejais, ypač dirbant su didelėmis lentelėmis, kuriose yra milijonai eilučių, svarbu užtikrinti, kad SQL komandos būtų vykdomos efektyviai. Čia veikia kaip DATAADD ir DATEDIFF ateiti į žaidimą. Šios funkcijos leidžia tiksliai manipuliuoti ir palyginti datas. Antroje naujinimo užklausoje „DATEADD(month, DATEDIFF(month, 0, DATEADD(DAY, -60, GETDATE())), 0)“ atima 60 dienų iš dabartinės datos („GETDATE()“) ir iš naujo nustato laiko iki mėnesio pradžios. Naudodami šias funkcijas galime apibrėžti dinamiškesnes dienų sekas, kurios koreguojasi laikui bėgant, užtikrinant, kad duomenys išliktų aktualūs net ir senstant.

Tačiau kai į vieną scenarijų sujungiami teiginiai „ALTER TABLE“ ir „UPDATE“, SQL serveris kartais gali pateikti klaidą „Neteisingas stulpelio pavadinimas“. Taip nutinka todėl, kad stulpelis, pridėtas naudojant „ALTER TABLE“, gali būti nevisiškai įtrauktas arba neatpažintas SQL serverio, vykdydamas paskesnes tos pačios partijos užklausas. Šios problemos sprendimas yra atskirti teiginį „ALTER TABLE“ ir „UPDATE“ komandas, užtikrinant, kad lentelės pakeitimas būtų visiškai atliktas prieš atliekant naujinimus. Tokiu būdu SQL Server naujas stulpelis bus tinkamai užregistruotas savo schemoje, todėl lentelę bus galima sklandžiai atnaujinti. Tvarkydami didelius duomenų rinkinius, apsvarstykite galimybę šias operacijas atlikti paketais arba naudoti operacijas, kad užtikrintumėte, jog procesas būtų kuo veiksmingesnis ir išvengtumėte galimo skirtojo laiko ar blokavimo. 🚀

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

Konkrečių SQL komandų, naudojamų scenarijuose, paaiškinimas

SQL serverio scenarijų optimizavimas didelėms lentelėms

Dirbant su didelėmis SQL serverio lentelėmis, keičiant lentelės struktūrą ir atnaujinant esamas eilutes svarbu atsižvelgti į našumo optimizavimo metodus. Viena iš dažniausiai pasitaikančių problemų, su kuriomis susiduriama paleidžiant scenarijus didelėse lentelėse, yra laikas, per kurį šios operacijos atliekamos, ypač kai lentelėje yra daugiau nei milijonas eilučių. Aptariamos operacijos, pvz., stulpelio pridėjimas su PAKEISTI LENTELĘ komandą ir eilučių atnaujinimas pagal konkrečias datos sąlygas gali užtrukti daug laiko. Šių operacijų optimizavimas tampa dar svarbesnis, kai dirbate su gamybos duomenų bazėmis, kuriose našumas yra prioritetas. Vienas scenarijus gali užrakinti lentelę ilgam laikui, paveikdamas kitas užklausas ir vartotojus.

Norint sušvelninti našumo problemas, vienas geriausių būdų yra suskaidyti užduotis į mažesnius žingsnius. Pavyzdžiui, užuot pridėję stulpelį ir atnaujinę visas eilutes viename scenarijuje, apsvarstykite galimybę paleisti PAKEISTI LENTELĘ komandą atskirai, po to sugrupuokite UPDATE operacijos. Atnaujinant įrašus mažesnėmis dalimis, scenarijus neapkraus serverio. Galite pasinaudoti UPDATE TOP komanda apriboti kiekvienos operacijos paveiktų eilučių skaičių. Be to, verta kurti indeksus jūsų stulpeliuose WHERE sąlygos (pvz LOAD_DATE), kad pagreitintumėte paieškos procesą. Didelių duomenų rinkinių indeksai sumažina eilučių filtravimo pagal dienų seką laiką.

Kitas svarbus aspektas yra operacijų naudojimas ir klaidų tvarkymas, siekiant užtikrinti, kad operacijos būtų vykdomos atomiškai. Suvynioję savo UPDATE pareiškimai viduje a BEGIN TRANSACTION ir COMMIT, užtikrinate, kad pakeitimai būtų atliekami saugiai ir nuosekliai. Jei kuri nors proceso dalis nepavyksta, galite naudoti ROLLBACK atšaukti pakeitimus, užkertant kelią daliniams atnaujinimams. Be to, scenarijų vykdymas ne piko valandomis arba naudojant SQL Server Agent planuojant šias operacijas užtikrina minimalų poveikį sistemos veikimui. Naudodami šiuos optimizavimus galite saugiai atlikti sudėtingus didelių lentelių pakeitimus, išlaikant sistemos vientisumą. 🖥️