Las mejores formas de utilizar la sentencia SQL Delete en una tabla SQL

En las bases de datos relacionales, creamos tablas para almacenar datos en varios formatos. SQL Server almacena los datos en un formato de filas y columnas que contiene un valor asociado a cada tipo de datos. Cuando diseñamos tablas SQL, definimos tipos de datos como integer, float, decimal, varchar y bit. Por ejemplo, una tabla que almacena datos de clientes puede tener campos como nombre del cliente, correo electrónico, dirección, estado, país, etc. En una tabla SQL se ejecutan varios comandos SQL que pueden dividirse en las siguientes categorías:

• Lenguaje de definición de datos (DDL): Estos comandos se utilizan para crear y modificar los objetos de una base de datos.
  • Crear: Crea objetos
  • Alterar: Modifica objetos
  • Drop: Elimina objetos
  • Truncate: Elimina todos los datos de una tabla
• Lenguaje de manipulación de datos (DML): Estos comandos insertan, recuperan, modifican, eliminan y actualizan datos en la base de datos.
  • Seleccionar: Recupera datos de una o varias tablas
  • Insertar: Añade nuevos datos en una tabla
  • Actualizar: Modifica los datos existentes
  • Eliminar: Elimina registros existentes en una tabla

• Lenguaje de control de datos (DCL): Estos comandos están asociados a los controles de derechos o permisos en una base de datos.
  • Conceder: Asigna permisos a un usuario
  • Revocar: Revoca permisos a un usuario
• Lenguaje de control de transacciones (TCL): Estos comandos controlan las transacciones en una base de datos.
  • Commit: Guarda los cambios realizados por la consulta
  • Rollback: Retrocede una transacción explícita o implícita al principio de la transacción, o a un punto de guardado dentro de la transacción
  • Guardar transacciones: Establece un punto de guardado o marcador dentro de una transacción

Suponga que tiene datos de pedidos de clientes almacenados en una tabla SQL. Si sigue insertando datos en esta tabla continuamente, la tabla podría contener millones de registros, lo que causaría problemas de rendimiento en sus aplicaciones. Además, el mantenimiento de los índices podría llevarle mucho tiempo. A menudo, no es necesario conservar los pedidos que tienen más de tres años de antigüedad. En estos casos, podría eliminar esos registros de la tabla. Esto ahorraría espacio de almacenamiento, así como reducir sus esfuerzos de mantenimiento.

Puede eliminar datos de una tabla SQL de dos maneras:

• Usando una sentencia SQL delete
• Usando un truncamiento

Veremos la diferencia entre estos comandos SQL más adelante. Exploremos primero la sentencia SQL delete.

Una sentencia SQL delete sin condiciones

En las sentencias del lenguaje de manipulación de datos (DML), una sentencia SQL delete elimina las filas de una tabla. Se puede eliminar una fila específica o todas las filas. Una sentencia delete básica no requiere ningún argumento.

Creemos una tabla SQL Orders utilizando el siguiente script. Esta tabla tiene tres columnas , y .

Create Table Orders ( OrderID int, ProductName varchar(50), ProductQuantity int )

Inserte algunos registros en esta tabla.

Insert into Orders values (1,'ABC books',10), (2,'XYZ',100), (3,'SQL book',50)

Ahora, supongamos que queremos eliminar los datos de la tabla. Puedes especificar el nombre de la tabla para eliminar los datos utilizando la sentencia delete. Ambas sentencias SQL son iguales. Podemos especificar el nombre de la tabla desde la palabra clave (opcional) o especificar el nombre de la tabla directamente después del delete.

Delete Orders Go Delete from Orders GO

Una sentencia SQL delete con datos filtrados

Estas sentencias SQL delete eliminan todos los datos de la tabla. Normalmente, no eliminamos todas las filas de una tabla SQL. Para eliminar una fila específica, podemos añadir una cláusula where con la sentencia delete. La cláusula where contiene los criterios de filtrado y finalmente determina qué fila(s) eliminar.

Por ejemplo, supongamos que queremos eliminar el id de pedido 1. Una vez que añadimos una cláusula where, SQL Server primero comprueba las filas correspondientes y elimina esas filas específicas.

Delete Orders where orderid=1

Si la condición de la cláusula where es falsa, no elimina ninguna fila. Por ejemplo, hemos eliminado la orden 1 de la tabla pedidos. Si volvemos a ejecutar la sentencia, no encuentra ninguna fila que satisfaga la condición de la cláusula where. En este caso, devuelve 0 filas afectadas.

Sentencia SQL delete y cláusula TOP

También se puede utilizar la sentencia TOP para eliminar las filas. Por ejemplo, la siguiente consulta elimina las 100 primeras filas de la tabla Orders.

Delete top (100)  from Orders

Como no hemos especificado ninguna cláusula ‘ORDER BY’, elige filas al azar y las elimina. Podemos utilizar la cláusula Order by para ordenar los datos y eliminar las filas superiores. En la siguiente consulta, ordena los datos en orden descendente, y luego los elimina de la tabla.

Delete from Orders where In ( Select top 100 FROM Orders order by Desc )

Eliminación de filas basadas en otra tabla

A veces necesitamos eliminar filas basadas en otra tabla. Esta tabla puede existir en la misma base de datos o no.

• Búsqueda de tablas

Podemos utilizar el método de búsqueda de tablas o SQL join para eliminar estas filas. Por ejemplo, queremos eliminar las filas de la tabla que satisfacen la siguiente condición:

Debe tener filas correspondientes en la tabla ..

Mira la siguiente consulta, aquí tenemos una sentencia select en la cláusula where de la sentencia delete. SQL Server primero obtiene las filas que satisfacen la sentencia select y luego elimina esas filas de la tabla utilizando la sentencia SQL delete.

Delete Orders where orderid in (Select orderid  from Customer)

• SQL Join

Alternativamente, podemos utilizar SQL joins entre estas tablas y eliminar las filas. En la siguiente consulta, unimos las tablas ] con la tabla. Una unión SQL siempre funciona en una columna común entre las tablas. Tenemos una columna que une ambas tablas.

DELETE Orders FROM Orders o INNER JOIN Customer c ON o.orderid=c.orderid

Para entender la sentencia de borrado anterior, veamos el plan de ejecución real.

Según el plan de ejecución, realiza un escaneo de la tabla en ambas tablas, obtiene los datos que coinciden y los borra de la tabla Pedidos.

• Expresión común de tabla (CTE)

También podemos utilizar una expresión común de tabla (CTE) para eliminar las filas de una tabla SQL. Primero, definimos una CTE para encontrar la fila que queremos eliminar.

Después, unimos la CTE con la tabla SQL Órdenes y eliminamos las filas.

WITH cteOrders AS (SELECT OrderID FROM Customer WHERE CustomerID = 1 ) DELETE Orders FROM cteOrders sp INNER JOIN dbo.Orders o ON o.orderid = sp.orderid;

Impactos en el rango de identidades

Las columnas de identidad en SQL Server generan valores únicos y secuenciales para su columna. Se utilizan principalmente para identificar de forma única una fila en la tabla SQL. Una columna de clave primaria también es una buena opción para un índice agrupado en SQL Server.

En el siguiente script, tenemos una tabla. Esta tabla tiene una columna de identidad id.

Create Table Employee ( id int identity(1,1),  varchar(50) )

Insertamos 50 registros en esta tabla que generaron los valores de identidad para la columna id.

Declare @id int=1 While(@id<=50) BEGIN Insert into Employee() values('Test'+CONVERT(VARCHAR,@ID)) Set @id=@id+1 END

Si eliminamos algunas filas de la tabla SQL, no se restablecen los valores de identidad para los valores posteriores. Por ejemplo, vamos a eliminar unas cuantas filas que tienen valores de identidad 20 a 25.

Delete from employee where id between 20 and 25

Ahora, vea los registros de la tabla.

Select * from employee where id>15

Muestra la brecha en el rango de valores de identidad.

La sentencia SQL delete y el registro de transacciones

SQL delete registra cada eliminación de filas en el registro de transacciones. Suponga que necesita eliminar millones de registros de una tabla SQL. No quiere eliminar un gran número de registros en una sola transacción porque podría hacer que su archivo de registro crezca exponencialmente y su base de datos podría no estar disponible también. Si usted cancela una transacción en el medio, podría tomar horas para revertir una sentencia de eliminación.

En este caso, siempre debe eliminar las filas en pequeños trozos y confirmar esos trozos regularmente. Por ejemplo, puede eliminar un lote de 10.000 filas a la vez, consignarlo y pasar al siguiente lote. Cuando SQL Server consigna el trozo, se puede controlar el crecimiento del registro de transacciones.

Mejores prácticas

• Siempre debe realizar una copia de seguridad antes de eliminar datos.
• Por defecto, SQL Server utiliza transacciones implícitas y consigna los registros sin preguntar al usuario. Como mejor práctica, debe iniciar una transacción explícita utilizando Begin Transaction. Esto le da el control para comprometer o revertir la transacción. También debe ejecutar copias de seguridad frecuentes del registro de transacciones si su base de datos está en modo de recuperación completa.
• Desea eliminar los datos en trozos pequeños para evitar el uso excesivo del registro de transacciones. También evita los bloqueos para otras transacciones SQL también.
• Debe restringir los permisos para que los usuarios no puedan eliminar datos. Sólo los usuarios autorizados deben tener acceso a eliminar datos de una tabla SQL.
• Desea ejecutar la sentencia delete con una cláusula where. Elimina los datos filtrados de una tabla SQL. Si su aplicación requiere la eliminación frecuente de datos, es una buena idea para restablecer los valores de identidad periódicamente. De lo contrario, podría enfrentarse a problemas de agotamiento de los valores de identidad.
• En caso de querer vaciar la tabla, es aconsejable utilizar la sentencia truncate. La sentencia truncate elimina todos los datos de una tabla, utiliza un registro de transacciones mínimo, restablece el rango de valores de identidad y es más rápida que la sentencia SQL delete, ya que anula la asignación de todas las páginas de la tabla de forma inmediata.
• En caso de que utilice restricciones de clave externa (relación padre-hijo) para sus tablas, debe eliminar la fila de una fila hija y luego de la tabla padre. Si elimina la fila de la fila padre, también puede utilizar la opción de cascada al eliminar para eliminar automáticamente la fila de una tabla hija. Puede consultar el artículo Cascada de eliminación y cascada de actualización en clave foránea de SQL Server para obtener más información.
• Si utiliza la sentencia top para eliminar las filas, SQL Server elimina las filas de forma aleatoria. Siempre debe utilizar la cláusula top con las cláusulas Order by y Group by correspondientes.
• Una sentencia delete adquiere un bloqueo de intención exclusivo en la tabla de referencia; por lo tanto, durante ese tiempo, ninguna otra transacción puede modificar los datos. Puede utilizar la sugerencia NOLOCK para leer los datos.
• Debe evitar el uso de la sugerencia de tabla para anular el comportamiento de bloqueo predeterminado de la sentencia SQL delete; sólo debe ser utilizado por DBAs y desarrolladores experimentados.

Consideraciones importantes

Hay muchos beneficios de usar sentencias SQL delete para eliminar datos de una tabla SQL, pero como puede ver, requiere un enfoque metódico. Es importante eliminar siempre los datos en pequeños lotes y proceder con precaución al eliminar datos de una instancia de producción. Tener una estrategia de copia de seguridad para recuperar los datos en la cantidad mínima de tiempo es una necesidad para evitar el tiempo de inactividad o futuros impactos de rendimiento.