DELETE (Transact-SQL)

Удаляет строки из таблиц и представлений.

Синтаксические обозначения в Transact-SQL

Синтаксис

[ WITH <common_table_expression> [ ,...n ] ]
DELETE 
    [ TOP ( expression ) [ PERCENT ] ] 
    [ FROM ] 
    { <object> | rowset_function_limited 
      [ WITH ( <table_hint_limited> [ ...n ] ) ]
    }
    [ <OUTPUT Clause> ]
    [ FROM <table_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 
}

Аргументы

• WITH <common_table_expression>
  Задает временный именованный результирующий набор, также называемый обобщенным табличным выражением, который определяется в области действия инструкции DELETE. Результирующий набор получается из инструкции SELECT.

  Обобщенные табличные выражения также можно использовать в инструкциях SELECT, INSERT, UPDATE и CREATE VIEW. Дополнительные сведения см. в разделе WITH обобщенное_табличное_выражение (Transact-SQL).

• TOP (expression) [ PERCENT ]
  Задает число или процент случайных строк для удаления. Выражение expression может быть либо числом, либо процентом строк. Строки, на которые ссылается выражение TOP, используемое с инструкциями INSERT, UPDATE и DELETE, не упорядочиваются.

  Разделение круглыми скобками выражения expression в выражении TOP требуется в инструкциях INSERT, UPDATE и DELETE. Дополнительные сведения см. в разделе TOP (Transact-SQL).

• FROM
  Необязательное ключевое слово, которое можно использовать между ключевым словом DELETE и целевым аргументом table_or_view_name или rowset_function_limited.

• server_name
  Имя сервера (с использованием имени связанного сервера или функции OPENDATASOURCE в качестве имени сервера), на котором расположена таблица или представление. Если указан аргумент server_name, также необходимо указать аргументы database_name и schema_name.

• database_name
  Имя базы данных.

• schema_name
  Имя схемы, которой принадлежит таблица или представление.

• table_or view_name
  Имя таблицы или представления, откуда удаляются строки.

  Переменную table в пределах ее области действия также можно использовать в качестве источника таблицы в инструкции DELETE.

  Представление, на которое ссылается аргумент table_or_view_name , должно быть обновляемым и ссылаться ровно на одну базовую таблицу в предложении FROM данного представления. Дополнительные сведения об обновляемых представлениях см. в разделе CREATE VIEW (Transact-SQL).

• rowset_function_limited
  Функция OPENQUERY или OPENROWSET в зависимости от возможностей поставщика. Дополнительные сведения о возможностях, требуемых для поставщика см. в разделе Требования UPDATE и DELETE для поставщиков OLE DB.

• WITH ( <table_hint_limited> [... n] )
  Задает одну или несколько табличных подсказок, разрешенных для целевой таблицы. Ключевое слово WITH и круглые скобки обязательны. Использование ключевых слов NOLOCK и READUNCOMMITTED запрещено. Дополнительные сведения о табличных подсказках см. в разделе Табличные подсказки (Transact-SQL).

• <OUTPUT_Clause>
  Возвращает удаленные строки или выражения, основанные на них, как часть операции DELETE. Предложение OUTPUT не поддерживается ни в каких инструкциях DML, направленных на представления и удаленные таблицы. Дополнительные сведения см. в разделе Предложение OUTPUT (Transact-SQL).

• FROM <table_source>
  Задает дополнительное предложение FROM. Это расширение языка Transact-SQL для инструкции DELETE позволяет задавать данные из <table_source> и удалять соответствующие строки из таблицы в первом предложении FROM.

  Это расширение, в котором задается соединение, может быть использовано вместо вложенного запроса в предложении WHERE для указания удаляемых строк.

  Дополнительные сведения см. в разделе FROM (Transact-SQL).

• WHERE
  Указывает условия, используемые для ограничения числа удаляемых строк. Если предложение WHERE не указывается, инструкция DELETE удаляет все строки из таблицы.

  Предусмотрено два вида операций удаления в соответствии с тем, что указывается в предложении WHERE.

  • Операции удаления с поиском указывают условие поиска для уточнения строк, которые будут удалены. Например, WHERE column_name = value.

  • Операции удаления по позиции используют предложение CURRENT OF для указания курсора. Удаление осуществляется в текущей позиции курсора. Эта операция может быть более точной, чем инструкция DELETE по найденному, которая использует предложение WHERE search_condition для указания удаляемых строк. Инструкция DELETE по найденному удаляет несколько строк, если условие поиска не определяет уникально одну строку.

• <search_condition>
  Указывает ограничивающие условия для удаляемых строк. Количество предикатов, которое может содержать условие поиска, не ограничено. Дополнительные сведения см. в разделе Условие поиска (Transact-SQL).

• CURRENT OF
  Указывает выполнение инструкции DELETE в текущей позиции указанного курсора.

• GLOBAL
  Указывает, что аргумент cursor_name ссылается на глобальный курсор.

• cursor_name
  Имя открытого курсора, из которого производится выборка. Если существует как глобальный, так и локальный курсор с именем cursor_name, то, когда указывается GLOBAL, этот аргумент указывает на глобальный курсор; в противном случае — на локальный курсор. Курсор должен позволять производить обновления.

• cursor_variable_name
  Имя переменной курсора. Переменная курсора должна содержать ссылку на курсор, обновления которого разрешены.

• OPTION ( <query_hint> [ ,... n] )
  Ключевые слова, показывающие, что подсказки оптимизатора применяются при настройке способа обработки инструкции компонентом Database Engine. Дополнительные сведения см. в разделе Подсказки в запросах (Transact-SQL).

Замечания

Инструкцию DELETE можно использовать в теле пользовательской функции, если изменяемым объектом является переменная table.

При выполнении инструкции DELETE может произойти ошибка, если она нарушает триггер или пытается удалить строку, на которую ссылаются данные в другой таблице с помощью ограничения FOREIGN KEY. Если инструкция DELETE удаляет несколько строк и одна из удаленных строк нарушает триггер или ограничение, то эта инструкция отменяется, т.е. возвращается ошибка и строки не удаляются.

В случае арифметической ошибки (переполнение, деление на ноль или выход за пределы допустимых значений), возникающей в ходе вычисления выражения при выполнении инструкции DELETE, компонент Database Engine будет обрабатывать эти ошибки, как если бы параметр SET ARITHABORT имел значение ON. Оставшаяся часть пакетной операции отменяется и возвращается сообщение об ошибке.

Значение параметра SET ROWCOUNT не учитывается в инструкциях DELETE для удаленных таблиц и локальных и удаленных секционированных представлений.

Использование инструкции SET ROWCOUNT не будет оказывать влияния на инструкции DELETE, INSERT и UPDATE в следующей версии SQL Server. В новых разработках следует избегать совместного использования инструкции SET ROWCOUNT с инструкциями DELETE, INSERT и UPDATE. Также необходимо запланировать внесение изменений в используемые приложения. Вместо нее рекомендуется использовать предложение TOP.

При необходимости удаления всех строк в таблице следует использовать инструкцию DELETE без предложения WHERE либо использовать инструкцию TRUNCATE TABLE. Инструкция TRUNCATE TABLE выполняется быстрее, чем инструкция DELETE, и использует меньше системных ресурсов и ресурсов журнала транзакций.

Удаление строк из кучи

Когда строки удаляются из кучи, компонент Database Engine может использовать строку или страницу блокировки для операции. В результате пустые страницы, в которых выполняются операции удаления, остаются размещенными для кучи. Если их не освободить, занимаемое ими место не может быть использовано под другие объекты базы данных.

Чтобы удалить из кучи строки и освободить страницы, воспользуйтесь одним из следующих методов.

• Задайте подсказку TABLOCK в инструкции DELETE. Это приведет к тому, что при выполнении операции удаления в таблице будет установлена совмещаемая блокировка, а не блокировка строки или страницы, что позволит освободить страницы. Дополнительные сведения о подсказке TABLOCK см. в разделе Табличные подсказки (Transact-SQL).

• Если из таблицы удаляются все строки, пользуйтесь инструкцией TRUNCATE TABLE.

• Перед удалением строк создайте в куче кластеризованный индекс. Потом его можно будет удалить. Этот метод потребует больше времени и потребляет больше временных ресурсов.

Дополнительные сведения о блокировках см. в разделе Блокировка в компоненте Database Engine.

Использование триггера INSTEAD OF в операциях DELETE

Если триггер INSTEAD OF определен в операциях DELETE над таблицей или представлением, то он выполняется вместо инструкции DELETE. В предыдущих версиях SQL Server имеется поддержка только триггеров AFTER для DELETE и других инструкций изменения данных. Предложение FROM нельзя указывать в инструкции DELETE, ссылающейся (прямо или косвенно) на представление, в котором указан триггер INSTEAD OF. Дополнительные сведения о триггерах INSTEAD OF см. в разделе CREATE TRIGGER (Transact-SQL).

Разрешения

Разрешения DELETE необходимы для целевой таблицы. Разрешения SELECT также необходимы, если инструкция содержит предложение WHERE.

Разрешения DELETE назначаются по умолчанию членам предопределенной роли сервера sysadmin, предопределенных ролей базы данных db_owner и db_ddladmin, а также владельцу таблицы. Члены ролей sysadmin, db_owner и db_securityadmin, а также владелец таблицы могут передавать разрешения другим пользователям.

Примеры

А. Использование инструкции DELETE с предложением WHERE

Следующий пример удаляет все строки из таблицы SalesPersonQuotaHistory, поскольку не указано предложение WHERE, ограничивающее количество удаляемых строк.

USE AdventureWorks2008R2;
GO
DELETE FROM Sales.SalesPersonQuotaHistory;
GO

Б. Использование инструкции DELETE для набора строк

Следующий пример удаляет все строки таблицы ProductCostHistory, у которых значение в столбце StandardCost больше чем 1000.00.

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

В. Использование инструкции DELETE для текущей строки курсора

Следующий пример удаляет одну строку из таблицы EmployeePayHistory с помощью курсора complex_cursor. Операция удаления затрагивает только одну строку, выбранную в данный момент курсором.

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

Г. Использование инструкции DELETE на основе вложенного запроса и с помощью расширения языка Transact-SQL

Следующий пример показывает использование расширения языка Transact-SQL для удаления записей из базовой таблицы, основанной на соединяющих или взаимосвязанных вложенных запросах. В первой инструкции DELETE показано основанное на вложенных запросах и совместимое с ISO решение, а во второй инструкции DELETE используется расширение языка Transact-SQL. Оба запроса удаляют строки из таблицы SalesPersonQuotaHistory, основанной на сведениях о продажах за текущий год, хранящихся в таблице SalesPerson.

-- SQL-2003 Standard subquery

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

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

GO

Д. Использование инструкции DELETE с предложением TOP

Следующий пример удаляет 2.5 процента строк (27 строк) из таблицы ProductInventory.

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

Е. Использование инструкции DELETE с предложением OUTPUT

Следующий пример демонстрирует способы сохранения результатов инструкции DELETE в табличную переменную.

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

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

Ж. Использование предложения OUTPUT с аргументом from_table_name в инструкции DELETE

Следующий пример удаляет строки в таблице ProductProductPhoto на основе критерия поиска, указанного в предложении FROM инструкции DELETE. Предложение OUTPUT возвращает столбцы из таблицы, в которой проводится удаление, DELETED.ProductID, DELETED.ProductPhotoID и столбцы из таблицы Product. Оно используется в предложении FROM для указания удаляемых строк.

USE AdventureWorks2008R2;
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

См. также

Справочник

CREATE TRIGGER (Transact-SQL)

Инструкция INSERT (Transact-SQL)

SELECT (Transact-SQL)

TRUNCATE TABLE (Transact-SQL)

UPDATE (Transact-SQL)

WITH обобщенное_табличное_выражение (Transact-SQL)

Основные понятия

Приступая к работе с хранилищем FILESTREAM

Другие ресурсы

Удаление данных в таблице