Hur man effektivt lägger till en kolumn och uppdaterar rader i SQL Server

Bemästra tabelländringar i SQL Server: En steg-för-steg-guide

Ibland kan det kännas som att arbeta med stora datamängder som att försöka jonglera med hundra uppgifter samtidigt. Nyligen hamnade jag i en situation där jag behövde lägga till en kolumn i en tabell som innehåller över en miljon rader. Även om detta verkade vara en enkel uppgift på ytan, stötte jag snabbt på en vägspärr som många SQL Server-användare står inför: det fruktade felet "Ogiltigt kolumnnamn". 🧐

Efter att ha kört flera försök att köra mina ALTER TABLE- och UPDATE-kommandon tillsammans, insåg jag att problemet inte låg i logiken utan i sekvensen av mina frågor. SQL Server kräver att du lägger till kolumnen först och genomför den ändringen innan du uppdaterar den med data. Om du inte gör det resulterar det i ett fel eftersom systemet inte känner igen den nyligen tillagda kolumnen när uppdateringen körs.

Föreställ dig till exempel att du har i uppdrag att uppdatera flaggan "IS_CURRENT" baserat på ett specifikt datumtröskelvärde för en stor kunddatabas. Om du lägger till kolumnen och försöker uppdatera raderna i ett enda skript, kan SQL Server ge felet "Ogiltigt kolumnnamn". Detta beror på att kolumnen inte är helt commited innan uppdateringsfrågan försöker använda den. 🚀

I den här artikeln går vi igenom rätt sekvens för att lägga till kolumnen och uppdatera raderna, vilket säkerställer smidigt utförande även med stora datamängder. Vi kommer också att dyka ner i tips för att optimera SQL-skript för att hantera miljontals rader effektivt, vilket säkerställer att dina dataoperationer fungerar utan problem. Håll ögonen öppna när vi utforskar stegen och felsöker vanliga problem på vägen!

Förstå SQL Server-kommandon för att ändra tabeller och uppdatera rader

När du arbetar med SQL Server, särskilt med tabeller som innehåller stora datamängder, är det avgörande att följa ett ordnat tillvägagångssätt för att ändra en tabell och uppdatera dess rader. Ett vanligt scenario är att behöva lägga till en ny kolumn i en tabell och sedan uppdatera raderna baserat på specifika förhållanden, som att sätta en flagga baserat på datum. Skriptet jag tillhandahöll visar ett enkelt tillvägagångssätt till detta, men det belyser viktiga SQL Server-kommandon som är viktiga för att utföra dessa uppgifter effektivt. De ÄNDRA TABELL kommandot används för att lägga till en ny kolumn i tabellen. Till exempel, när vi kör `ALTER TABLE dbo.sample ADD IS_CURRENT BIT;`, modifierar vi tabellstrukturen för att introducera en ny kolumn med namnet `IS_CURRENT` av typen `BIT` (en boolesk typ, antingen 0 eller 1).

Efter att ha lagt till kolumnen är nästa steg att uppdatera raderna i tabellen baserat på vissa villkor. Detta uppnås med hjälp av UPPDATERA kommando. Till exempel, frågan `UPPDATERA dbo.sample SET IS_CURRENT = 0 WHERE LOAD_DATE

I vissa fall, särskilt när man hanterar stora tabeller som innehåller miljontals rader, är det viktigt att se till att SQL-kommandona exekveras effektivt. Det är här funktioner som DATEADD och DATUMDIFF komma till spel. Dessa funktioner låter dig manipulera och jämföra datum med precision. I den andra uppdateringsfrågan subtraherar `DATEADD(month, DATEDIFF(month, 0, DATEADD(DAY, -60, GETDATE())), 0)` 60 dagar från det aktuella datumet (`GETDATE()`) och återställer tid till början av månaden. Genom att använda dessa funktioner kan vi definiera mer dynamiska datumintervall som justeras allt eftersom tiden fortskrider, vilket säkerställer att informationen förblir aktuell även när den åldras.

Men när man kombinerar både `ALTER TABLE`- och `UPDATE`-satserna till ett enda skript, kan SQL Server ibland skicka felet "Ogiltigt kolumnnamn". Detta beror på att kolumnen som läggs till av `ALTER TABLE` kanske inte är fullständigt commited eller igenkänd av SQL Server under körningen av efterföljande frågor i samma batch. Lösningen på detta problem är att separera `ALTER TABLE`-satsen och `UPDATE`-kommandona, vilket säkerställer att tabelländringen är fullständigt commited innan uppdateringarna utförs. Genom att göra det kommer SQL Server att ha den nya kolumnen korrekt registrerad i sitt schema, vilket möjliggör smidiga uppdateringar av tabellen. När du hanterar stora datamängder, överväg att utföra dessa operationer i omgångar eller använda transaktioner för att säkerställa att processen är så effektiv som möjligt och undviker potentiella tidsgränser eller låsningar. 🚀

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

Förklaring av specifika SQL-kommandon som används i skripten

Optimera SQL Server-skript för stora tabeller

När du arbetar med stora tabeller i SQL Server är det viktigt att överväga prestandaoptimeringstekniker när du ändrar tabellstrukturen och uppdaterar befintliga rader. Ett av de vanligaste problemen man möter när man kör skript på stora tabeller är den tid det tar för dessa operationer att slutföras, speciellt när en tabell innehåller över en miljon rader. Operationerna i fråga, som att lägga till en kolumn med ÄNDRA TABELL kommando och uppdatering av rader baserat på specifika datumförhållanden kan ta en betydande tid. Att optimera dessa operationer blir ännu viktigare när du arbetar med produktionsdatabaser där prestanda är en prioritet. Ett enda skript kan potentiellt låsa tabellen under längre perioder, vilket påverkar andra frågor och användare.

För att mildra prestationsproblem är en av de bästa metoderna att dela upp uppgifterna i mindre steg. Till exempel, istället för att lägga till en kolumn och uppdatera alla rader i ett enda skript, överväg att köra ÄNDRA TABELL kommandot separat, följt av batchning av UPDATE operationer. Genom att uppdatera poster i mindre bitar kommer skriptet inte att överväldiga servern. Du kan utnyttja UPDATE TOP kommando för att begränsa antalet rader som påverkas i varje transaktion. Dessutom är det också en bra idé att skapa index på kolumnerna som används i din WHERE klausuler (som LOAD_DATE) för att påskynda sökprocessen. För stora datamängder minskar index den tid det tar att filtrera rader baserat på datumintervall.

En annan viktig faktor är användningen av transaktioner och felhantering för att säkerställa att operationer utförs atomärt. Genom att slå in din UPDATE uttalanden inuti en BEGIN TRANSACTION och COMMIT, ser du till att ändringarna görs på ett säkert och konsekvent sätt. Om någon del av processen misslyckas kan du använda ROLLBACK för att återställa ändringar, förhindra partiella uppdateringar. Att köra skript under lågtrafik eller använda SQL Server Agent för att schemalägga dessa operationer säkerställer dessutom minimal påverkan på systemets prestanda. Med dessa optimeringar kan du säkert utföra komplexa ändringar på stora tabeller samtidigt som systemets integritet bibehålls. 🖥️