Partilhar via

MESA TRUNCATE (Transact-SQL)

Aplica-se a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsSistema de Plataforma de Análise (PDW)Armazém no Microsoft FabricBase 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 individuais. TRUNCATE TABLE é semelhante à declaração DELETE sem cláusula WHERE; no entanto, TRUNCATE TABLE é mais rápido e usa menos recursos do sistema e do log de transações.

Transact-SQL convenções de sintaxe

Syntax

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

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 literal. table_name não pode ser a função OBJECT_ID() ou uma variável.

COM ( PARTIÇÕES ( { <partition_number_expression> | <intervalo> } [ , ... n ] ) )

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

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

<partition_number_expression> podem ser especificados das seguintes formas:

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

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

  • Forneça intervalos e 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 devem ser alinhados (particionados na mesma função de partição).

Remarks

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

  • Menos espaço no log de transações é usado.

    A instrução DELETE 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 deslocalizando as páginas de dados usadas para armazenar os dados de tabela e índice e registra apenas os locais de negociação de página no log de transações.

  • Normalmente, são utilizadas menos fechaduras.

    Quando a instrução DELETE é executada 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 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 instrução DELETE é executada, a tabela ainda pode conter páginas vazias. Por exemplo, páginas vazias em uma pilha 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) pode conter muitas páginas vazias. Para índices, a DELETE instrução pode deixar páginas vazias para trás. Em seguida, um processo de limpeza em segundo plano deslocaliza 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 além de seus dados, use a instrução DROP TABLE.

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

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

No banco de dados SQL de malha, 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 os deallocations 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 deallocation. Isso significa que o espaço liberado por TRUNCATE TABLE pode 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

Não é possível usar TRUNCATE TABLE em tabelas que:

  • São referenciados por uma restrição de FOREIGN KEY. Você pode truncar uma tabela que tenha uma chave estrangeira que faça referência a si mesma.

  • Participe de uma exibição indexada.

  • São publicados usando replicação transacional ou replicação de mesclagem.

  • São temporais.

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

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

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, consulte CREATE TRIGGER (Transact-SQL).

No Azure Synapse Analytics and Analytics Platform System (PDW):

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

  • 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 As permissões padrão para o proprietário da tabela, os sysadmin membros da função de servidor fixa e as funções de banco de dados fixas db_ownerdb_ddladmin e não são transferíveis. No entanto, você pode incorporar a instrução TRUNCATE TABLE em um módulo, como um procedimento armazenado, e conceder permissões apropriadas ao módulo usando a cláusula EXECUTE AS.

Examples

A. Truncar uma tabela

O exemplo a seguir remove todos os dados da tabela JobCandidate. SELECT declarações são incluídas antes e depois da declaração TRUNCATE TABLE para comparar os 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 partições especificadas de uma tabela particionada. A sintaxe WITH (PARTITIONS (2, 4, 6 TO 8)) faz com que os números 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 operação de TRUNCATE TABLE 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ê as três linhas.

  5. Limpe a mesa.

    DROP TABLE TruncateTest;

Conteúdo relacionado

  • Last updated on