TRUNCATE TABLE (Transact-SQL)

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do AzureAzure Synapse AnalyticsPDW (Analytics Platform System)

Remove todas as linhas de uma tabela ou partições especificadas de uma tabela sem registrar as exclusões de linha individual. TRUNCATE TABLEé semelhante à instrução sem WHERE cláusula, no entanto, TRUNCATE TABLE é mais rápida e usa menos recursos do DELETE sistema e do log de transações.

Convenções de sintaxe de Transact-SQL

Sintaxe

Sintaxe para SQL Server e Banco de Dados SQL do Azure.

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>

Sintaxe para o Azure Synapse Analytics e o Parallel Data Warehouse.

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

Argumentos

database_name

O nome do banco de dados.

schema_name

O nome do esquema ao qual a tabela pertence.

table_name

O nome da tabela a ser truncada ou da qual todas as linhas são removidas. table_name deve ser um literal. table_name não pode ser a OBJECT_ID() função ou uma variável.

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

Aplica-se a: SQL Server 2016 (13.x) e versões posteriores.

Especifica as partições para truncar ou das quais todas as linhas são removidas. Se a tabela não estiver particionada, o argumento gerará WITH PARTITIONS um erro. Se a WITH PARTITIONS cláusula não for fornecida, a tabela inteira será truncada.

<partition_number_expression> pode ser especificado das seguintes maneiras:

• Forneça o número de uma partição, por exemplo: WITH (PARTITIONS (2))

• Forneça os números de várias partições individuais separados por vírgulas, por exemplo: WITH (PARTITIONS (1, 5))

• Forneça os intervalos e as partições individuais, por exemplo: WITH (PARTITIONS (2, 4, 6 TO 8))

• <range> podem ser especificados como números de partição separados pela palavra TO, por exemplo: WITH (PARTITIONS (6 TO 8))

Para truncar uma tabela particionada, a tabela e os índices deverão estar alinhados (particionados na mesma função de partição).

Comentários

Em comparação com a DELETE declaração, TRUNCATE TABLE tem as seguintes vantagens:

• O espaço utilizado para log de transações é menor.

  A DELETE instrução remove linhas uma de cada vez e registra uma entrada no log de transações para cada linha excluída. TRUNCATE TABLE remove os dados desalocando as páginas de dados usadas para armazenar os dados da tabela e registra somente as desalocações de página no log de transações.

• Normalmente são utilizados menos bloqueios.

  Quando a instrução é executada DELETE usando um bloqueio de linha, cada linha na tabela é bloqueada para exclusão. TRUNCATE TABLE sempre bloqueia a tabela (incluindo um bloqueio de esquema (SCH-M)) e a página, mas não cada linha.

• Sem exceção, nenhuma página é deixada na tabela.

  Depois que uma DELETE instrução é executada, a tabela ainda pode conter páginas vazias. Por exemplo, páginas vazias em um heap não podem ser desalocadas sem pelo menos um bloqueio de tabela exclusivo (LCK_M_X). Se a operação de exclusão não usar um bloqueio de tabela, a tabela (heap) conterá muitas páginas vazias. Para índices, a operação de exclusão pode deixar páginas vazias para trás, embora um processo de limpeza em segundo plano desaloce essas páginas rapidamente.

TRUNCATE TABLE Remove todas as linhas de uma tabela, mas a estrutura da tabela e suas colunas, restrições, índices e assim por diante, permanecem. Para remover a definição de tabela junto com seus dados, use a instrução DROP TABLE.

Se a tabela contiver uma coluna de identidade, o contador daquela coluna será redefinido no valor da semente definido para a coluna. Se nenhuma semente foi definida, o valor 1 padrão será usado. Para reter o contador de identidade, use DELETE em vez disso.

Uma TRUNCATE TABLE operação pode ser revertida dentro de uma transação.

Limitações

Você não pode usar TRUNCATE TABLE em tabelas que:

• São referenciados por uma FOREIGN KEY restrição. É possível truncar uma tabela que tenha uma chave estrangeira que referencie a ela mesma.

• Participam de uma exibição indexada.

• São publicadas com replicação transacional ou replicação de mesclagem.

• São temporais com controle de versão do sistema.

• São referenciados por uma EDGE restrição.

Para tabelas com uma ou mais dessas características, use a DELETE instrução.

TRUNCATE TABLE não é possível ativar um gatilho porque a operação não registra exclusões de linha individuais. Para obter mais informações, veja CREATE TRIGGER (Transact-SQL).

Em Azure Synapse Analytics e PDW (Analytics Platform System):

• TRUNCATE TABLE não é permitido dentro da EXPLAIN declaração.

• TRUNCATE TABLE não pode ser executado dentro de uma transação.

Truncar tabelas grandes

O Microsoft SQL Server tem a capacidade de remover ou truncar tabelas com mais de 128 extensões sem manter bloqueios simultâneos em todas as extensões necessárias para a remoção.

Permissões

A permissão mínima necessária é ALTER em table_name. TRUNCATE TABLE As permissões padrão para o proprietário da tabela, membros da função de servidor fixa sysadmin e as funções de banco de dados fixas db_owner e e db_ddladmin e não são transferíveis. Entretanto, você pode incorporar a instrução TRUNCATE TABLE dentro de um módulo, como um procedimento armazenado, e conceder permissões adequadas ao módulo por meio da cláusula EXECUTE AS.

Exemplos

a. Truncar uma tabela

O exemplo a seguir remove todos os dados da tabela JobCandidate. São incluídas instruções SELECT antes e depois da instrução TRUNCATE TABLE para comparar 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 partições de tabela

Aplica-se a: SQL Server 2016 (13.x) e versões posteriores.

O exemplo a seguir trunca as partições especificadas de uma tabela particionada. A sintaxe WITH (PARTITIONS (2, 4, 6 TO 8)) faz com que os número de partição 2, 4, 6, 7 e 8 sejam truncados.

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

C. Reverter uma operação truncada

O exemplo a seguir demonstra que uma TRUNCATE TABLE operação dentro de uma transação pode ser revertida.

1. Crie uma tabela de teste com três linhas.

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

2. Verifique os dados antes de trucar.

   SELECT * FROM TruncateTest;
   GO
   

3. Truncar a tabela dentro de uma transação e verificar o número de linhas.

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

   Você vê que a tabela está vazia.

4. Reverta a transação e verifique os dados.

   ROLLBACK TRANSACTION;
   GO
   
   SELECT * FROM TruncateTest;
   GO
   

   Você vê as três linhas.

5. Limpe a mesa.

   DROP TABLE TruncateTest;
   GO
   

Conteúdo relacionado

• DELETE (Transact-SQL)
• DROP TABLE (Transact-SQL)
• CREATE TABLE (Transact-SQL) IDENTITY (Propriedade)