Kuidas SQL Serveris tõhusalt veergu lisada ja ridu värskendada

Tabelite muudatuste valdamine SQL Serveris: samm-sammult juhend

Mõnikord võib suurte andmekogumitega töötamine tunduda nagu prooviks žongleerida saja ülesandega korraga. Hiljuti leidsin end olukorrast, kus mul oli vaja lisada veerg tabelisse, mis sisaldab üle miljoni rea. Kuigi see tundus pealtnäha lihtsa ülesandena, sattusin kiiresti teetõkkesse, millega paljud SQL Serveri kasutajad silmitsi seisavad: kardetud veaga „Veeru kehtetu nimi”. 🧐

Pärast mitut katset käivitada käske ALTER TABLE ja UPDATE, mõistsin, et probleem ei olnud mitte loogikas, vaid minu päringute järjestuses. SQL Server nõuab, et lisate kõigepealt veeru ja kinnitate selle muudatuse enne selle värskendamist mis tahes andmetega. Kui seda ei tehta, tekib tõrge, kuna süsteem ei tunne värskenduse käivitamise ajal äsja lisatud veergu.

Kujutage näiteks ette, et teie ülesandeks on värskendada lipu "IS_CURRENT" konkreetse kuupäevaläve alusel suure kliendiandmebaasi jaoks. Kui lisate veeru ja proovite värskendada ridu ühes skriptis, võib SQL Server kuvada veateate "Veeru kehtetu nimi". Selle põhjuseks on asjaolu, et veerg ei ole täielikult kinnitatud enne, kui värskenduspäring proovib seda kasutada. 🚀

Selles artiklis käsitleme veeru lisamise ja ridade värskendamise õiget järjestust, tagades sujuva täitmise isegi suurte andmekogumite korral. Samuti käsitleme näpunäiteid SQL-skriptide optimeerimiseks, et hallata tõhusalt miljoneid ridu, tagades teie andmetoimingute tõrgeteta toimimise. Olge kursis, kui uurime samme ja otsime levinumaid probleeme!

SQL Serveri käskude mõistmine tabelite muutmiseks ja ridade värskendamiseks

SQL Serveriga töötamisel, eriti suuri andmekogumeid sisaldavate tabelitega, on ülioluline järgida korrapärast lähenemist tabeli muutmisel ja selle ridade värskendamisel. Üks levinud stsenaarium on see, et tabelisse tuleb lisada uus veerg ja seejärel värskendada ridu konkreetsete tingimuste alusel, näiteks kuupäevade alusel lipu määramine. Minu esitatud skript demonstreerib lihtsat lähenemist sellele, kuid toob esile peamised SQL Serveri käsud, mis on nende ülesannete tõhusaks täitmiseks hädavajalikud. The ALTER TABLE käsku kasutatakse tabelisse uue veeru lisamiseks. Näiteks kui käivitame käsu „ALTER TABLE dbo.sample ADD IS_CURRENT BIT;”, muudame tabeli struktuuri, et tutvustada uut veergu nimega „IS_CURRENT”, mille tüüp on „BIT” (tõve tüüp, kas 0 või 1).

Pärast veeru lisamist tuleb järgmise sammuna teatud tingimustel tabeli ridu värskendada. See saavutatakse kasutades VÄRSKENDAMINE käsk. Näiteks päring `UPDATE dbo.sample SET IS_CURRENT = 0 WHERE LOAD_DATE

Mõnel juhul, eriti kui tegemist on suurte tabelitega, mis sisaldavad miljoneid ridu, on oluline tagada SQL-käskude tõhus täitmine. See on koht, kus funktsioonid nagu KUUPÄEV LISA ja DATEDIFF mängu tulla. Need funktsioonid võimaldavad teil kuupäevi täpselt manipuleerida ja võrrelda. Teises värskenduspäringus lahutab `DATEADD(month, DATEDIFF(month, 0, DATEADD(DAY, -60, GETDATE())), 0)' praegusest kuupäevast (`GETDATE()) 60 päeva ja lähtestab aega kuu alguseni. Neid funktsioone kasutades saame määratleda dünaamilisemad kuupäevavahemikud, mis aja edenedes kohanduvad, tagades andmete ajakohasuse isegi vananedes.

Kui aga kombineerida nii ALTER TABLE kui ka UPDATE laused üheks skriptiks, võib SQL Server mõnikord esitada veateate "Veeru kehtetu nimi". Selle põhjuseks on asjaolu, et SQL Server ei pruugi sama partii järgmiste päringute täitmisel veergu ALTER TABLE lisatud veergu täielikult siduda ega tuvastada. Selle probleemi lahenduseks on eraldada lause ALTER TABLE ja käsud UPDATE, tagades, et tabeli muutmine on enne värskenduste tegemist täielikult vastu võetud. Seda tehes registreerib SQL Server uue veeru oma skeemis korralikult, mis võimaldab tabelit sujuvalt värskendada. Suurte andmehulkade käsitlemisel kaaluge nende toimingute sooritamist partiidena või tehingute kasutamist, et tagada protsessi võimalikult tõhus toimimine, vältides võimalikke ajalõppusid või lukke. 🚀

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

Skriptides kasutatavate spetsiifiliste SQL-käskude selgitus

SQL Serveri skriptide optimeerimine suurte tabelite jaoks

SQL Serveris suurte tabelitega töötades on tabeli struktuuri muutmisel ja olemasolevate ridade värskendamisel oluline arvestada jõudluse optimeerimise tehnikatega. Üks levinumaid probleeme suurtes tabelites skriptide käitamisel on nende toimingute lõpuleviimiseks kuluv aeg, eriti kui tabel sisaldab üle miljoni rea. Kõnealused toimingud, näiteks veeru lisamine ALTER TABLE käsk ja ridade värskendamine konkreetsete kuupäevatingimuste alusel võib võtta palju aega. Nende toimingute optimeerimine muutub veelgi olulisemaks, kui töötate tootmisandmebaasidega, kus jõudlus on prioriteet. Üks skript võib tabeli pikemaks ajaks lukustada, mõjutades teisi päringuid ja kasutajaid.

Jõudlusprobleemide leevendamiseks on üks parimaid lähenemisviise ülesannete jaotamine väiksemateks sammudeks. Näiteks ühe skripti veeru lisamise ja kõigi ridade värskendamise asemel kaaluge rakenduse käivitamist ALTER TABLE käsk eraldi, millele järgneb pakkimine UPDATE operatsioonid. Värskendades kirjeid väiksemate tükkidena, ei koorma skript serverit üle. Saate võimendada UPDATE TOP käsk, et piirata iga tehingu puhul mõjutatud ridade arvu. Lisaks on hea mõte luua ka oma veergudele indeksid WHERE klauslid (nagu LOAD_DATE) otsinguprotsessi kiirendamiseks. Suurte andmekogumite puhul vähendavad indeksid kuupäevavahemike alusel ridade filtreerimiseks kuluvat aega.

Teine oluline kaalutlus on tehingute kasutamine ja vigade käsitlemine, et tagada toimingute aatomiline täitmine. Pakkides oma UPDATE avaldused a sees BEGIN TRANSACTION ja COMMIT, tagate muudatuste tegemise ohutul ja järjepideval viisil. Kui mõni protsessi osa ebaõnnestub, saate seda kasutada ROLLBACK muudatuste tagasivõtmiseks, vältides osalisi värskendusi. Lisaks tagab skriptide käitamine väljaspool tipptundi või SQL Server Agenti kasutamine nende toimingute ajastamiseks minimaalse mõju süsteemi jõudlusele. Nende optimeerimiste abil saate turvaliselt teostada keerulisi muudatusi suurtes tabelites, säilitades samal ajal süsteemi terviklikkuse. 🖥️