TRUNCATE TABLE (Transact-SQL)

Se aplica a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

Quita todas las filas de una tabla o las particiones especificadas de una tabla, sin registrar las eliminaciones individuales de filas. TRUNCATE TABLE es similar a la DELETE instrucción sin WHERE cláusula; sin embargo, TRUNCATE TABLE es más rápida y usa menos recursos del registro de transacciones y del sistema.

Convenciones de sintaxis de Transact-SQL

Sintaxis

Sintaxis para SQL Server y Azure SQL Database.

TRUNCATE TABLE
    { database_name.schema_name.table_name | schema_name.table_name | table_name }
    [ WITH ( PARTITIONS ( { <partition_number_expression> | <range> }
    [ , ...n ] ) ) ]
[ ; ]

<range> ::=
<partition_number_expression> TO <partition_number_expression>

Sintaxis para Azure Synapse Analytics y Almacenamiento de datos paralelos.

TRUNCATE TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }
[ ; ]

Argumentos

database_name

El nombre de la base de datos.

schema_name

El nombre del esquema al que pertenece la tabla.

table_name

Nombre de la tabla que se va a truncar o de la que se quitan todas las filas. table_name debe ser un valor literal. table_name no puede ser la OBJECT_ID() función ni una variable.

WITH ( PARTITIONS ( { <partition_number_expression> | <range> } [ , ...n ] ) )

Se aplica a: SQL Server 2016 (13.x) y versiones posteriores.

Especifica las particiones para truncar o desde las que se quitan todas las filas. Si la tabla no tiene particiones, el WITH PARTITIONS argumento genera un error. Si no se proporciona la WITH PARTITIONS cláusula , se trunca toda la tabla.

<partition_number_expression> se puede especificar de estas maneras:

• Proporcionando el número de una partición, como por ejemplo: WITH (PARTITIONS (2))

• Proporcionando los números de partición para varias particiones individuales separadas por comas, como por ejemplo: WITH (PARTITIONS (1, 5))

• Proporcionando ambos intervalos y particiones individuales, como por ejemplo: WITH (PARTITIONS (2, 4, 6 TO 8))

• <range> se puede especificar como números de partición separados por la palabra TO, por ejemplo: WITH (PARTITIONS (6 TO 8))

Para truncar una tabla con particiones, la tabla y los índices deben estar alineados (con particiones en la misma función de partición).

Comentarios

En comparación con la DELETE instrucción , TRUNCATE TABLE tiene las siguientes ventajas:

• Se utiliza menos espacio del registro de transacciones.

  La DELETE instrucción quita las filas una a la vez y registra una entrada en el registro de transacciones para cada fila eliminada. TRUNCATE TABLE quita los datos al cancelar la asignación de las páginas de datos utilizadas para almacenar los datos de la tabla y solo graba en el registro de transacciones las cancelaciones de asignación de páginas.

• Por regla general, se utilizan menos bloqueos.

  Cuando la DELETE instrucción se ejecuta mediante un bloqueo de fila, cada fila de la tabla se bloquea para su eliminación. TRUNCATE TABLE siempre bloquea la tabla (incluido un bloqueo de esquema (SCH-M) y la página, pero no cada fila.

• Las páginas cero se conservan en la tabla sin excepciones.

  Una vez ejecutada una DELETE instrucción, la tabla todavía puede contener páginas vacías. Por ejemplo, las páginas vacías de un montón no se pueden desasignar sin al menos un bloqueo de tabla exclusivo (LCK_M_X). Si en la operación de eliminación no se utiliza un bloqueo de tabla, la tabla (montón) contiene muchas páginas vacías. En el caso de los índices, la operación de eliminación puede dejar páginas vacías detrás, aunque un proceso de limpieza en segundo plano desasigna estas páginas rápidamente.

TRUNCATE TABLE quita todas las filas de una tabla, pero la estructura de la tabla y sus columnas, restricciones, índices, etc., permanecen. Para quitar la definición de tabla además de los datos, utilice la instrucción DROP TABLE.

Si la tabla contiene una columna de identidad, el contador para dicha columna se restablece al valor de inicialización definido para ella. Si no se definió ningún valor de inicialización, se usa el valor 1 predeterminado. Para conservar el contador de identidades, use DELETE en su lugar.

Una TRUNCATE TABLE operación se puede revertir dentro de una transacción.

Limitaciones

No se puede usar TRUNCATE TABLE en tablas que:

• Se hace referencia a ello mediante una FOREIGN KEY restricción . (Puede truncar una tabla que tenga una clave externa que haga referencia a sí misma).

• Tablas que participan en una vista indizada.

• Tablas que se publican mediante replicación transaccional o replicación de mezcla.

• Tablas temporales con versiones del sistema.

• Se hace referencia a una EDGE restricción.

En el caso de las tablas con una o varias de estas características, use la DELETE instrucción en su lugar.

TRUNCATE TABLE no puede activar un desencadenador porque la operación no registra eliminaciones de filas individuales. Para más información, consulte CREATE TRIGGER (Transact-SQL).

En Azure Synapse Analytics y Sistema de la plataforma de análisis (PDW):

• TRUNCATE TABLE no se permite dentro de la EXPLAIN instrucción .

• TRUNCATE TABLE no se puede ejecutar dentro de una transacción.

Truncar tablas grandes

Microsoft SQL Server ofrece la posibilidad de quitar o truncar las tablas con más de 128 extensiones sin mantener bloqueos simultáneos en todas las extensiones necesarias para la eliminación.

Permisos

El permiso mínimo necesario es ALTER en table_name. TRUNCATE TABLE los permisos predeterminados para el propietario de la tabla, los miembros del rol fijo de servidor sysadmin y los db_owner roles fijos de base de datos y db_ddladmin , y no se pueden transferir. No obstante, puede incorporar la instrucción TRUNCATE TABLE en un módulo, por ejemplo un procedimiento almacenado, y conceder los permisos correspondientes al módulo mediante la cláusula EXECUTE AS.

Ejemplos

A. Truncar una tabla

En el siguiente ejemplo se quitan todos los datos de la tabla JobCandidate. Se incluyen instrucciones SELECT antes y después de la instrucción TRUNCATE TABLE para comparar los resultados.

USE AdventureWorks2022;
GO

SELECT COUNT(*) AS BeforeTruncateCount
FROM HumanResources.JobCandidate;
GO

TRUNCATE TABLE HumanResources.JobCandidate;
GO

SELECT COUNT(*) AS AfterTruncateCount
FROM HumanResources.JobCandidate;
GO

B. Truncar las particiones de la tabla

Se aplica a: SQL Server 2016 (13.x) y versiones posteriores.

En el ejemplo siguiente se trunca las particiones especificadas de una tabla con particiones. La sintaxis de WITH (PARTITIONS (2, 4, 6 TO 8)) provoca que los números de partición 2, 4, 6, 7 y 8 se trunquen.

TRUNCATE TABLE PartitionTable1
WITH (PARTITIONS (2, 4, 6 TO 8));
GO

C. Reversión de una operación truncada

En el ejemplo siguiente se muestra que se puede revertir una TRUNCATE TABLE operación dentro de una transacción.

1. Cree una tabla de prueba con tres filas.

   USE [tempdb];
   GO
   CREATE TABLE TruncateTest (ID INT IDENTITY (1, 1) NOT NULL);
   GO
   INSERT INTO TruncateTest DEFAULT VALUES;
   GO 3
   

2. Compruebe los datos antes de truncar.

   SELECT * FROM TruncateTest;
   GO
   

3. Truncar la tabla dentro de una transacción y comprobar el número de filas.

   BEGIN TRANSACTION;
   
   TRUNCATE TABLE TruncateTest;
   
   SELECT * FROM TruncateTest;
   

   Verá que la tabla está vacía.

4. Revierte la transacción y compruebe los datos.

   ROLLBACK TRANSACTION;
   GO
   
   SELECT * FROM TruncateTest;
   GO
   

   Verá las tres filas.

5. Limpie la tabla.

   DROP TABLE TruncateTest;
   GO
   

Contenido relacionado

• DELETE (Transact-SQL)
• DROP TABLE (Transact-SQL)
• Crear tabla (Transact-SQL) IDENTITY (propiedad)