La forma más rápida de eliminar todos los datos en una tabla grande
Tuve que eliminar todas las filas de una tabla de registro que contenía aproximadamente 5 millones de filas. Mi primer intento fue emitir el siguiente comando en el analizador de consultas:
eliminar de client_log
que tomó mucho tiempo.
Echa un vistazo a truncar tabla que es mucho más rápido.
Descubrí TRUNCATE TABLE en la referencia msdn transact-SQL. Para todos los interesados aquí están los comentarios:
TRUNCATE TABLE es funcionalmente idéntico a la instrucción DELETE sin cláusula WHERE: ambos eliminan todas las filas de la tabla. Pero TRUNCATE TABLE es más rápido y utiliza menos recursos del sistema y de registro de transacciones que DELETE.
La instrucción DELETE elimina las filas de una en una y registra una entrada en el registro de transacciones para cada fila eliminada. TRUNCATE TABLE elimina los datos al desasignar las páginas de datos utilizadas para almacenar los datos de la tabla, y solo las desasignaciones de páginas se registran en el registro de transacciones.
TRUNCATE TABLE elimina todas las filas de una tabla, pero la estructura de la tabla y sus columnas, restricciones, índices, etc. permanecen. El contador utilizado por una identidad para las nuevas filas se restablece a la semilla de la columna. Si desea conservar el contador de identidad, use DELETE en su lugar. Si desea eliminar la definición de la tabla y sus datos, use la instrucción DROP TABLE.
No puede usar TRUNCATE TABLE en una tabla referenciada por una restricción FOREIGN KEY; en su lugar, use la declaración DELETE sin una cláusula WHERE. Debido a que TRUNCATE TABLE no está registrado, no puede activar un activador.
TRUNCATE TABLE no se puede usar en tablas que participan en una vista indizada.
Existe un mito común de que TRUNCATE de alguna manera omite el registro de transacciones.
Esto es un malentendido y se menciona claramente en MSDN.
Este mito se invoca en varios comentarios aquí. Vamos a erradicarlo juntos;)
Para referencia TRUNCATE TABLE también funciona en MySQL
olvide truncar y eliminar. mantenga las definiciones de su tabla (en caso de que quiera volver a crearla) y simplemente use drop table.
Utilizo el siguiente método para poner a cero las tablas, con la ventaja añadida de que me deja con una copia de archivo de la tabla.
CREATE TABLE `new_table` LIKE `table`;
RENAME TABLE `table` TO `old_table`, `new_table` TO `table`;
En SQL Server puede usar Truncate Table comando que es más rápido que una eliminación normal y también usa menos recursos. También restablecerá cualquier campo de identidad al valor semilla.
Los inconvenientes de truncar son que no se puede usar en tablas a las que hacen referencia claves externas y no disparará ningún desencadenante. Además, no podrá deshacer los datos si algo sale mal.
truncate table es no plataforma SQL independiente. Si sospecha que podría alguna vez cambiar de proveedor de base de datos, puede desconfiar de usarlo.
Tenga en cuenta que TRUNCATE también restablecerá las teclas de incremento automático, si las está utilizando.
Si no desea perder sus claves de incremento automático, puede acelerar la eliminación al eliminar en conjuntos (por ejemplo, ELIMINAR DE la tabla DONDE id> 1 Y id <10000). Lo acelerará significativamente y, en algunos casos, evitará que los datos se bloqueen.
La sugerencia de "Soltar y volver a crear la tabla" probablemente no sea buena porque eso hace que tus claves foráneas se vean mal.
USTED está usando claves extranjeras, ¿verdad?
Sí, bueno, eliminar 5 millones de filas probablemente llevará mucho tiempo. La única forma potencialmente más rápida que se me ocurre sería soltar la tabla y volver a crearla. Eso solo funciona, por supuesto, si desea eliminar TODOS los datos de la tabla.
truncar tabla client_log
es tu mejor opción, truncar mata todo el contenido de la tabla e índices y restablece cualquier semilla que tengas también.
Estoy revisando mi declaración anterior:
Debe comprender que al utilizar TRUNCATE, los datos se borrarán pero no se registrará nada en el registro de transacciones. Escribir en el registro es la razón por la que DELETE tardará para siempre en 5 millones de filas. Uso TRUNCATE a menudo durante el desarrollo, pero debes tener cuidado al usarlo en una base de datos de producción porque no podrás revertir tus cambios. Debe realizar una copia de seguridad completa de la base de datos inmediatamente después de realizar un TRUNCATE para establecer una nueva base para la restauración.
La declaración anterior tenía la intención de incitarlo a asegurarse de que comprende que hay una diferencia entre los dos. Desafortunadamente, está mal escrito y hace declaraciones no compatibles, ya que no he hecho ninguna prueba entre los dos. Se basa en declaraciones que he escuchado de otros.
De MSDN :
La instrucción DELETE elimina las filas de una en una y registra una entrada en el registro de transacciones para cada fila eliminada. TRUNCATE TABLE elimina los datos al desasignar las páginas de datos utilizadas para almacenar los datos de la tabla, y solo las desasignaciones de páginas se registran en el registro de transacciones.
Solo quería decir que hay una diferencia fundamental entre los dos y porque hay una diferencia, habrá aplicaciones en las que uno u otro puede ser inapropiado.
Si no puede usar TRUNCATE TABLE debido a claves externas y/o desencadenantes, puede considerar:
- descartar todos los índices;
- hacer el BORRADO habitual;
- volver a crear todos los índices.
Esto puede acelerar DELETE algo.