Hvordan man effektivt tilføjer en kolonne og opdaterer rækker i SQL Server

Mestring af tabelændringer i SQL Server: En trin-for-trin guide

Nogle gange kan arbejdet med store datasæt føles som at prøve at jonglere med hundrede opgaver på én gang. For nylig befandt jeg mig i en situation, hvor jeg havde brug for at tilføje en kolonne til en tabel, der indeholder over en million rækker. Selvom dette virkede som en simpel opgave på overfladen, løb jeg hurtigt ind i en vejspærring, som mange SQL Server-brugere står over for: den frygtede "Ugyldigt kolonnenavn"-fejl. 🧐

Efter at have kørt flere forsøg på at udføre mine ALTER TABLE- og UPDATE-kommandoer sammen, indså jeg, at problemet ikke lå i logikken, men i rækkefølgen af ​​mine forespørgsler. SQL Server kræver, at du tilføjer kolonnen først og foretager denne ændring, før du opdaterer den med nogen data. Undladelse af at gøre det resulterer i en fejl, fordi systemet ikke genkender den nyligt tilføjede kolonne på det tidspunkt, hvor opdateringen udføres.

Forestil dig for eksempel, at du har til opgave at opdatere flaget "IS_CURRENT" baseret på en specifik datogrænse for en stor kundedatabase. Hvis du tilføjer kolonnen og prøver at opdatere rækkerne i et enkelt script, kan SQL Server give fejlen "Ugyldigt kolonnenavn". Dette skyldes, at kolonnen ikke er fuldt commited, før opdateringsforespørgslen forsøger at bruge den. 🚀

I denne artikel gennemgår vi den korrekte rækkefølge for at tilføje kolonnen og opdatere rækkerne, hvilket sikrer jævn udførelse selv med store datasæt. Vi vil også dykke ned i tips til optimering af SQL-scripts til at håndtere millioner af rækker effektivt, hvilket sikrer, at dine dataoperationer kører uden problemer. Hold dig opdateret, mens vi udforsker trinene og fejlfinder almindelige problemer undervejs!

Forstå SQL Server-kommandoer til ændring af tabeller og opdatering af rækker

Når du arbejder med SQL Server, især med tabeller, der indeholder store datasæt, er det afgørende at følge en velordnet tilgang til at ændre en tabel og opdatere dens rækker. Et almindeligt scenarie er behov for at tilføje en ny kolonne til en tabel og derefter opdatere rækkerne baseret på specifikke forhold, som at indstille et flag baseret på datoer. Det script, jeg leverede, demonstrerer en enkel tilgang til dette, men det fremhæver vigtige SQL Server-kommandoer, der er afgørende for at opnå disse opgaver effektivt. De ÆNDRE TABEL kommandoen bruges til at tilføje en ny kolonne til tabellen. For eksempel, når vi kører `ALTER TABLE dbo.sample ADD IS_CURRENT BIT;`, ændrer vi tabelstrukturen for at introducere en ny kolonne med navnet `IS_CURRENT` af typen `BIT` (en boolesk type, enten 0 eller 1).

Efter tilføjelse af kolonnen er næste trin at opdatere rækkerne i tabellen baseret på visse betingelser. Dette opnås ved hjælp af OPDATERING kommando. For eksempel, forespørgslen `UPDATE dbo.sample SET IS_CURRENT = 0 WHERE LOAD_DATE

I nogle tilfælde, især når man har at gøre med store tabeller, der indeholder millioner af rækker, er det vigtigt at sikre, at SQL-kommandoerne udføres effektivt. Det er her funktioner som DATEADD og DATODIFF komme i spil. Disse funktioner giver dig mulighed for at manipulere og sammenligne datoer med præcision. I den anden opdateringsforespørgsel trækker `DATEADD(month, DATEDIFF(month, 0, DATEADD(DAY, -60, GETDATE())), 0)` 60 dage fra den aktuelle dato (`GETDATE()`) og nulstiller tid til starten af ​​måneden. Ved at bruge disse funktioner kan vi definere mere dynamiske datointervaller, der justeres, efterhånden som tiden skrider frem, og sikre, at dataene forbliver aktuelle, selvom de ældes.

Men når du kombinerer både `ALTER TABLE`- og `UPDATE`-sætningerne i et enkelt script, kan SQL Server nogle gange give fejlen "Ugyldigt kolonnenavn". Dette sker, fordi kolonnen tilføjet af `ALTER TABLE` muligvis ikke er fuldt ud overført eller genkendt af SQL Server under udførelsen af ​​efterfølgende forespørgsler i samme batch. Løsningen på dette problem er at adskille `ALTER TABLE`-sætningen og `UPDATE`-kommandoerne, hvilket sikrer, at tabelændringen er fuldt ud overført, før opdateringerne udføres. Ved at gøre det vil SQL Server have den nye kolonne korrekt registreret i sit skema, hvilket giver mulighed for jævne opdateringer til tabellen. Når du håndterer store datasæt, skal du overveje at udføre disse operationer i batches eller bruge transaktioner for at sikre, at processen er så effektiv som muligt og undgå potentielle timeouts eller låse. 🚀

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 af specifikke SQL-kommandoer, der bruges i scripts

Optimering af SQL Server-scripts til store tabeller

Når du arbejder med store tabeller i SQL Server, er det vigtigt at overveje præstationsoptimeringsteknikker, når du ændrer tabelstrukturen og opdaterer eksisterende rækker. Et af de mest almindelige problemer, man står over for, når man kører scripts på store tabeller, er den tid, det tager for disse operationer at fuldføre, især når en tabel indeholder over en million rækker. De pågældende operationer, såsom tilføjelse af en kolonne med ÆNDRE TABEL kommando og opdatering af rækker baseret på specifikke datoforhold, kan tage en betydelig mængde tid. Optimering af disse operationer bliver endnu vigtigere, når du arbejder på produktionsdatabaser, hvor ydeevne er en prioritet. Et enkelt script kan potentielt låse tabellen i længere perioder, hvilket påvirker andre forespørgsler og brugere.

For at afbøde præstationsproblemer er en af ​​de bedste tilgange at opdele opgaverne i mindre trin. For eksempel, i stedet for at tilføje en kolonne og opdatere alle rækker i et enkelt script, kan du overveje at køre ÆNDRE TABEL kommando separat, efterfulgt af batching af UPDATE operationer. Ved at opdatere poster i mindre bidder vil scriptet ikke overvælde serveren. Du kan udnytte UPDATE TOP kommando for at begrænse antallet af berørte rækker i hver transaktion. Derudover er det også en god idé at oprette indekser på de kolonner, der bruges i din WHERE klausuler (som f LOAD_DATE) for at fremskynde søgeprocessen. For store datasæt reducerer indekser den tid, det tager at filtrere rækker baseret på datointervaller.

En anden vigtig overvejelse er brugen af ​​transaktioner og fejlhåndtering for at sikre, at operationer udføres atomært. Ved at pakke din UPDATE udsagn inde i en BEGIN TRANSACTION og COMMIT, sikrer du, at ændringerne foretages på en sikker og konsekvent måde. Hvis en del af processen mislykkes, kan du bruge ROLLBACK for at gendanne ændringer, hvilket forhindrer delvise opdateringer. Kørsel af scripts uden for myldretiden eller brug af SQL Server Agent til at planlægge disse operationer sikrer desuden minimal indvirkning på systemets ydeevne. Med disse optimeringer kan du sikkert udføre komplekse ændringer på store tabeller, mens du bevarer systemets integritet. 🖥️