Kuinka lisätä tehokkaasti sarake ja päivittää rivejä SQL Serverissä

Taulukon muutosten hallitseminen SQL Serverissä: Vaiheittainen opas

Joskus suurten tietojoukkojen kanssa työskentely voi tuntua siltä, ​​että yrittäisit käsitellä sataa tehtävää kerralla. Äskettäin jouduin tilanteeseen, jossa minun piti lisätä sarake taulukkoon, joka sisältää yli miljoona riviä. Vaikka tämä vaikutti pinnalta yksinkertaiselta tehtävältä, törmäsin nopeasti monien SQL Server -käyttäjien kohtaamaan estoon: pelättyyn "Virheellinen sarakkeen nimi" -virhe. 🧐

Suoritettuani useita yrityksiä suorittaa ALTER TABLE- ja UPDATE-komennot yhdessä, tajusin, että ongelma ei johtunut logiikasta vaan kyselyjeni järjestyksestä. SQL Server edellyttää, että lisäät sarakkeen ensin ja vahvistat tämän muutoksen ennen kuin päivität sen tiedoilla. Jos näin ei tehdä, seurauksena on virhe, koska järjestelmä ei tunnista juuri lisättyä saraketta päivityksen suorittamishetkellä.

Kuvittele esimerkiksi, että sinun tehtäväsi on päivittää "IS_CURRENT" -lippu suuren asiakastietokannan tietyn päivämääräkynnyksen perusteella. Jos lisäät sarakkeen ja yrität päivittää rivit yhdessä komentosarjassa, SQL Server saattaa antaa virheellisen sarakkeen nimi -virheen. Tämä johtuu siitä, että saraketta ei ole täysin sitoutunut ennen kuin päivityskysely yrittää käyttää sitä. 🚀

Tässä artikkelissa käymme läpi oikean järjestyksen sarakkeen lisäämiseksi ja rivien päivittämiseksi, mikä varmistaa sujuvan suorituskyvyn myös suurilla tietojoukoilla. Sukellaan myös vinkkeihin SQL-skriptien optimoimiseksi käsittelemään miljoonia rivejä tehokkaasti ja varmistamaan, että tietotoiminnot toimivat ongelmitta. Pysy kuulolla, kun tutkimme vaiheita ja etsimme yleisiä ongelmia matkan varrella!

SQL Server -komentojen ymmärtäminen taulukoiden muuttamiseen ja rivien päivittämiseen

Kun työskentelet SQL Serverin kanssa, erityisesti suuria tietojoukkoja sisältävien taulukoiden kanssa, on tärkeää noudattaa säännöllistä lähestymistapaa taulukon muuttamisessa ja sen rivien päivittämisessä. Yksi yleinen skenaario on lisätä uusi sarake taulukkoon ja päivittää sitten rivit tiettyjen ehtojen mukaan, kuten lipun asettaminen päivämäärien perusteella. Toimittamani skripti osoittaa yksinkertaisen lähestymistavan tähän, mutta se korostaa tärkeimmät SQL Server -komennot, jotka ovat välttämättömiä näiden tehtävien suorittamiseksi tehokkaasti. The MUUTTA TAULU -komentoa käytetään uuden sarakkeen lisäämiseen taulukkoon. Esimerkiksi kun suoritamme komennon "ALTER TABLE dbo.sample ADD IS_CURRENT BIT;", muokkaamme taulukkorakennetta lisäämään uuden sarakkeen nimeltä "IS_CURRENT", jonka tyyppi on "BIT" (looginen tyyppi, joko 0 tai 1).

Sarakkeen lisäämisen jälkeen seuraava vaihe on päivittää taulukon rivit tiettyjen ehtojen mukaan. Tämä saavutetaan käyttämällä PÄIVITTÄÄ komento. Esimerkiksi kysely `UPDATE dbo.sample SET IS_CURRENT = 0 WHERE LOAD_DATE

Joissakin tapauksissa, varsinkin kun käsitellään suuria, miljoonia rivejä sisältäviä taulukoita, on tärkeää varmistaa, että SQL-komennot suoritetaan tehokkaasti. Tässä toimii kuten PÄIVÄMÄÄRÄ ja DATEDIFF tulla peliin. Näiden toimintojen avulla voit käsitellä ja vertailla päivämääriä tarkasti. Toisessa päivityskyselyssä `DATEADD(month, DATEDIFF(kuukausi, 0, DATEADD(DAY, -60, GETDATE())), 0)' vähentää 60 päivää nykyisestä päivämäärästä (`GETDATE()`) ja nollaa aika kuun alkuun. Näiden toimintojen avulla voimme määrittää dynaamisempia ajanjaksoja, jotka mukautuvat ajan edetessä ja varmistavat, että tiedot pysyvät ajan tasalla myös ikääntyessään.

Kuitenkin, kun yhdistät sekä ALTER TABLE- että UPDATE-käskyt yhdeksi skriptiksi, SQL Server voi joskus antaa virheellisen sarakkeen nimi -virheen. Tämä johtuu siitä, että SQL Server ei välttämättä ole täysin sitoutunut tai tunnistanut ALTER TABLE:n lisäämää saraketta suorittaessaan myöhempiä kyselyitä samassa erässä. Ratkaisu tähän ongelmaan on erottaa ALTER TABLE -käsky ja UPDATE-komennot ja varmistaa, että taulukon muutos on täysin sitoutunut ennen päivitysten suorittamista. Näin tekemällä SQL Server rekisteröi uuden sarakkeen oikein skeemossaan, mikä mahdollistaa taulukon sujuvat päivitykset. Kun käsittelet suuria tietojoukkoja, harkitse näiden toimintojen suorittamista erissä tai tapahtumien käyttöä varmistaaksesi, että prosessi on mahdollisimman tehokas ja vältä mahdolliset aikakatkaisut tai lukitukset. 🚀

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

Komentosarjoissa käytettyjen erityisten SQL-komentojen selitys

SQL Server -komentosarjojen optimointi suurille taulukoille

Kun työskentelet suurten taulukoiden kanssa SQL Serverissä, on tärkeää ottaa huomioon suorituskyvyn optimointitekniikat, kun taulukkorakennetta muutetaan ja olemassa olevia rivejä päivitetään. Yksi yleisimmistä ongelmista suoritettaessa skriptejä suurissa taulukoissa on näiden toimintojen suorittamiseen kuluva aika, varsinkin kun taulukko sisältää yli miljoona riviä. Kyseiset toiminnot, kuten sarakkeen lisääminen MUUTTA TAULU komento ja rivien päivittäminen tiettyjen päivämääräehtojen perusteella, voi viedä huomattavasti aikaa. Näiden toimintojen optimoinnista tulee entistä tärkeämpää, kun työskentelet tuotantotietokannassa, jossa suorituskyky on etusijalla. Yksi komentosarja voi mahdollisesti lukita taulukon pitkäksi aikaa, mikä vaikuttaa muihin kyselyihin ja käyttäjiin.

Suorituskykyongelmien lieventämiseksi yksi parhaista tavoista on jakaa tehtävät pienempiin vaiheisiin. Esimerkiksi sen sijaan, että lisäisit sarakkeen ja päivittäisit kaikki rivit yhdessä skriptissä, harkitse ohjelman suorittamista MUUTTA TAULU -komento erikseen, jonka jälkeen jaksotetaan UPDATE toiminnot. Päivittämällä tietueita pienemmissä osissa komentosarja ei kuormita palvelinta. Voit hyödyntää UPDATE TOP komento rajoittaa kunkin tapahtuman vaikutusten rivien määrää. Lisäksi on myös hyvä idea luoda indeksejä käyttämissäsi sarakkeissa WHERE lausekkeet (esim LOAD_DATE) nopeuttaaksesi hakuprosessia. Suurille tietojoukoille hakemistot vähentävät aikaa, joka kuluu rivien suodattamiseen ajanjakson perusteella.

Toinen tärkeä näkökohta on tapahtumien ja virheiden käsittelyn käyttö sen varmistamiseksi, että toiminnot suoritetaan atomaarisesti. Käärimällä omasi UPDATE lausunnot sisällä a BEGIN TRANSACTION ja COMMIT, varmistat, että muutokset tehdään turvallisesti ja johdonmukaisesti. Jos jokin prosessin osa epäonnistuu, voit käyttää ROLLBACK peruuttaa muutokset ja estää osittaiset päivitykset. Lisäksi komentosarjojen suorittaminen ruuhka-aikoina tai SQL Server Agentin käyttäminen näiden toimintojen ajoittamiseen varmistaa minimaalisen vaikutuksen järjestelmän suorituskykyyn. Näiden optimointien avulla voit turvallisesti suorittaa monimutkaisia ​​muutoksia suurissa taulukoissa säilyttäen samalla järjestelmän eheyden. 🖥️