Kā efektīvi pievienot kolonnu un atjaunināt rindas SQL serverī

Tabulu modifikāciju apgūšana SQL serverī: soli pa solim

Dažreiz, strādājot ar lielām datu kopām, var šķist, ka mēģinātu vienlaikus veikt simts uzdevumus. Nesen es nokļuvu situācijā, kad man vajadzēja pievienot kolonnu tabulai, kurā ir vairāk nekā miljons rindu. Lai gan ārēji tas šķita vienkāršs uzdevums, es ātri saskāros ar šķērsli, ar kuru saskaras daudzi SQL Server lietotāji: briesmīgā kļūda "Nederīgs kolonnas nosaukums". 🧐

Pēc vairākiem mēģinājumiem izpildīt komandas ALTER TABLE un UPDATE kopā, es sapratu, ka problēma nav saistīta ar loģiku, bet gan ar manu vaicājumu secību. Lai izmantotu SQL Server, vispirms ir jāpievieno kolonna un jāveic šīs izmaiņas, pirms to atjaunināt ar jebkādiem datiem. Ja tas netiek izdarīts, rodas kļūda, jo sistēma neatpazīst tikko pievienoto kolonnu atjauninājuma izpildes laikā.

Piemēram, iedomājieties, ka jums ir uzdots atjaunināt karogu "IS_CURRENT", pamatojoties uz konkrētu datuma slieksni lielai klientu datu bāzei. Ja pievienojat kolonnu un mēģināt atjaunināt rindas vienā skriptā, SQL Server var parādīt kļūdu “Nederīgs kolonnas nosaukums”. Tas ir tāpēc, ka kolonna nav pilnībā piesaistīta, pirms atjaunināšanas vaicājums mēģina to izmantot. 🚀

Šajā rakstā mēs apskatīsim pareizo secību, lai pievienotu kolonnu un atjauninātu rindas, nodrošinot vienmērīgu izpildi pat ar lielām datu kopām. Mēs arī iedziļināsimies padomos, kā optimizēt SQL skriptus, lai efektīvi apstrādātu miljoniem rindu, nodrošinot, ka jūsu datu darbības darbojas bez aizķeršanās. Sekojiet līdzi jaunumiem, kad mēs izpētām darbības un novēršam bieži sastopamās problēmas!

Izpratne par SQL Server komandām tabulu mainīšanai un rindu atjaunināšanai

Strādājot ar SQL Server, jo īpaši ar tabulām, kurās ir lielas datu kopas, ir ļoti svarīgi ievērot sakārtotu pieeju tabulas mainīšanai un tās rindu atjaunināšanai. Viens izplatīts scenārijs ir, ka tabulai jāpievieno jauna kolonna un pēc tam jāatjaunina rindas, pamatojoties uz konkrētiem nosacījumiem, piemēram, karodziņa iestatīšana, pamatojoties uz datumiem. Manis sniegtais skripts parāda vienkāršu pieeju, taču tajā ir izceltas galvenās SQL Server komandas, kas ir būtiskas šo uzdevumu efektīvai izpildei. The MAINĪT TABLU komanda tiek izmantota, lai tabulai pievienotu jaunu kolonnu. Piemēram, palaižot 'ALTER TABLE dbo.sample ADD IS_CURRENT BIT;', mēs modificējam tabulas struktūru, lai ieviestu jaunu kolonnu ar nosaukumu 'IS_CURRENT' ar tipu 'BIT' (būla tips, vai nu 0, vai 1).

Pēc kolonnas pievienošanas nākamais solis ir atjaunināt tabulas rindas, pamatojoties uz noteiktiem nosacījumiem. Tas tiek panākts, izmantojot ATJAUNINĀT komandu. Piemēram, vaicājums “UPDATE dbo.sample SET IS_CURRENT = 0 WHERE LOAD_DATE

Dažos gadījumos, īpaši strādājot ar lielām tabulām, kurās ir miljoniem rindu, ir svarīgi nodrošināt, lai SQL komandas tiktu izpildītas efektīvi. Šeit darbojas kā DATUMS PIEVIENOT un DATEDIFF stāties spēlē. Šīs funkcijas ļauj precīzi manipulēt un salīdzināt datumus. Otrajā atjaunināšanas vaicājumā `DATEADD(month, DATEDIFF(month, 0, DATEADD(DAY, -60, GETDATE())), 0)' atņem 60 dienas no pašreizējā datuma (`GETDATE()`) un atiestata laiks līdz mēneša sākumam. Izmantojot šīs funkcijas, mēs varam definēt dinamiskākus datumu diapazonus, kas tiek pielāgoti laika gaitā, nodrošinot, ka dati paliek aktuāli pat novecojot.

Tomēr, apvienojot gan ALTER TABLE, gan UPDATE priekšrakstus vienā skriptā, SQL Server dažkārt var parādīt kļūdu “Nederīgs kolonnas nosaukums”. Tas notiek tāpēc, ka kolonna, ko pievieno ALTER TABLE, SQL Server var nebūt pilnībā piesaistīta vai atpazīta, izpildot turpmākos vaicājumus tajā pašā partijā. Šīs problēmas risinājums ir atdalīt priekšrakstu "ALTER TABLE" un komandas "UPDATE", nodrošinot, ka tabulas izmaiņas ir pilnībā veiktas pirms atjaunināšanas. Šādi rīkojoties, SQL Server jaunā kolonna tiks pareizi reģistrēta savā shēmā, ļaujot vienmērīgi atjaunināt tabulu. Apstrādājot lielas datu kopas, apsveriet šo darbību pakešu izpildi vai darījumu izmantošanu, lai nodrošinātu pēc iespējas efektīvāku procesu, izvairoties no iespējamiem noildzes vai bloķēšanas gadījumiem. 🚀

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

Skriptos izmantoto specifisko SQL komandu skaidrojums

SQL Server skriptu optimizēšana lielām tabulām

Strādājot ar lielām tabulām SQL Server, ir svarīgi ņemt vērā veiktspējas optimizācijas metodes, mainot tabulas struktūru un atjauninot esošās rindas. Viena no visbiežāk sastopamajām problēmām, ar ko saskaras, palaižot skriptus lielās tabulās, ir laiks, kas nepieciešams šo darbību pabeigšanai, it īpaši, ja tabulā ir vairāk nekā miljons rindu. Attiecīgās darbības, piemēram, kolonnas pievienošana ar MAINĪT TABLU komandu un rindu atjaunināšana, pamatojoties uz konkrētiem datuma nosacījumiem, var aizņemt daudz laika. Šo darbību optimizēšana kļūst vēl svarīgāka, ja strādājat ar ražošanas datu bāzēm, kur veiktspēja ir prioritāte. Viens skripts var potenciāli bloķēt tabulu uz ilgāku laiku, ietekmējot citus vaicājumus un lietotājus.

Lai mazinātu veiktspējas problēmas, viena no labākajām pieejām ir sadalīt uzdevumus mazākos posmos. Piemēram, tā vietā, lai pievienotu kolonnu un atjauninātu visas rindas vienā skriptā, apsveriet iespēju palaist MAINĪT TABLU komandu atsevišķi, kam seko pakešu komplektēšana UPDATE operācijas. Atjauninot ierakstus mazākās daļās, skripts neapgrūtinās serveri. Jūs varat izmantot UPDATE TOP komanda, lai ierobežotu katrā darījumā ietekmēto rindu skaitu. Turklāt ir arī ieteicams izveidot indeksus kolonnās, kuras tiek izmantotas jūsu slejā WHERE klauzulas (piemēram, LOAD_DATE), lai paātrinātu meklēšanas procesu. Lielām datu kopām indeksi samazina laiku, kas nepieciešams rindu filtrēšanai, pamatojoties uz datumu diapazoniem.

Vēl viens svarīgs apsvērums ir transakciju izmantošana un kļūdu apstrāde, lai nodrošinātu, ka darbības tiek izpildītas atomāri. Iesaiņojot savu UPDATE paziņojumi iekšpusē a BEGIN TRANSACTION un COMMIT, jūs nodrošināsiet, ka izmaiņas tiek veiktas drošā un konsekventā veidā. Ja kāda procesa daļa neizdodas, varat izmantot ROLLBACK lai atsauktu izmaiņas, novēršot daļējus atjauninājumus. Turklāt skriptu palaišana ārpus sastrēguma stundās vai SQL Server Agent izmantošana šo darbību ieplānošanai nodrošina minimālu ietekmi uz sistēmas veiktspēju. Izmantojot šīs optimizācijas, varat droši veikt sarežģītas modifikācijas lielās tabulās, vienlaikus saglabājot sistēmas integritāti. 🖥️