Assim como o comando DELETE, o TRUNCATE TABLE remove TODOS os registros de uma tabela. A grande diferença é que o DELETE remove uma linha por vez e registra uma entrada no transaction log do database para cada registro excluído. Para grandes tabelas isto pode ser um problema pois muitas das vezes o DELETE é cancelado devido a falta de espaço no transaction log do database. Quando isso ocorre, o SQL Server registra a seguinte mensagem em seu Errolog:
Error: 9002, Severity: 17, State: 6
The log file for database ‘DBXXX’ is full. Back up the transaction log for the database to free up some log space..
Já o TRUNCATE TABLE exclui os dados simplesmente por desalocar as páginas de dados usadas para armazenar estes dados. Isto faz com ele seja mais rápido e neccessite de menos recursos de sitema e espaço no transaction log.
Algumas outras vantagens do TRUNCATE TABLE em relação ao DELETE são:
1. Reset de coluna IDENTITY: quando se usa o TRUNCATE para excluir os dados de uma tabela, o contador usado por uma coluna do tipo IDENTITY (autonumeração) é reiniciado.
2. Menor uso de Locks: quando usamos o DELETE, para excluir o registro um lock é atribuído a cada linha da tabela. O TRUNCATE atribui lock à tabela e páginas mas não a cada linha.
Como podemos observar, a utilização do TRUNCATE TABLE nos proporciona algumas vantagens, principalmente quando queremos limpar tabelas que possuem grandes volumes de dados. No entanto, como nem tudo são flores, uma de suas maiores restrições está relacioda a permissões. Para usá-lo o usuário precisa ser o owner da tabela ou pertencer a uma das roles: sysadmin, db_owner, db_ddladmin.
Outras restrições para o uso do TRUNCATE TABLE são:
1. Como ele não registra no log cada registro excluído, ele não pode ativar uma trigger
2. Não pode ser usado em tabelas que são referenciadas por uma FOREIGN KEY constraint.
3. Não pode ser usado em tabelas que participam de indexed views.
4. Não pode ser usado em tabelas que participam de replicação transacional ou merge
Como vimos, infelizmente o uso do TRUNCATE TABLE não está disponível para qualquer usuário de uma base e é aí que entra a procedure sp_truncatetable que estarei descrevendo neste artigo.
A PROCEDURE
A procedure sp_truncatetable nasceu justamente devido a necessidade de permitir o uso do TRUNCATE TABLE a qualquer usuário de uma base. Ao executá-la as seguintes ações são realizadas:
1. Verifica se a tabela existe
2. Verifica se o usuário possui permissão de DELETE na tabela
3. Verifica se o usuário possui permissão de DELETE negada (DENY) na tabela
4. Verifica se o usuário é membro de alguma role (grupo) que possui permissão de DELETE na tabela
5. Verifica se o usuário é membro de alguma role (grupo) que possui permissão de DELETE negada (DENY) na tabela.
6. Usa a procedure de sistema xp_cmdshell para executar o TRUNCATE TABLE
As restrições para o uso da sp_truncatetable são as seguintes:
1. O usuário deve ter permissão de DELETE na tabela ou ser membro de alguma role (grupo) que a tenha.
2. A procedure deve ser criada no database master
3. Usuários que NÂO são membros da fixed server role sysadmin devem ter permissão para executar a procedure de sistema xp_cmdshell. Para saber mais, consulte no Books Online por “xp_cmdshell“.
Download sp_truncatetable.sql
EXECUTANDO A PROCEDURE
Após criar a procedure no database master, atribua a permissão de DELETE na tabela para um usuário ou role do qual este seja membro. Depois, execute a procedure passando como referência os dois parâmetros obrigatórios:
@dbname: Nome do database que possui a tabela a ser truncada
@tbname: Nome da tabela a ser truncada
Sintaxe:
sp_truncatetable ‘database’,’tabela’
Exemplos:
DECLARE @return_status tinyint
EXEC @return_status = sp_truncatetable ‘pubs’,’tb_delete’
SELECT ‘Return Status’ = @return_status
GO
OU
DECLARE @return_status tinyint
EXEC @return_status = master..sp_truncatetable ‘pubs’,’tb_delete’
SELECT ‘Return Status’ = @return_status
GO
Como retorno, quatro possíveis status podem ser obtidos:
0: TRUNCATE TABLE efetuado com sucesso
1: Falha na execução do truncate (possivelmente problema relacionado à xp_cmdshell)
2: A tabela informada não existe
3: O usuário tem permissão de DELETE negada (DENY) na tabela
4: O usuário não tem permissão de DELETE na tabela
Bom pessoal, é isso aí. Espero que a sp_truncatetable seja de grande utilidade para todos. Qualquer problema relacionado à procedure abra um post em nosso fórum. E como é sempre bom lembrar, TESTE, TESTE e TESTE !!! Esta procedure foi testada com o SQL Server 2000 e 2005.
Um abraço a todos
Nilton Pinheiro