Hvordan du effektivt legger til en kolonne og oppdaterer rader i SQL Server

Mestring av tabellendringer i SQL Server: En trinn-for-trinn-veiledning

Noen ganger kan det å jobbe med store datasett føles som å prøve å sjonglere hundre oppgaver samtidig. Nylig befant jeg meg i en situasjon der jeg trengte å legge til en kolonne i en tabell som inneholder over en million rader. Selv om dette virket som en enkel oppgave på overflaten, løp jeg raskt inn i en veisperring som mange SQL Server-brukere står overfor: den fryktede feilen "Ugyldig kolonnenavn". 🧐

Etter å ha kjørt flere forsøk på å utføre ALTER TABLE- og UPDATE-kommandoene mine sammen, innså jeg at problemet ikke var logikken, men sekvensen av spørringene mine. SQL Server krever at du legger til kolonnen først og foretar endringen før du oppdaterer den med data. Unnlatelse av å gjøre dette resulterer i en feil fordi systemet ikke gjenkjenner den nylig lagt til kolonnen på tidspunktet oppdateringen utføres.

Tenk deg for eksempel at du har i oppgave å oppdatere "IS_CURRENT"-flagget basert på en bestemt datoterskel for en stor kundedatabase. Hvis du legger til kolonnen og prøver å oppdatere radene i et enkelt skript, kan SQL Server gi feilmeldingen "Ugyldig kolonnenavn". Dette er fordi kolonnen ikke er fullstendig forpliktet før oppdateringsspørringen prøver å bruke den. 🚀

I denne artikkelen går vi gjennom riktig rekkefølge for å legge til kolonnen og oppdatere radene, noe som sikrer jevn utførelse selv med store datasett. Vi vil også dykke ned i tips for å optimalisere SQL-skript for å håndtere millioner av rader effektivt, og sikre at dataoperasjonene dine går uten problemer. Følg med mens vi utforsker trinnene og feilsøker vanlige problemer underveis!

Forstå SQL Server-kommandoer for endring av tabeller og oppdatering av rader

Når du arbeider med SQL Server, spesielt med tabeller som inneholder store datasett, er det avgjørende å følge en ryddig tilnærming for å endre en tabell og oppdatere dens rader. Et vanlig scenario er å måtte legge til en ny kolonne i en tabell og deretter oppdatere radene basert på spesifikke forhold, som å sette et flagg basert på datoer. Skriptet jeg ga demonstrerer en enkel tilnærming til dette, men det fremhever viktige SQL Server-kommandoer som er avgjørende for å oppnå disse oppgavene effektivt. De ENDRE TABELL kommandoen brukes til å legge til en ny kolonne i tabellen. For eksempel, når vi kjører `ALTER TABLE dbo.sample ADD IS_CURRENT BIT;`, endrer vi tabellstrukturen for å introdusere en ny kolonne kalt `IS_CURRENT` av typen `BIT` (en boolsk type, enten 0 eller 1).

Etter å ha lagt til kolonnen, er neste trinn å oppdatere radene i tabellen basert på visse forhold. Dette oppnås ved hjelp av OPPDATERING kommando. For eksempel, spørringen `UPDATE dbo.sample SET IS_CURRENT = 0 WHERE LOAD_DATE

I noen tilfeller, spesielt når du arbeider med store tabeller som inneholder millioner av rader, er det viktig å sikre at SQL-kommandoene utføres effektivt. Det er her funksjoner som DATEADD og DATODIFF komme i spill. Disse funksjonene lar deg manipulere og sammenligne datoer med presisjon. I den andre oppdateringsspørringen trekker `DATEADD(month, DATEDIFF(month, 0, DATEADD(DAY, -60, GETDATE())), 0)` 60 dager fra gjeldende dato (`GETDATE()`) og tilbakestiller tid til begynnelsen av måneden. Ved å bruke disse funksjonene kan vi definere mer dynamiske datoperioder som justeres etter hvert som tiden skrider frem, og sikre at dataene forblir oppdaterte selv når de eldes.

Men når du kombinerer både `ALTER TABLE`- og `UPDATE`-setningene til ett enkelt skript, kan SQL Server noen ganger gi feilen "Ugyldig kolonnenavn". Dette skjer fordi kolonnen som er lagt til av `ALTER TABLE` kanskje ikke er fullstendig overført eller gjenkjent av SQL Server under kjøringen av påfølgende spørringer i samme batch. Løsningen på dette problemet er å skille `ALTER TABLE`-setningen og `UPDATE`-kommandoene, og sikre at tabellendringen er fullstendig committed før du utfører oppdateringene. Ved å gjøre det vil SQL Server ha den nye kolonnen riktig registrert i skjemaet, noe som gir jevne oppdateringer til tabellen. Når du håndterer store datasett, bør du vurdere å utføre disse operasjonene i batcher eller bruke transaksjoner for å sikre at prosessen er så effektiv som mulig, og unngå potensielle tidsavbrudd eller låser. 🚀

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

Forklaring av spesifikke SQL-kommandoer som brukes i skriptene

Optimalisering av SQL Server-skript for store tabeller

Når du arbeider med store tabeller i SQL Server, er det viktig å vurdere ytelsesoptimaliseringsteknikker når du endrer tabellstrukturen og oppdaterer eksisterende rader. Et av de vanligste problemene man møter når man kjører skript på store tabeller, er tiden det tar for disse operasjonene å fullføre, spesielt når en tabell inneholder over en million rader. De aktuelle operasjonene, for eksempel å legge til en kolonne med ENDRE TABELL kommando og oppdatering av rader basert på spesifikke datoforhold, kan ta betydelig tid. Å optimalisere disse operasjonene blir enda viktigere når du jobber med produksjonsdatabaser hvor ytelse er en prioritet. Et enkelt skript kan potensielt låse tabellen i lengre perioder, og påvirke andre spørringer og brukere.

For å redusere ytelsesproblemer er en av de beste tilnærmingene å dele opp oppgavene i mindre trinn. For eksempel, i stedet for å legge til en kolonne og oppdatere alle rader i et enkelt skript, bør du vurdere å kjøre ENDRE TABELL kommandoen separat, etterfulgt av batching av UPDATE operasjoner. Ved å oppdatere poster i mindre biter, vil ikke skriptet overvelde serveren. Du kan utnytte UPDATE TOP kommando for å begrense antall berørte rader i hver transaksjon. I tillegg er det også en god idé å lage indekser på kolonnene som brukes i din WHERE klausuler (som f.eks LOAD_DATE) for å fremskynde søkeprosessen. For store datasett reduserer indekser tiden det tar å filtrere rader basert på datoperioder.

En annen viktig faktor er bruken av transaksjoner og feilhåndtering for å sikre at operasjoner utføres atomært. Ved å pakke inn din UPDATE uttalelser i en BEGIN TRANSACTION og COMMIT, sørger du for at endringene gjøres på en sikker og konsekvent måte. Hvis noen del av prosessen mislykkes, kan du bruke ROLLBACK for å tilbakestille endringer, forhindre delvise oppdateringer. I tillegg sørger det for minimal innvirkning på systemytelsen å kjøre skript i rushtiden eller bruke SQL Server Agent for å planlegge disse operasjonene. Med disse optimaliseringene kan du trygt utføre komplekse modifikasjoner på store tabeller mens du opprettholder systemintegriteten. 🖥️