Compartilhar via

TRUNCADO TABLE (Transact-SQL)

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do AzureAzure Synapse AnalyticsPDW (Analytics Platform System)Warehouse no Microsoft FabricBanco de dados SQL no Microsoft Fabric

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 cláusulaDELETE; no entanto, WHERE é mais rápida e usa menos recursos de TRUNCATE TABLE log de transações e do sistema.

Convenções de sintaxe de Transact-SQL

Syntax

Sintaxe para SQL Server, Banco de Dados SQL do Azure, Banco de Dados SQL do Fabric

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 Microsoft Fabric, Azure Synapse Analytics e Parallel Data Warehouse.

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

Arguments

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 ( PARTIÇÕES ( { <partition_number_expression> | <gama> } [ , ... 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).

Remarks

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

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

    A DELETE instrução remove uma linha 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 do índice e registra apenas 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 da 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, zero páginas são deixadas na tabela ou em seus índices.

    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) poderá conter muitas páginas vazias. Para índices, a instrução DELETE pode deixar páginas vazias para trás. Um processo de limpeza em segundo plano desaloca essas páginas.

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 é usado. Para manter o contador de identidade, use DELETE em vez disso.

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

No banco de dados SQL do Fabric, truncar uma tabela exclui todos os dados espelhados do Fabric OneLake para essa tabela.

Realocação diferida

Quando uma tabela que usa 128 extensões ou mais é truncada, o Mecanismo de Banco de Dados adia as desalocações de página reais e seus bloqueios associados, até que a transação seja confirmada. O truncamento ocorre em duas fases separadas: lógica e física. Na fase lógica, as unidades de alocação existentes usadas pela tabela e seus índices são marcadas para desalocação e bloqueadas até que a transação seja confirmada. Na fase física, um processo em segundo plano remove as páginas marcadas para desalocação. Isso significa que o espaço liberado pode TRUNCATE TABLE não estar disponível para novas alocações imediatamente.

Se a recuperação acelerada do banco de dados estiver habilitada, o truncamento usará fases lógicas e físicas separadas, independentemente do número de extensões.

Limitations

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 linhas 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 na EXPLAIN declaração.

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

Permissions

A permissão mínima necessária é ALTER em table_name. TRUNCATE TABLE permissões padrão para o proprietário da tabela, membros da função de sysadmin servidor fixa e as db_ownerdb_ddladmin funções de banco de dados fixas 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.

Examples

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;

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

TRUNCATE TABLE HumanResources.JobCandidate;

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

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];

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

  2. Verifique os dados antes de truncar.

    SELECT ID FROM TruncateTest;

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

    BEGIN TRANSACTION;

TRUNCATE TABLE TruncateTest;

SELECT ID FROM TruncateTest;

    Você vê que a tabela está vazia.

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

    ROLLBACK TRANSACTION;

SELECT ID FROM TruncateTest;

    Você vê todas as três linhas.

  5. Limpe a tabela.

    DROP TABLE TruncateTest;

Conteúdo relacionado

  • Last updated on