DELETE (Transact-SQL)

Sintaxe

[ WITH common_table_expression [ ,...n ] ]
DELETE 
    [ TOP (expression ) [ PERCENT ] ] 
    [ FROM ] 
    { { table_alias
      | <object> 
      | rowset_function_limited 
      [ WITH (table_hint_limited [ ...n ] ) ] } 
      | @table_variable
    }
    [ OUTPUT Clause ]
    [ FROMtable_source [ ,...n ] ] 
    [ WHERE { search_condition 
            | { [ CURRENT OF 
                   { { [ GLOBAL ] cursor_name } 
                       | cursor_variable_name 
                   } 
                ]
              }
            } 
    ] 
    [ OPTION (Query Hint [ ,...n ] ) ] 
[; ]

<object> ::=
{ 
    [ server_name.database_name.schema_name. 
      | database_name. [ schema_name ] . 
      | schema_name.
    ]
    table_or_view_name 
}

Argumentos

• WITH common_table_expression
  Especifica o conjunto de resultados nomeado temporário, também conhecido como expressão de tabela comum, definido dentro do escopo da instrução DELETE. O conjunto de resultados é derivado de uma instrução SELECT. Para obter mais informações, consulte WITH common_table_expression (Transact-SQL).

• TOP ( expression) [ PERCENT ]
  Especifica o número ou a porcentagem de linhas aleatórias que serão excluídas. expression pode ser um número ou uma porcentagem de linhas. As linhas referenciadas na expressão TOP usada com DELETE não são organizadas em qualquer ordem.

  São necessários parênteses delimitando expression em TOP nas instruções INSERT, UPDATE, MERGE e DELETE. Para obter mais informações, consulte TOP (Transact-SQL).

• FROM
  Uma palavra-chave opcional que pode ser usada entre a palavra-chave DELETE e o objeto de destino.

• table_alias
  O alias especificado na cláusula FROM table_source representando a tabela ou exibição na qual as linhas devem ser excluídas.

• server_name
  O nome do servidor vinculado no qual a tabela ou exibição está localizada. server_name pode ser especificado como um nome de servidor vinculado ou usando-se a função OPENDATASOURCE.

  Quando server_name é especificado como um servidor vinculado, database_name e schema_name são necessários. Quando server_name é especificado com OPENDATASOURCE, database_name e schema_name podem não se aplicar a todas as fontes de dados e podem estar sujeitos aos recursos do provedor OLE DB que acessa o objeto remoto. Para obter mais informações, consulte Consultas distribuídas.

• database_name
  O nome do banco de dados.

• schema_name
  O nome do esquema ao qual a tabela ou exibição pertence.

• table_or view_name
  O nome da tabela ou exibição da qual as linhas serão removidas.

  A exibição referenciada por table_or_view_name deve ser atualizável e referenciar exatamente uma tabela base na cláusula FROM da exibição. Para obter mais informações sobre exibições atualizáveis, consulte CREATE VIEW (Transact-SQL).

• rowset_function_limited
  A função OPENQUERY ou OPENROWSET, sujeita aos recursos de provedor. Para obter mais informações sobre os recursos exigidos pelo provedor, consulte Requisitos de UPDATE e DELETE para provedores OLE DB.

• WITH (table_hint_limited [... n] )
  Especifica uma ou mais dicas de tabela permitidas para uma tabela de destino. A palavra-chave WITH e parênteses são necessários. NOLOCK e READUNCOMMITTED não são permitidos. Para obter mais informações sobre dicas de tabela, consulte Dicas de tabela (Transact-SQL).

• @table\_variable
  Especifica uma variável de tabela.

• <OUTPUT_Clause>
  Retorna linhas excluídas, ou expressões baseadas nelas, como parte da operação de exclusão. A cláusula OUTPUT não tem suporte em instruções DML destinadas a exibições particionadas locais, exibições particionadas distribuídas, tabelas remotas ou exibições remotas. Para obter mais informações, consulte cláusula OUTPUT (Transact-SQL).

• FROM table_source
  Especifica uma cláusula FROM adicional que pode ser usada para unir a table_or view_name de destino a <table_source> para identificar linhas a serem removidas. Essa extensão Transact-SQL para DELETE pode ser usada no lugar de uma subconsulta na cláusula WHERE.

  Para obter mais informações, consulte FROM (Transact-SQL).

• WHERE
  Especifica as condições usadas para limitar o número de linhas a serem excluídas. Se uma cláusula WHERE não for fornecida, DELETE removerá todas as linhas da tabela.

  Há duas formas de excluir operações com base no que é especificado na cláusula WHERE:

  • Exclusões pesquisadas especificam um critério de pesquisa para qualificar as linhas a serem excluídas. Por exemplo, WHERE column_name = value.

  • Exclusões posicionadas usam a cláusula CURRENT OF para especificar um cursor. A operação de exclusão ocorre na posição atual do cursor. Isso pode ser mais preciso do que uma instrução DELETE pesquisada que usa uma cláusula WHERE search_condition para qualificar as linhas a serem excluídas. Uma instrução DELETE pesquisada excluirá várias linhas se o critério de pesquisa não identificar exclusivamente uma única linha.

• search_condition
  Especifica os critérios de restrição para as linhas a serem excluídas. Não há nenhum limite para o número de predicados que podem ser incluídos em um critério de pesquisa. Para obter mais informações, consulte Critério de pesquisa (Transact-SQL).

• CURRENT OF
  Especifica que DELETE é executado na posição atual do cursor especificado.

• GLOBAL
  Especifica que cursor_name se refere a um cursor global.

• cursor_name
  O nome do cursor aberto do qual a busca é feita. Se um cursor global e um cursor local com o mesmo nome cursor_name existirem, este argumento fará referência ao cursor global se GLOBAL for especificado; caso contrário, fará referência ao cursor local. O cursor deve permitir atualizações.

• cursor_variable_name
  O nome de uma variável de cursor. A variável de cursor deve fazer referência a um cursor que permita atualizações.

• OPTION (query_hint [ ,... n] )
  Palavras-chave que indicam que são usadas dicas de otimizador para personalizar a forma como o Mecanismo de Banco de Dados processa a instrução. Para obter mais informações, consulte dicas de consulta (Transact-SQL).

Práticas recomendadas

Para excluir todas as linhas de uma tabela, use TRUNCATE TABLE. TRUNCATE TABLE é mais rápido que DELETE e usa menos recursos do sistema e do log de transações.

Use a função @@ROWCOUNT para retornar o número de linhas excluídas ao aplicativo cliente. Para obter mais informações, consulte @@ROWCOUNT (Transact-SQL).

Suporte de compatibilidade

O uso de SET ROWCOUNT não afetará as instruções DELETE na próxima versão do SQL Server. Não use SET ROWCOUNT com instruções DELETE em um novo trabalho de desenvolvimento, e modifique os aplicativos que o utilizam para que usem a sintaxe TOP.

Manipulação de erros

Você pode implementar a manipulação de erros para a instrução DELETE especificando essa instrução em uma construção TRY…CATCH. Para obter mais informações, consulte Usando TRY...CATCH na Transact-SQL.

A instrução DELETE poderá falhar se violar um gatilho ou tentar remover uma linha referenciada por dados em outra tabela com uma restrição FOREIGN KEY. Se DELETE remover várias linhas e qualquer uma das linhas removidas violar um gatilho ou uma restrição, a instrução será cancelada, um erro será retornado e nenhuma linha será removida.

Quando uma instrução DELETE encontra um erro aritmético (estouro, divisão por zero ou um erro de domínio) que ocorre durante a avaliação de expressão, o Mecanismo de Banco de Dados trata esses erros como se SET ARITHABORT estivesse definido como ON. O resto do lote é cancelado e uma mensagem de erro é retornada.

Interoperabilidade

DELETE poderá ser usado no corpo de uma função definida pelo usuário se o objeto modificado for uma variável de tabela.

Ao excluir uma linha que contém uma coluna FILESTREAM, você também exclui seus arquivos subjacentes do sistema de arquivos. Os arquivos subjacentes são removidos pelo coletor de lixo do FILESTREAM. Para obter mais informações, consulte Gerenciando dados de FILESTREAM usando o Transact-SQL.

A cláusula FROM não pode ser especificada em uma instrução DELETE quer referencie, direta ou indiretamente, uma exibição com um gatilho INSTEAD OF definido. Para obter mais informações sobre gatilhos INSTEAD OF, consulte CREATE TRIGGER (Transact-SQL).

Limitações e restrições

Quando TOP é usada como DELETE, as linhas referenciadas não são organizadas em nenhuma ordem, e a cláusula ORDER BY não pode ser especificada diretamente nessa instrução. Se você precisar usar TOP para excluir linhas em uma ordem cronológica significativa, será preciso usar TOP junto com uma cláusula ORDER BY em uma instrução de subseleção. Consulte a seção Exemplos mais adiante neste tópico.

Não é possível usar TOP em uma instrução DELETE em exibições particionadas.

A configuração da opção SET ROWCOUNT é ignorada para instruções DELETE em tabelas remotas e exibições locais e remotas particionadas.

Comportamento de bloqueio

Quando linhas são excluídas de um heap, o Mecanismo de Banco de Dados pode usar bloqueio de linha ou página para a operação. Como resultado, as páginas que ficaram vazias pela operação de exclusão permanecem alocadas no heap. Quando páginas vazias não são desalocadas, o espaço associado não pode ser usado novamente por outros objetos do banco de dados. Para excluir linhas em um heap e desalocar páginas, use um dos seguintes métodos.

• Especifique a dica TABLOCK na instrução DELETE. Usando a dica TABLOCK faz com que a operação de exclusão faça um bloqueio compartilhado na tabela em vez de um bloqueio de linha ou página. Isso permite que as páginas sejam desalocadas. Para obter mais informações sobre a dica TABLOCK, consulte Dicas de tabela (Transact-SQL).

• Use TRUNCATE TABLE se todas as linhas forem excluídas da tabela.

• Crie um índice clusterizado no heap antes de excluir as linhas. Você pode cancelar o índice clusterizado depois que as linhas forem excluídas. Esse método consome mais tempo do que os métodos anteriores e usa mais recursos temporários.

Para obter mais informações sobre bloqueios, consulte Bloqueios no mecanismo de banco de dados.

Comportamento de registro em log

A instrução DELETE é sempre totalmente registrada.

Permissões

São necessárias permissões DELETE na tabela de destino. Também serão necessárias permissões SELECT se a instrução tiver uma cláusula WHERE.

As permissões DELETE seguem o padrão para membros da função de servidor fixa sysadmin, das funções de banco de dados fixas db_owner e db_datawriter e do proprietário da tabela. Os membros das funções sysadmin, db_owner e db_securityadmin e o proprietário da tabela podem transferir permissões para outros usuários.

Exemplos

Sintaxe básica

Os exemplos nesta seção demonstram a funcionalidade básica da instrução DELETE usando a sintaxe mínima necessária.

O exemplo a seguir exclui todas as linhas da tabela SalesPersonQuotaHistory porque uma cláusula WHERE não é usada para limitar o número de linhas excluídas.

USE AdventureWorks;
GO
DELETE FROM Sales.SalesPersonQuotaHistory;
GO

Especificando linhas a serem excluídas

Os exemplos nesta seção demonstram maneiras de limitar o número de linhas excluídas.

A. Usando a cláusula WHERE para limitar linhas

USE AdventureWorks;
GO
DELETE FROM Production.ProductCostHistory
WHERE StandardCost > 1000.00;
GO

B. Usando a cláusula TOP

USE AdventureWorks;
GO
DELETE TOP (2.5) PERCENT 
FROM Production.ProductInventory;
GO

USE AdventureWorks;
GO
DELETE TOP (20) 
FROM Purchasing.PurchaseOrderDetail
WHERE DueDate < '20020701';
GO

USE AdventureWorks;
GO
DELETE FROM Purchasing.PurchaseOrderDetail
WHERE PurchaseOrderDetailID IN
   (SELECT TOP 10 PurchaseOrderDetailID 
    FROM Purchasing.PurchaseOrderDetail 
    ORDER BY DueDate ASC);
GO

C. Usando um cursor para excluir a linha atual

USE AdventureWorks;
GO
DECLARE complex_cursor CURSOR FOR
    SELECT a.EmployeeID
    FROM HumanResources.EmployeePayHistory AS a
    WHERE RateChangeDate <> 
         (SELECT MAX(RateChangeDate)
          FROM HumanResources.EmployeePayHistory AS b
          WHERE a.EmployeeID = b.EmployeeID) ;
OPEN complex_cursor;
FETCH FROM complex_cursor;
DELETE FROM HumanResources.EmployeePayHistory
WHERE CURRENT OF complex_cursor;
CLOSE complex_cursor;
DEALLOCATE complex_cursor;
GO

D. Usando uma subconsulta e a extensão Transact-SQL FROM

-- SQL-2003 Standard subquery

USE AdventureWorks;
GO
DELETE FROM Sales.SalesPersonQuotaHistory 
WHERE SalesPersonID IN 
    (SELECT SalesPersonID 
     FROM Sales.SalesPerson 
     WHERE SalesYTD > 2500000.00);
GO

-- Transact-SQL extension
USE AdventureWorks;
GO
DELETE FROM Sales.SalesPersonQuotaHistory 
FROM Sales.SalesPersonQuotaHistory AS spqh
    INNER JOIN Sales.SalesPerson AS sp
    ON spqh.SalesPersonID = sp.SalesPersonID
WHERE sp.SalesYTD > 2500000.00;
GO

E. Usando uma expressão de tabela comum

Especificando objetos de destino diferentes de tabelas padrão

Os exemplos nesta seção demonstram como excluir linhas especificando uma exibição ou variável de tabela.

Excluindo linhas em uma tabela remota

Os exemplos nesta seção demonstram como excluir linhas em uma tabela de destino remota usando um servidor vinculado ou uma função de conjunto de linhas para referenciar a tabela remota.

Substituindo o comportamento padrão do otimizador de consulta usando dicas

Os exemplos nesta seção demonstram como usar dicas de tabela e dicas de consulta para substituir temporariamente o comportamento padrão do otimizador de consulta durante o processamento da instrução DELETE.

Cuidado
Como o otimizador de consulta do SQL Server normalmente seleciona o melhor plano de execução para uma consulta, é recomendável que desenvolvedores e administradores de banco de dados experientes usem as dicas apenas como um último recurso.

O exemplo a seguir usa a dica de tabela especificada READPAST. Quando READPAST é especificada, os bloqueios em nível de linha e de página são ignorados, o que faz com que o Mecanismo de Banco de Dados não leia linhas e páginas bloqueadas por outras transações. Para obter mais informações, consulte Dicas de tabela (Transact-SQL).

USE AdventureWorks;
GO
DELETE TOP(1) dbo.DatabaseLog WITH (READPAST)
OUTPUT deleted.*
WHERE DatabaseLogID = 7;
GO

Capturando os resultados da instrução DELETE

Os exemplos nesta seção demonstram como usar a cláusula OUTPUT para retornar informações ou expressões baseadas em cada linha afetada por uma instrução DELETE. Esses resultados podem ser retornados ao aplicativo de processamento para uso em mensagens de confirmação, arquivamentos e outros requisitos similares de aplicativo.

A. Usando DELETE com a cláusula OUTPUT

USE AdventureWorks;
GO
DELETE Sales.ShoppingCartItem
OUTPUT DELETED.* 
WHERE ShoppingCartID = 20621;

--Verify all rows in the table that match the WHERE clause have been deleted.
SELECT COUNT(*) AS [Rows in Table] FROM Sales.ShoppingCartItem WHERE ShoppingCartID = 20621;
GO

B. Usando OUTPUT com from_table_name em uma instrução DELETE

USE AdventureWorks;
GO
DECLARE @MyTableVar table (
    ProductID int NOT NULL, 
    ProductName nvarchar(50)NOT NULL,
    ProductModelID int NOT NULL, 
    PhotoID int NOT NULL);

DELETE Production.ProductProductPhoto
OUTPUT DELETED.ProductID,
       p.Name,
       p.ProductModelID,
       DELETED.ProductPhotoID
    INTO @MyTableVar
FROM Production.ProductProductPhoto AS ph
JOIN Production.Product as p 
    ON ph.ProductID = p.ProductID 
    WHERE p.ProductModelID BETWEEN 120 and 130;

--Display the results of the table variable.
SELECT ProductID, ProductName, ProductModelID, PhotoID 
FROM @MyTableVar
ORDER BY ProductModelID;
GO

Consulte também

Referência

Conceitos