Com afegir eficaçment una columna i actualitzar files a SQL Server

Dominar les modificacions de la taula a SQL Server: una guia pas a pas

De vegades, treballar amb grans conjunts de dades pot tenir la sensació d'intentar fer malabars amb cent tasques alhora. Recentment, em vaig trobar en una situació en què necessitava afegir una columna a una taula que conté més d'un milió de files. Tot i que semblava una tasca senzilla a la superfície, ràpidament em vaig trobar amb un obstacle que molts usuaris de SQL Server s'enfronten: el temut error "Nom de columna no vàlid". 🧐

Després d'executar diversos intents d'executar les meves ordres ALTER TABLE i UPDATE junts, em vaig adonar que el problema no era amb la lògica sinó amb la seqüència de les meves consultes. SQL Server requereix que afegiu primer la columna i compromeu el canvi abans d'actualitzar-lo amb qualsevol dada. Si no ho feu, es produirà un error perquè el sistema no reconeix la columna recentment afegida en el moment en què s'executa l'actualització.

Per exemple, imagineu-vos que teniu l'encàrrec d'actualitzar la marca "IS_CURRENT" en funció d'un llindar de data específic per a una gran base de dades de clients. Si afegiu la columna i intenteu actualitzar les files en un sol script, SQL Server podria generar un error "Nom de columna no vàlid". Això es deu al fet que la columna no està completament compromesa abans que la consulta d'actualització intenti utilitzar-la. 🚀

En aquest article, repassarem la seqüència adequada per afegir la columna i actualitzar les files, garantint una execució fluida fins i tot amb grans conjunts de dades. També aprofundirem en consells per optimitzar els scripts SQL per gestionar milions de files de manera eficient, garantint que les vostres operacions de dades funcionin sense cap problema. Estigueu atents mentre explorem els passos i solucionem problemes habituals al llarg del camí!

Entendre les ordres d'SQL Server per modificar taules i actualitzar files

Quan es treballa amb SQL Server, especialment amb taules que contenen grans conjunts de dades, és crucial seguir un enfocament ordenat per alterar una taula i actualitzar-ne les files. Un escenari comú és la necessitat d'afegir una columna nova a una taula i, a continuació, actualitzar les files en funció de condicions específiques, com ara establir una bandera basada en les dates. L'script que he proporcionat demostra un enfocament senzill d'això, però destaca les ordres clau d'SQL Server que són essencials per assolir aquestes tasques de manera eficient. El ALTERA LA TAULA L'ordre s'utilitza per afegir una nova columna a la taula. Per exemple, quan executem `ALTER TABLE dbo.sample ADD IS_CURRENT BIT;`, estem modificant l'estructura de la taula per introduir una nova columna anomenada `IS_CURRENT' de tipus `BIT' (un tipus booleà, ja sigui 0 o 1).

Després d'afegir la columna, el següent pas és actualitzar les files de la taula en funció de determinades condicions. Això s'aconsegueix utilitzant el ACTUALITZACIÓ comandament. Per exemple, la consulta `UPDATE dbo.sample SET IS_CURRENT = 0 WHERE LOAD_DATE

En alguns casos, especialment quan es tracta de taules grans que contenen milions de files, és important assegurar-se que les ordres SQL s'executen de manera eficient. Aquí és on funciona com DATEADD i DATEDIFF entrar en joc. Aquestes funcions us permeten manipular i comparar dates amb precisió. A la segona consulta d'actualització, `DATEADD(mes, DATEDIFF(mes, 0, DATEADD(DAY, -60, GETDATE())), 0)` resta 60 dies de la data actual (`GETDATE()`) i restableix el temps per començar el mes. Mitjançant aquestes funcions, podem definir intervals de dates més dinàmics que s'ajusten a mesura que avança el temps, garantint que les dades es mantinguin actuals encara que envelleixin.

Tanmateix, quan es combinen les sentències "ALTER TABLE" i "UPDATE" en un sol script, de vegades SQL Server pot generar l'error "Nom de columna no vàlid". Això passa perquè la columna afegida per `ALTER TABLE` pot ser que SQL Server no hagi confirmat o reconegut completament durant l'execució de consultes posteriors en el mateix lot. La solució a aquest problema és separar la instrucció `ALTER TABLE` i les ordres `UPDATE`, assegurant-se que l'alteració de la taula està totalment compromesa abans de realitzar les actualitzacions. En fer-ho, SQL Server tindrà la nova columna registrada correctament al seu esquema, permetent actualitzacions fluides de la taula. Quan manegeu grans conjunts de dades, considereu executar aquestes operacions per lots o utilitzar transaccions per garantir que el procés sigui el més eficient possible, evitant possibles temps d'espera o bloquejos. 🚀

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

Explicació de les ordres SQL específiques utilitzades als scripts

Optimització de scripts d'SQL Server per a taules grans

Quan es treballa amb taules grans a SQL Server, és important tenir en compte les tècniques d'optimització del rendiment a l'hora d'alterar l'estructura de la taula i actualitzar les files existents. Un dels problemes més habituals que s'enfronten quan s'executen scripts en taules grans és el temps que triguen a completar aquestes operacions, especialment quan una taula conté més d'un milió de files. Les operacions en qüestió, com ara afegir una columna amb el ALTERA LA TAULA L'ordre i l'actualització de files en funció de condicions de data específiques poden trigar un temps important. L'optimització d'aquestes operacions és encara més important quan treballeu en bases de dades de producció on el rendiment és una prioritat. Un únic script pot bloquejar la taula durant períodes prolongats, afectant altres consultes i usuaris.

Per mitigar els problemes de rendiment, un dels millors enfocaments és dividir les tasques en passos més petits. Per exemple, en comptes d'afegir una columna i actualitzar totes les files en un sol script, penseu en executar el ALTERA LA TAULA comanda per separat, seguit de l'agrupació per lots UPDATE operacions. Si actualitzeu els registres en trossos més petits, l'script no aclapararà el servidor. Podeu aprofitar el UPDATE TOP comanda per limitar el nombre de files afectades en cada transacció. A més, també és una bona idea crear índexs a les columnes utilitzades al vostre WHERE clàusules (com ara LOAD_DATE) per accelerar el procés de cerca. Per a conjunts de dades grans, els índexs redueixen el temps que triga a filtrar les files en funció dels intervals de dates.

Una altra consideració important és l'ús de transaccions i la gestió d'errors per garantir que les operacions s'executen atòmicament. En embolicar el teu UPDATE declaracions dins a BEGIN TRANSACTION i COMMIT, us assegureu que els canvis es realitzen d'una manera segura i coherent. Si alguna part del procés falla, podeu utilitzar ROLLBACK per revertir els canvis, evitant actualitzacions parcials. A més, l'execució de scripts durant les hores de baixa intensitat o l'ús de l'agent SQL Server per programar aquestes operacions garanteix un impacte mínim en el rendiment del sistema. Amb aquestes optimitzacions, podeu executar de manera segura modificacions complexes en taules grans mantenint la integritat del sistema. 🖥️