Hoe u effectief een kolom kunt toevoegen en rijen kunt bijwerken in SQL Server

Tabelwijzigingen beheersen in SQL Server: een stapsgewijze handleiding

Soms kan het werken met grote datasets het gevoel geven dat je honderd taken tegelijk moet uitvoeren. Onlangs bevond ik mij in een situatie waarin ik een kolom moest toevoegen aan een tabel met meer dan een miljoen rijen. Hoewel dit op het eerste gezicht een eenvoudige taak leek, kwam ik al snel een obstakel tegen waar veel SQL Server-gebruikers mee te maken krijgen: de gevreesde fout 'Ongeldige kolomnaam'. 🧐

Nadat ik verschillende pogingen had ondernomen om mijn ALTER TABLE- en UPDATE-opdrachten samen uit te voeren, besefte ik dat het probleem niet in de logica lag, maar in de volgorde van mijn vragen. SQL Server vereist dat u eerst de kolom toevoegt en die wijziging vastlegt voordat u deze bijwerkt met gegevens. Als u dit niet doet, resulteert dit in een fout omdat het systeem de nieuw toegevoegde kolom niet herkent op het moment dat de update wordt uitgevoerd.

Stel je voor dat je de taak hebt om de vlag 'IS_CURRENT' bij te werken op basis van een specifieke datumdrempel voor een grote klantendatabase. Als u de kolom toevoegt en de rijen in één script probeert bij te werken, kan SQL Server de fout 'Ongeldige kolomnaam' genereren. Dit komt omdat de kolom niet volledig is vastgelegd voordat de updatequery deze probeert te gebruiken. 🚀

In dit artikel doorlopen we de juiste volgorde voor het toevoegen van de kolom en het bijwerken van de rijen, zodat een soepele uitvoering wordt gegarandeerd, zelfs bij grote datasets. We gaan ook in op tips voor het optimaliseren van SQL-scripts om miljoenen rijen efficiënt te verwerken, zodat uw gegevensbewerkingen probleemloos verlopen. Houd ons in de gaten terwijl we de stappen verkennen en onderweg veelvoorkomende problemen oplossen!

Inzicht in de SQL Server-opdrachten voor het wijzigen van tabellen en het bijwerken van rijen

Bij het werken met SQL Server, vooral met tabellen die grote gegevenssets bevatten, is het van cruciaal belang om een ​​ordelijke aanpak te volgen bij het wijzigen van een tabel en het bijwerken van de rijen ervan. Een veelvoorkomend scenario is het toevoegen van een nieuwe kolom aan een tabel en het vervolgens bijwerken van de rijen op basis van specifieke voorwaarden, zoals het instellen van een vlag op basis van datums. Het script dat ik heb aangeleverd demonstreert een eenvoudige benadering hiervan, maar benadrukt de belangrijkste SQL Server-opdrachten die essentieel zijn voor het efficiënt uitvoeren van deze taken. De WIJZIG TABEL commando wordt gebruikt om een ​​nieuwe kolom aan de tabel toe te voegen. Als we bijvoorbeeld `ALTER TABLE dbo.sample ADD IS_CURRENT BIT;` uitvoeren, passen we de tabelstructuur aan om een ​​nieuwe kolom te introduceren met de naam `IS_CURRENT` van het type `BIT` (een booleaans type, 0 of 1).

Na het toevoegen van de kolom is de volgende stap het bijwerken van de rijen in de tabel op basis van bepaalde voorwaarden. Dit wordt bereikt met behulp van de UPDATE commando. Bijvoorbeeld de zoekopdracht `UPDATE dbo.sample SET IS_CURRENT = 0 WHERE LOAD_DATE

In sommige gevallen, vooral als het gaat om grote tabellen met miljoenen rijen, is het belangrijk ervoor te zorgen dat de SQL-opdrachten efficiënt worden uitgevoerd. Dit is waar functies zoals DATUM TOEVOEGEN En DATUMVERSCH komen in het spel. Met deze functies kunt u datums nauwkeurig manipuleren en vergelijken. In de tweede bijwerkquery trekt `DATEADD(maand, DATEDIFF(maand, 0, DATEADD(DAY, -60, GETDATE())), 0)` 60 dagen af ​​van de huidige datum (`GETDATE()`) en wordt de datum opnieuw ingesteld. tijd tot het begin van de maand. Door deze functies te gebruiken, kunnen we dynamischere datumbereiken definiëren die zich aanpassen naarmate de tijd vordert, zodat de gegevens actueel blijven, zelfs als ze ouder worden.

Wanneer u echter de instructies `ALTER TABLE` en `UPDATE` combineert in één script, kan SQL Server soms de fout 'Ongeldige kolomnaam' genereren. Dit gebeurt omdat de kolom die is toegevoegd door `ALTER TABLE` mogelijk niet volledig wordt vastgelegd of herkend door SQL Server tijdens de uitvoering van volgende query's in dezelfde batch. De oplossing voor dit probleem is het scheiden van de `ALTER TABLE`-instructie en de `UPDATE`-opdrachten, waarbij u ervoor zorgt dat de tabelwijziging volledig wordt doorgevoerd voordat de updates worden uitgevoerd. Door dit te doen, zorgt SQL Server ervoor dat de nieuwe kolom correct wordt geregistreerd in het schema, waardoor soepele updates van de tabel mogelijk zijn. Wanneer u grote datasets verwerkt, kunt u overwegen deze bewerkingen in batches uit te voeren of transacties te gebruiken om ervoor te zorgen dat het proces zo efficiënt mogelijk is en mogelijke time-outs of vergrendelingen worden vermeden. 🚀

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

Uitleg van specifieke SQL-opdrachten die in de scripts worden gebruikt

Optimalisatie van SQL Server-scripts voor grote tabellen

Wanneer u met grote tabellen in SQL Server werkt, is het belangrijk om technieken voor prestatieoptimalisatie te overwegen bij het wijzigen van de tabelstructuur en het bijwerken van bestaande rijen. Een van de meest voorkomende problemen bij het uitvoeren van scripts op grote tabellen is de tijd die nodig is om deze bewerkingen te voltooien, vooral wanneer een tabel meer dan een miljoen rijen bevat. De betreffende bewerkingen, zoals het toevoegen van een kolom met de WIJZIG TABEL Het uitvoeren van opdrachten en het bijwerken van rijen op basis van specifieke datumvoorwaarden kan een aanzienlijke hoeveelheid tijd in beslag nemen. Het optimaliseren van deze bewerkingen wordt nog belangrijker als u werkt aan productiedatabases waarbij prestaties prioriteit hebben. Eén enkel script kan de tabel mogelijk voor langere perioden vergrendelen, wat gevolgen heeft voor andere query's en gebruikers.

Om prestatieproblemen te verminderen, is een van de beste benaderingen het opsplitsen van de taken in kleinere stappen. In plaats van bijvoorbeeld een kolom toe te voegen en alle rijen in één script bij te werken, kunt u overwegen om de WIJZIG TABEL commando afzonderlijk, gevolgd door het batchen van de UPDATE operaties. Door records in kleinere delen bij te werken, zal het script de server niet overbelasten. U kunt gebruik maken van de UPDATE TOP commando om het aantal betrokken rijen in elke transactie te beperken. Bovendien is het ook een goed idee om indexen te maken voor de kolommen die in uw bestand worden gebruikt WHERE clausules (zoals LOAD_DATE) om het zoekproces te versnellen. Voor grote gegevenssets verkorten indexen de tijd die nodig is om rijen te filteren op basis van datumbereiken.

Een andere belangrijke overweging is het gebruik van transacties en foutafhandeling om ervoor te zorgen dat bewerkingen atomair worden uitgevoerd. Door het inpakken van uw UPDATE uitspraken binnen een BEGIN TRANSACTION En COMMITzorg jij ervoor dat de wijzigingen op een veilige en consistente manier worden doorgevoerd. Als een deel van het proces mislukt, kunt u dit gebruiken ROLLBACK om wijzigingen ongedaan te maken, waardoor gedeeltelijke updates worden voorkomen. Bovendien zorgt het uitvoeren van scripts tijdens daluren of het gebruik van SQL Server Agent om deze bewerkingen te plannen voor een minimale impact op de systeemprestaties. Met deze optimalisaties kunt u veilig complexe wijzigingen uitvoeren op grote tabellen, terwijl de systeemintegriteit behouden blijft. 🖥️