entre drop diferencia delete sql database truncate

drop - truncate table mysql



¿Cuál es la diferencia entre TRUNCATE y DELETE en SQL? (30)

Escribí una respuesta a esta pregunta por error en respuesta a una pregunta sobre la diferencia entre DROP y TRUNCATE , pero pensé que es una pena no compartirla, así que publicaré mi propia respuesta a mi propia pregunta ... incluso etico? :)

Editar: Si su respuesta es específica de la plataforma, puede indicar eso.

BORRAR

El comando DELETE se usa para eliminar filas de una tabla. Una cláusula WHERE se puede usar para eliminar solo algunas filas. Si no se especifica la condición WHERE, se eliminarán todas las filas. Después de realizar una operación DELETE, debe COMPROMIR o REINICIAR la transacción para que el cambio sea permanente o para deshacerlo. Tenga en cuenta que esta operación hará que se activen todos los activadores DELETE en la tabla.

TRUNCAR

TRUNCATE elimina todas las filas de una tabla. La operación no se puede revertir y no se activarán disparadores. Como tal, TRUCATE es más rápido y no usa tanto espacio de deshacer como DELETE.

SOLTAR

El comando DROP elimina una tabla de la base de datos. Todas las filas de tablas, índices y privilegios también serán eliminados. No se activarán disparadores DML. La operación no puede ser revertida.

DROP y TRUNCATE son comandos DDL, mientras que DELETE es un comando DML. Por lo tanto, las operaciones DELETE se pueden deshacer (deshacer), mientras que las operaciones DROP y TRUNCATE no se pueden deshacer.

De: http://www.orafaq.com/faq/difference_between_truncate_delete_and_drop_commands

SOLTAR

El comando DROP elimina una tabla de la base de datos. Todas las filas de tablas, índices y privilegios también serán eliminados. No se activarán disparadores DML. La operación no puede ser revertida.

TRUNCAR

TRUNCATE elimina todas las filas de una tabla. La operación no se puede revertir y no se activarán disparadores. Como tal, TRUCATE es más rápido y no usa tanto espacio de deshacer como DELETE.

BORRAR

El comando DELETE se usa para eliminar filas de una tabla. Una cláusula WHERE se puede usar para eliminar solo algunas filas. Si no se especifica la condición WHERE, se eliminarán todas las filas. Después de realizar una operación DELETE, debe COMPROMIR o REINICIAR la transacción para que el cambio sea permanente o para deshacerlo. Tenga en cuenta que esta operación hará que se activen todos los activadores DELETE en la tabla.

De: http://www.orafaq.com/faq/difference_between_truncate_delete_and_drop_commands

"Truncar no registra nada" es correcto. Yo iría más lejos:

Truncar no se ejecuta en el contexto de una transacción.

La ventaja de la velocidad de truncar sobre eliminar debe ser obvia. Esa ventaja varía de trivial a enorme, dependiendo de su situación.

Sin embargo, he visto truncado involuntariamente romper la integridad referencial y violar otras restricciones. El poder que obtiene al modificar los datos fuera de una transacción debe equilibrarse con la responsabilidad que hereda cuando camina por la cuerda floja sin una red.

Al emitir una declaración TRUNCATE TABLE, le está indicando a SQL Server que elimine todos los registros de una tabla, sin que se lleve a cabo ningún registro o procesamiento de transacciones.

Aquí está mi respuesta detallada sobre la diferencia entre DELETE y TRUNCATE en SQL Server

• Eliminar datos : primero lo primero, ambos pueden usarse para eliminar las filas de la tabla.
Pero se puede usar un BORRADO para eliminar las filas no solo de una Tabla sino también de una VISTA o el resultado de un OPENROWSET o OPENQUERY sujeto a las capacidades del proveedor.

• Cláusula FROM : con DELETE también puede eliminar filas de una tabla / view / rowset_function_limited en base a las filas de otra tabla usando otra cláusula FROM. En esa cláusula FROM también puede escribir las condiciones normales de JOIN. En realidad, puede crear una instrucción DELETE a partir de una instrucción SELECT que no contenga ninguna función agregada reemplazando SELECT con DELETE y eliminando nombres de columna.
Con TRUNCATE no puedes hacer eso.

• DONDE : Un TRUNCATE no puede tener las condiciones DONDE, pero puede un BORRAR. Eso significa que con TRUNCATE no puede eliminar una fila específica o un grupo específico de filas. TRUNCATE TABLE es similar a la sentencia DELETE sin la cláusula WHERE.

• Rendimiento : TRUNCATE TABLE es más rápido y utiliza menos recursos del sistema y del registro de transacciones. Y una de las razones es el bloqueo utilizado por cualquiera de las declaraciones. La instrucción DELETE se ejecuta mediante un bloqueo de fila, cada fila de la tabla se bloquea para su eliminación. TRUNCATE TABLE siempre bloquea la tabla y la página, pero no cada fila.

• Registro de transacciones : la instrucción DELETE elimina las filas de una en una y hace entradas individuales en el registro de transacciones para cada fila.
TRUNCATE TABLE elimina los datos al desasignar las páginas de datos utilizadas para almacenar los datos de la tabla y registra solo las desasignaciones de páginas en el registro de transacciones.

• Páginas : después de ejecutar una instrucción DELETE, la tabla aún puede contener páginas vacías. TRUNCATE elimina los datos al desasignar las páginas de datos utilizadas para almacenar los datos de la tabla.

• Activación : TRUNCATE no activa las activaciones de eliminación en la tabla. Así que debes tener mucho cuidado al usar TRUNCATE. Uno nunca debe usar TRUNCATE si eliminar Trigger está definido en la tabla para realizar una acción de limpieza automática o registro cuando se eliminan las filas.

• Columna de identidad : con TRUNCATE si la tabla contiene una columna de identidad, el contador de esa columna se restablece al valor de semilla definido para la columna. Si no se definió una semilla, se usa el valor predeterminado 1. BORRAR no restablece el contador de identidad. Por lo tanto, si desea conservar el contador de identidad, utilice DELETE en su lugar.

• Replicación : DELETE se puede usar contra la tabla usada en la replicación transaccional o en la replicación de mezcla.
Mientras que TRUNCATE no se puede usar contra las tablas involucradas en la replicación transaccional o la replicación de mezcla.

• Restablecer : la instrucción DELETE se puede revertir.
TRUNCATE también se puede retrotraer siempre que esté incluido en un bloque TRANSACTION y la sesión no esté cerrada. Una vez que se cierre la sesión, no podrá Retroceder TRUNCATE.

• Restricciones : la instrucción DELETE puede fallar si viola un desencadenante o intenta eliminar una fila a la que hacen referencia los datos en otra tabla con una restricción FOREIGN KEY. Si ELIMINAR elimina varias filas, y cualquiera de las filas eliminadas viola un desencadenante o una restricción, la declaración se cancela, se devuelve un error y no se eliminan filas.
Y si DELETE se usa en vista, esa vista debe ser una vista actualizable. TRUNCATE no se puede utilizar contra la tabla utilizada en la vista Indizada.
TRUNCATE no se puede usar contra la tabla a la que hace referencia una restricción FOREIGN KEY, a menos que una tabla que tenga una clave externa que haga referencia a sí misma.

Aquí hay una lista de diferencias. He resaltado las características específicas de Oracle y espero que la comunidad también pueda agregar la diferencia específica de otros proveedores. Las diferencias que son comunes a la mayoría de los proveedores pueden ir directamente debajo de los encabezados, con las diferencias resaltadas a continuación.

Visión general

Si desea eliminar rápidamente todas las filas de una tabla, y está realmente seguro de que desea hacerlo, y no tiene claves externas en las tablas, entonces TRUNCATE probablemente será más rápido que DELETE. .

Se deben considerar varios problemas específicos del sistema, como se detalla a continuación.

Tipo de declaración

Eliminar es DML, Truncar es DDL

Comprometerse y deshacer

Variable por vendedor

Servidor SQL

Truncar se puede deshacer.

PostgreSQL

Truncar se puede deshacer.

Oráculo

Como TRUNCATE es DDL, implica dos confirmaciones, una antes y otra después de la ejecución de la instrucción. Por lo tanto, Truncate no puede ser revertido, y una falla en el proceso de Truncate habrá emitido un commit de todos modos.

Sin embargo, vea Flashback a continuación.

Recuperación de espacio

Eliminar no recupera espacio, Truncar recupera espacio

Oráculo

Si utiliza la cláusula REUSE STORAGE, los segmentos de datos no se desasignan, lo que puede ser un poco más eficiente si la tabla se va a volver a cargar con datos. Se restablece la marca de límite superior.

Alcance de fila

Eliminar se puede usar para eliminar todas las filas o solo un subconjunto de filas. Truncar elimina todas las filas.

Oráculo

Cuando una tabla está particionada, las particiones individuales se pueden truncar de forma aislada, por lo que es posible una eliminación parcial de todos los datos de la tabla.

Tipos de objeto

Eliminar se puede aplicar a tablas y tablas dentro de un clúster. Truncar se aplica solo a las tablas o al clúster completo. (Puede ser específico para Oracle)

Identidad del objeto de datos

Oráculo

Eliminar no afecta la identificación del objeto de datos, pero truncar asigna una nueva identificación de objeto de datos a menos que nunca haya habido una inserción contra la tabla desde su creación. Incluso una única inserción que se revierte hará que se asigne una nueva identificación de objeto de datos al truncar .

Flashback (Oracle)

El flashback funciona a través de eliminaciones, pero un truncado evita el flashback a los estados antes de la operación.

Sin embargo, a partir de 11gR2, la característica FLASHBACK ARCHIVE lo permite, excepto en Express Edition

Uso de FLASHBACK en Oracle http://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#ADFNS638

Privilegios

Variable

Oráculo

El borrado se puede otorgar en una tabla a otro usuario o rol, pero truncar no se puede hacer sin usar una concesión DROP ANY TABLE.

Rehacer Deshacer

Eliminar genera una pequeña cantidad de rehacer y una gran cantidad de deshacer. Truncar genera una cantidad despreciable de cada uno.

Índices

Oráculo

Una operación truncada hace que los índices inutilizables sean utilizables nuevamente. Eliminar no lo hace.

Llaves extranjeras

Un truncado no se puede aplicar cuando una clave foránea habilitada hace referencia a la tabla. El tratamiento con eliminar depende de la configuración de las claves foráneas.

Bloqueo de mesa

Oráculo

Truncar requiere un bloqueo de tabla exclusivo, eliminar requiere un bloqueo de tabla compartido. Por lo tanto, deshabilitar los bloqueos de tabla es una forma de evitar operaciones truncadas en una tabla.

Gatillos

Los activadores de DML no se activan en un truncado.

Oráculo

Los disparadores DDL están disponibles.

Ejecución remota

Oráculo

Truncate no se puede emitir a través de un enlace de base de datos.

Columnas de identidad

Servidor SQL

Truncar restablece la secuencia para los tipos de columna IDENTIDAD, la eliminación no lo hace.

Conjunto resultante

En la mayoría de las implementaciones, una instrucción DELETE puede devolver al cliente las filas que se eliminaron.

Por ejemplo, en un subprograma de Oracle PL / SQL, usted podría:

DELETE FROM employees_temp WHERE employee_id = 299 RETURNING first_name, last_name INTO emp_first_name, emp_last_name;

Con SQL Server o MySQL, si hay un PK con incremento automático, truncar restablecerá el contador.

Declaración DELETE: este comando elimina solo las filas de la tabla según la condición dada en la cláusula where o elimina todas las filas de la tabla si no se especifica ninguna condición. Pero no libera el espacio que contiene la tabla.

La sintaxis de una sentencia DELETE de SQL es:

ELIMINAR DE table_name [WHERE condicion];

Sentencia TRUNCATE: este comando se utiliza para eliminar todas las filas de la tabla y liberar el espacio que contiene la tabla.

En SQL Server 2005 creo que se puede revertir un truncado

En resumen, Truncate no registra nada (por lo que es mucho más rápido pero no se puede deshacer), mientras que la eliminación se registra (y puede ser parte de una transacción más grande, se revertirá, etc.). Si tiene datos que no desea en una tabla en el desarrollo, normalmente es mejor truncarlos, ya que no corre el riesgo de completar el registro de transacciones.

La diferencia entre truncar y eliminar se enumera a continuación:

+----------------------------------------+----------------------------------------------+ | Truncate | Delete | +----------------------------------------+----------------------------------------------+ | We can''t Rollback after performing | We can Rollback after delete. | | Truncate. | | | | | | Example: | Example: | | BEGIN TRAN | BEGIN TRAN | | TRUNCATE TABLE tranTest | DELETE FROM tranTest | | SELECT * FROM tranTest | SELECT * FROM tranTest | | ROLLBACK | ROLLBACK | | SELECT * FROM tranTest | SELECT * FROM tranTest | +----------------------------------------+----------------------------------------------+ | Truncate reset identity of table. | Delete does not reset identity of table. | +----------------------------------------+----------------------------------------------+ | It locks the entire table. | It locks the table row. | +----------------------------------------+----------------------------------------------+ | Its DDL(Data Definition Language) | Its DML(Data Manipulation Language) | | command. | command. | +----------------------------------------+----------------------------------------------+ | We can''t use WHERE clause with it. | We can use WHERE to filter data to delete. | +----------------------------------------+----------------------------------------------+ | Trigger is not fired while truncate. | Trigger is fired. | +----------------------------------------+----------------------------------------------+ | Syntax : | Syntax : | | 1) TRUNCATE TABLE table_name | 1) DELETE FROM table_name | | | 2) DELETE FROM table_name WHERE | | | example_column_id IN (1,2,3) | +----------------------------------------+----------------------------------------------+

La instrucción DELETE puede tener una cláusula WHERE para eliminar registros específicos, mientras que la instrucción TRUNCATE no requiere ninguna y borra toda la tabla. Es importante destacar que la sentencia DELETE registra la fecha de eliminación, mientras que la sentencia TRUNCATE no lo hace.

La mayor diferencia es que truncate es una operación no registrada mientras que delete es.

Simplemente significa que en caso de que se produzca una falla en la base de datos, no puede recuperar los datos operados por Truncate, pero sí puede eliminarlos.

Más detalles here

Me gustaría comentar sobre la publicación de matthieu, pero todavía no tengo el representante ...

En MySQL, el contador de incremento automático se restablece con truncar, pero no con eliminar.

No es que Truncate no registre nada en SQL Server. truncate no registra ninguna información, pero registra la desasignación de la página de datos para la tabla en la que disparó TRUNCATE.

y el registro truncado se puede revertir si definimos la transacción al comienzo y podemos recuperar el registro truncado después de revertirlo. Pero no puede recuperar registros truncados de la copia de seguridad del registro de transacciones después de una transacción truncada confirmada.

No se puede hacer DDL a través de un enlace doble.

Otra diferencia específica para el servidor Microsoft SQL es que con delete puede usar output declaración de output para rastrear qué registros se han eliminado, por ejemplo:

delete from [SomeTable] output deleted.Id, deleted.Name

No puedes hacer esto con truncate .

Sí, DELETE es más lento, TRUNCATE es más rápido. ¿Por qué?

DELETE debe leer los registros, verificar las restricciones, actualizar el bloque, actualizar los índices y generar rehacer / deshacer. Todo eso lleva tiempo.

¡TRUNCATE simplemente ajusta un puntero en la base de datos para la tabla (High Water Mark) y poof! los datos se han ido

Esto es específico de Oracle, AFAIK.

Si accidentalmente eliminó todos los datos de la tabla utilizando Eliminar / Truncar. Puede deshacer la transacción confirmada. Restaure la última copia de seguridad y ejecute el registro de transacciones hasta el momento en que se producirá Eliminar / Truncar.

La información relacionada a continuación es de una publicación de blog :

Mientras trabajamos en la base de datos, estamos usando Eliminar y Truncar sin conocer las diferencias entre ellos. En este artículo, discutiremos la diferencia entre Eliminar y Truncar en Sql.

Borrar:

  • Eliminar es un comando DML.
  • La instrucción de eliminación se ejecuta mediante un bloqueo de fila, cada fila de la tabla se bloquea para su eliminación.
  • Podemos especificar filtros en la cláusula where.
  • Borra los datos especificados si la condición existe.
  • Eliminar actividades un disparador porque la operación se registra de forma individual.
  • Más lento que Truncate porque mantiene registros

Truncar

  • Truncar es un comando DDL.
  • Truncar tabla siempre bloquea la tabla y la página, pero no cada fila. Como elimina todos los datos.
  • No se puede utilizar donde la condición.
  • Elimina todos los datos.
  • La tabla de truncamiento no puede activar un disparador porque la operación no registra las eliminaciones de filas individuales.
  • Más rápido en cuanto a rendimiento, ya que no mantiene ningún registro.

Nota: Eliminar y Truncar ambos pueden revertirse cuando se usan con Transacción. Si Transaction está hecho, significa que estamos comprometidos, entonces no podemos deshacer el comando Truncar, pero sí podemos deshacer el comando Eliminar de los archivos de registro, ya que los registros de escritura y borrado los graban en el archivo de registro en caso de que sea necesario restaurarlos en el futuro desde archivos de registro.

Si tiene una restricción de clave externa que se refiere a la tabla que está intentando truncar, esto no funcionará incluso si la tabla de referencia no tiene datos en ella. Esto se debe a que la comprobación de la clave externa se realiza con DDL en lugar de DML. Esto se puede solucionar desactivando temporalmente las restricciones de clave externa en la tabla.

Eliminar tabla es una operación registrada. Así que la eliminación de cada fila se registra en el registro de transacciones, lo que lo hace lento. Truncar tabla también elimina todas las filas de una tabla, pero no registra la eliminación de cada fila, sino que registra la desasignación de las páginas de datos de la tabla, lo que lo hace más rápido.

~ Si accidentalmente eliminó todos los datos de la tabla utilizando Eliminar / Truncar. Puede deshacer la transacción confirmada. Restaure la última copia de seguridad y ejecute el registro de transacciones hasta el momento en que se producirá Eliminar / Truncar.

TRUNCATE se puede revertir si está envuelto en una transacción.

Por favor, vea las dos referencias a continuación y compruebe usted mismo:

http://blog.sqlauthority.com/2007/12/26/sql-server-truncate-cant-be-rolled-back-using-log-files-after-transaction-session-is-closed/

http://sqlblog.com/blogs/kalen_delaney/archive/2010/10/12/tsql-tuesday-11-rolling-back-truncate-table.aspx

TRUNCATE vs. DELETE es una de las preguntas infames durante las entrevistas de SQL. Solo asegúrese de explicárselo correctamente al entrevistador o podría costarle el trabajo. El problema es que no muchos lo saben, por lo que lo más probable es que consideren la respuesta como incorrecta si les dice que SÍ se puede deshacer el Truncado.

Truncar también se puede hacer retroceder aquí el ejemplo.

begin Tran delete from Employee select * from Employee Rollback select * from Employee

Truncate y Delete en SQL son dos comandos que se utilizan para eliminar o eliminar datos de la tabla. Aunque los comandos Sql de naturaleza bastante básica pueden crear muchos problemas hasta que esté familiarizado con los detalles antes de usarlos. Una elección incorrecta de comando puede resultar en un proceso muy lento o incluso puede explotar el segmento de registro, si es necesario eliminar demasiados datos y el segmento de registro no es suficiente. Es por eso que es fundamental saber cuándo usar el comando Truncar y eliminar en SQL, pero antes de usarlos, debe tener en cuenta las Diferencias entre Truncar y Eliminar, y basándonos en ellos, podremos saber cuándo DELETE es la mejor opción para eliminar Los datos o TRUNCATE deben usarse para purgar tablas.

Consulta ver clic aquí

Una diferencia adicional de las dos operaciones es que si la tabla contiene una columna de identidad, el contador de esa columna se reinicia 1 (o al valor de semilla definido para la columna) bajo TRUNCATE. ELIMINAR no tiene este efecto.

Una pequeña corrección en la respuesta original: eliminar también genera cantidades significativas de rehacer (ya que deshacer está protegido por rehacer). Esto se puede ver en la salida de autotrace:

SQL> delete from t1; 10918 rows deleted. Elapsed: 00:00:00.58 Execution Plan ---------------------------------------------------------- 0 DELETE STATEMENT Optimizer=FIRST_ROWS (Cost=43 Card=1) 1 0 DELETE OF ''T1'' 2 1 TABLE ACCESS (FULL) OF ''T1'' (TABLE) (Cost=43 Card=1) Statistics ---------------------------------------------------------- 30 recursive calls 12118 db block gets 213 consistent gets 142 physical reads 3975328 redo size 441 bytes sent via SQL*Net to client 537 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 10918 rows processed

Una razón importante por la que es útil es cuando necesita actualizar los datos en una tabla de varios millones de filas, pero no desea reconstruirlos. "Eliminar *" tardaría una eternidad, mientras que el impacto de Truncate sería insignificante.

TRUNCATE es la declaración DDL mientras que DELETE es una declaración DML. A continuación se presentan las diferencias entre los dos:

  1. Como TRUNCATE es una declaración DDL ( lenguaje de definición de datos ), no se requiere un compromiso para que los cambios sean permanentes. Y esta es la razón por la que las filas eliminadas por truncado no se pueden revertir. Por otro lado, DELETE es una declaración DML ( lenguaje de manipulación de datos ), por lo que requiere un compromiso explícito para que su efecto sea permanente.

  2. TRUNCATE siempre elimina todas las filas de una tabla, dejando la tabla vacía y la estructura de la tabla intacta, mientras que DELETE puede eliminar de forma condicional si se usa la cláusula where.

  3. Las filas eliminadas por la TRUNCATE TABLE no se pueden restaurar y no puede especificar la cláusula where en la TRUNCATE .

  4. TRUNCATE instrucciones TRUNCATE no activan los activadores a diferencia de los activados de eliminación en la instrucción DELETE

Here está el muy buen enlace relevante para el tema.

BORRAR

DELETE is a DML command DELETE you can rollback Delete = Only Delete- so it can be rolled back In DELETE you can write conditions using WHERE clause Syntax – Delete from [Table] where [Condition]

TRUNCAR

TRUNCATE is a DDL command You can''t rollback in TRUNCATE, TRUNCATE removes the record permanently Truncate = Delete+Commit -so we can''t roll back You can''t use conditions(WHERE clause) in TRUNCATE Syntax – Truncate table [Table]

Para más detalles visita.

http://www.zilckh.com/what-is-the-difference-between-truncate-and-delete/

El comando Truncate se usa para reinicializar la tabla, es un comando DDL que elimina todas las filas de la tabla. Si bien DELETE es un comando DML que se usa para eliminar una fila o un conjunto de filas de acuerdo con alguna condición, si no se especifica la condición entonces este comando eliminará todas las filas de la tabla.