DELETE y TRUNCATE

Como bien sabemos, ambas instrucciones sirven para eliminar filas de nuestras tablas, con algunas diferencias y características propias que a continuación indicaré.

Para los ejemplos, utilizaremos las siguientes tablas, como recomendación ejecuten el siguiente script antes de ejecutar cada consulta de ejemplo:

-- ELIMINAMOS LAS TABLAS EN CASO QUE EXISTAN.
IF OBJECT_ID( 'dbo.empleados' , 'U' ) IS NOT NULL
 DROP TABLE dbo.empleados

IF OBJECT_ID( 'dbo.paisOrigen' , 'U' ) IS NOT NULL
 DROP TABLE dbo.paisOrigen

-- CREAMOS LAS TABLAS
CREATE TABLE dbo.paisOrigen
(
 cvePaisOrigen SMALLINT IDENTITY( 1,1 )
 , nombre VARCHAR( 50 )
 , CONSTRAINT PKpaisOrigen PRIMARY KEY ( cvePaisOrigen )
)

CREATE TABLE dbo.empleados
(
 cveEmpleado SMALLINT IDENTITY(1,1)
 ,nombre VARCHAR(50) 
 , cvePaisOrigen SMALLINT
 , CONSTRAINT PKempleados PRIMARY KEY ( cveEmpleado )
 , CONSTRAINT FKpaisOrigen FOREIGN KEY ( cvePaisOrigen ) REFERENCES dbo.paisOrigen( cvePaisOrigen )
)

-- INSERTAMOS VALORES EN LAS TABLAS
INSERT INTO dbo.paisorigen( nombre )
VALUES( 'MEXICO' ) , ('ESTADOS UNIDOS') , ( 'COLOMBIA' )

INSERT INTO dbo.empleados( nombre, cvePaisOrigen )
VALUES( 'PEDRO' , 1 ) , ('LUIS' , 2 ) , ( 'EDUARDO', 3 )

-- VERIFICAMOS EL CONTENIDO DE LAS TABLAS(opcional)
--SELECT * FROM dbo.paisOrigen

--SELECT * FROM dbo.empleados

DELETE

Es posible borrar las filas de una tabla, algunos ejemplos :

-- BORRANDO TODA LA TABLA
DELETE FROM dbo.empleados;

SELECT * FROM dbo.empleados; 

-- BORRAR FILTRANDO LOS DATOS
DELETE FROM dbo.empleados
WHERE cveEmpleado = 3;

SELECT * FROM dbo.empleados;  

-- BORRAR UTILIZANDO RELACION ENTRE TABLAS
DELETE e
FROM dbo.empleados e 
 INNER JOIN dbo.paisOrigen p
 ON p.cvePaisOrigen = e.cvePaisOrigen
WHERE p.nombre LIKE '%MEX%';

SELECT * FROM dbo.empleados; 

--UTILIZANDO CTEs Y RELACION ENTRE TABLAS NO ES POSIBLE REALIZAR.
;WITH cte AS(
 SELECT e.*
 FROM dbo.empleados e 
  INNER JOIN dbo.paisOrigen p
  ON p.cvePaisOrigen = e.cvePaisOrigen
 WHERE p.nombre LIKE '%MEX%'
)

DELETE FROM cte;

-- SI ES POSIBLE CUANDO SOLO ES UNA TABLA
;WITH cte AS(
 SELECT * FROM dbo.empleados
 WHERE cveEmpleado = 3
)

DELETE FROM cte;

SELECT * FROM dbo.empleados; 

-- TAMBIEN UTILIZANDO TOP 
;WITH cte AS(
 SELECT TOP (2) * 
 FROM dbo.empleados
 ORDER BY cveEmpleado DESC 
) 

DELETE FROM cte;

SELECT * FROM dbo.empleados;

-- TAMBIEN USANDO TABLAS DERIVADAS
DELETE x FROM (
 SELECT TOP (2) * 
 FROM dbo.empleados
 ORDER BY cveEmpleado DESC 
) AS x;

SELECT * FROM dbo.empleados;

-- TAMBIEN UTILIZANDO ROW_NUMBER
;WITH cte AS(
 SELECT ROW_NUMBER() OVER( ORDER BY nombre ) AS rn, * 
 FROM dbo.empleados
) 

DELETE FROM cte
WHERE rn > 2;

SELECT * FROM dbo.empleados; 

-- EL MISMO EJEMPLO CON TABLAS DERIVADAS
DELETE x FROM (
 SELECT ROW_NUMBER() OVER( ORDER BY nombre ) AS rn, * 
 FROM dbo.empleados
) AS x
WHERE rn > 2;

SELECT * FROM dbo.empleados;

Para las tablas que estamos utilizando, no toma mucho tiempo en ejecutar las consultas pero si tenemos una tabla con gran cantidad de datos, tomaría mucho tiempo en eliminar las filas, por lo tanto pueden utilizar un script como el siguiente, suponiendo que una tabla contiene 15,000,000 de filas y queremos borrar de una consulta que nos arroja 1,001,250 filas:

WHILE 1 = 1
BEGIN
 DELETE TOP (1000) FROM dbo.nombreTabla
 WHERE cveProducto = 5;
 IF @@rowcount < 1000 BREAK;
END

Es una iteración que permite eliminar las primeras 1000 filas de la consulta, sabiendo que en realidad dicha consulta originalmente sin usar el TOP devuelve 1,001,250 filas, llegará un momento en que la consulta anterior solo arrojará como resultado 250 y es cuando la iteración habrá terminado.

TRUNCATE

No es posible filtrar las filas que deseamos borrar, por lo tanto hay que tener mucho cuidado al momento de utilizarla porque puede borrar todos los datos de una tabla.

TRUNCATE TABLE dbo.empleados;
SELECT * FROM dbo.empleados;

Algunas diferencias:

• TRUNCATE es mucho más rápido que DELETE ya que no escribe tanta información en el log de transacciones.
• TRUNCATE reinicia la numeración de una columna si está fue declarada con la propiedad IDENTITY, DELETE no.

DELETE FROM dbo.empleados 
WHERE nombre LIKE 'EDUARDO';

SELECT * FROM dbo.empleados;

INSERT INTO dbo.empleados ( nombre, cvePaisOrigen )
VALUES( 'ALBERTO' , 3 ); 

SELECT * FROM dbo.empleados;

TRUNCATE TABLE dbo.empleados;

SELECT * FROM dbo.empleados;

INSERT INTO dbo.empleados ( nombre, cvePaisOrigen )
VALUES( 'ALBERTO' , 3 ); 

SELECT * FROM dbo.empleados;

• DELETE requiere de permisos DELETE sobre la tabla, TRUNCATE requiere permisos ALTER sobre la tabla.
• TRUNCATE no activa algún disparador porque no registra eliminaciones, DELETE si lo hace.

-- AVERIGUAMOS SI EXISTE EL TRIGGER, DE SER ASI, LO ELIMINAMOS
IF OBJECT_ID('dbo.trDelete','TR') IS NOT NULL
 DROP TRIGGER dbo.trDelete
GO

-- CREAMOS DE NUEVO EL TRIGGER
CREATE TRIGGER dbo.trDelete
ON dbo.empleados
AFTER DELETE
AS
 SELECT * 
 FROM deleted;
GO


TRUNCATE TABLE dbo.empleados;

Nuevamente volvemos a ejecutar el primer script de este post y después:

-- AVERIGUAMOS SI EXISTE EL TRIGGER, DE SER ASI, LO ELIMINAMOS
IF OBJECT_ID('dbo.trDelete','TR') IS NOT NULL
 DROP TRIGGER dbo.trDelete
GO

-- CREAMOS DE NUEVO EL TRIGGER
CREATE TRIGGER dbo.trDelete
ON dbo.empleados
AFTER DELETE
AS
 SELECT * 
 FROM deleted;
GO

DELETE FROM dbo.empleados;

• TRUNCATE no se puede ejecutar en una tabla donde su llave primaria es utilizada en otra tabla como FOREIGN KEY, aun cuando no existan filas relacionadas. DELETE si lo hace.

TRUNCATE TABLE dbo.empleados

TRUNCATE TABLE dbo.paisOrigen

DELETE FROM dbo.empleados

DELETE FROM dbo.paisOrigen

Recuerden que todos los scripts requieren de la ejecución del primero que aparece en este post, si tienen algún detalle o les genera algún error, favor de avisarme y les responderé tan pronto como pueda.

SALUDOS.

COMPARTE ESTA INFORMACION SI TE PARECIO INTERESANTE