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 instrucción DELETE sin una cláusula WHERE; no obstante, TRUNCATE TABLE es más rápida y utiliza menos recursos de registros de transacciones y de sistema.

Convenciones de sintaxis de Transact-SQL

Sintaxis

-- Syntax for SQL Server and 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>  
-- Syntax for Azure Synapse Analytics and Parallel Data Warehouse  
  
TRUNCATE TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }  
[;]  

Nota:

Para ver la sintaxis de Transact-SQL para SQL Server 2014 (12.x) y versiones anteriores, consulte Versiones anteriores de la documentación.

Argumentos

database_name

Es el nombre de la base de datos.

schema_name

Es el nombre del esquema al que pertenece la tabla.

table_name

Es el nombre de la tabla que se va a truncar o de la que se van a quitar todas las filas. table_name debe ser un valor literal. table_name no puede ser la variable o la función de OBJECT_ID() .

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

Se aplica a: SQL Server (desde SQL Server 2016 (13.x) hasta la versión actual)

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

<partition_number_expression> se puede especificar de las maneras siguientes:

  • 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).

Observaciones

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

  • Se utiliza menos espacio del registro de transacciones.

    La instrucción DELETE quita una a una las filas y graba una entrada en el registro de transacciones por 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.

    Si se ejecuta la instrucción DELETE con un bloqueo de fila, se bloquea cada fila de la tabla 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.

    Después de ejecutar una instrucción DELETE, la tabla puede seguir conteniendo páginas vacías. Por ejemplo, no se puede cancelar la asignación de las páginas vacías de un montón sin un bloqueo de tabla exclusivo (LCK_M_X) como mínimo. 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, aunque la asignación de estas páginas se puede cancelar rápidamente mediante un proceso de limpieza en segundo plano.

TRUNCATE TABLE quita todas las filas de una tabla, pero permanecen la estructura y sus columnas, las restricciones, los índices, etc. 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 define ningún valor de inicialización, se utiliza el valor predeterminado 1. Para conservar el contador de identidad, utilice DELETE.

Nota

Se puede revertir una operación TRUNCATE TABLE.

Restricciones

No se puede usar TRUNCATE TABLE en los siguientes casos:

  • Tablas a las que se hace referencia mediante una restricción FOREIGN KEY. (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.

  • Tablas a las que se hace referencia con una restricción EDGE.

En el caso de las tablas con una o más de estas características, utilice la instrucción DELETE.

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 instrucción EXPLAIN.

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

Truncar tablas de gran tamaño

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. Los permisos TRUNCATE TABLE se adjudican de manera predeterminada al propietario de la tabla, a los miembros del rol fijo de servidor sysadmin y a los roles fijos de base de datos db_owner 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 la tabla de particiones

Se aplica a: SQL Server (desde SQL Server 2016 (13.x) hasta la versión actual)

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  

Consulte también

DELETE (Transact-SQL)
DROP TABLE (Transact-SQL)
IDENTITY (Propiedad) (Transact-SQL)