DELETE (Transact-SQL)

Удаляет одну или несколько строк из таблицы или представления в SQL Server 2008.

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

Синтаксис

                [ 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
                    
                }

Аргументы

• WITH обобщенное_табличное_выражение
  Задает временный именованный результирующий набор, также называемый обобщенным табличным выражением, который определяется в области действия инструкции DELETE. Результирующий набор получается из инструкции SELECT. Дополнительные сведения см. в разделе WITH обобщенное_табличное_выражение (Transact-SQL).

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

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

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

• table_alias
  Псевдоним, заданный в предложении FROM table_source, представляющем таблицу или представление, строки которых будут удалены.

• server_name
  Имя связанного сервера, на котором расположена таблица или индексированное представление. Аргумент server_name можно задавать в виде имени связанного сервера или с помощью функции OPENDATASOURCE.

  Если аргумент server_name задается в виде связанного сервера, то необходимы аргументы database_name и schema_name. Если аргумент server_name задается с помощью функции OPENDATASOURCE, то аргументы database_name и schema_name могут применяться не ко всем источникам данных, в зависимости от возможностей поставщика OLE DB, который обращается к удаленному объекту. Дополнительные сведения см. в разделе Распределенные запросы.

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

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

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

  Представление, на которое ссылается аргумент 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).

• @table\_variable
  Задает переменную таблицы.

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

• FROM table_source
  Указывает дополнительное предложение FROM, которое используется для объединения целевого элемента table_or view_name с <table_source> для указания удаляемых строк. Это расширение Transact-SQL к команде DELETE может использоваться вместо вложенного запроса в предложении 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
  Имя переменной курсора. Переменная курсора должна содержать ссылку на курсор, обновления которого разрешены.

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

Рекомендации

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

Для возврата в клиентское приложение количества удаленных строк воспользуйтесь клиентским приложением. Дополнительные сведения см. в разделе @@ROWCOUNT (Transact-SQL).

Поддержка совместимости

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

Обработка ошибок

Можно реализовать обработку ошибок для инструкции DELETE, включив инструкцию в конструкцию TRY…CATCH. Дополнительные сведения см. в разделе Использование конструкции TRY...CATCH в языке Transact-SQL.

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

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

Совместимость

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

При удалении строки, содержащей столбец FILESTREAM, также удаляются и связанные с ней файлы файловой системы. Базовые файлы удаляются сборщиком мусора FILESTREAM. Дополнительные сведения см. в разделе Управление данными FILESTREAM с помощью языка Transact-SQL.

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

Ограничения

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

TOP нельзя использовать в инструкции UPDATE или DELETE, применяемой к секционированным представлениям.

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

Режим блокировки

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

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

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

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

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

Правила ведения журнала

Инструкция DELETE всегда выполняется с полной регистрацией.

Разрешения

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

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

Примеры

Категория	Используемые элементы синтаксиса
Базовый синтаксис	DELETE
Указание строк для удаления	предложение WHERE • TOP • предложение FROM и вложенные запросы • курсор • обобщенное табличное выражение WITH
Указание целевых объектов, не являющихся стандартными таблицами	Представления • табличные переменные
Удаление строк из удаленной таблицы	Связанный сервер • функция набора строк OPENQUERY • функция набора строк OPENDATASOURCE
Переопределение стандартных действий, выполняемых оптимизатором запросов, с помощью подсказок	Табличные подсказки
Сбор результатов инструкции DELETE	OUTPUT, предложение
Использование инструкции DELETE для других инструкций	Хранимая процедура • MERGE

Базовый синтаксис

В примерах из этого раздела показаны основные функции инструкции DELETE и используется минимально необходимый синтаксис.

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

USE AdventureWorks;
GO
DELETE FROM Sales.SalesPersonQuotaHistory;
GO

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

В примерах из этого раздела показаны способы ограничения числа удаляемых строк.

А. Ограничение количества строк с помощью предложения WHERE

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

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

Б. Использование предложения TOP

Число строк, удаляемых инструкцией DELETE, можно ограничивать с помощью предложения TOP. Если с инструкцией DELETE применяется предложение TOP (n), то операция удаления производится над n случайно выбранных строк.

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

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

Так, в следующем примере удаляется 20 случайных строк из таблицы PurchaseOrderDetail, имеющих дату ранее 1 июля 2002 г.

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

Если необходимо с помощью предложения TOP удалять строки в значимом хронологическом порядке, то вместе с ним в инструкции подзапроса выборки следует использовать ORDER BY. В следующем примере из таблицы удаляется PurchaseOrderDetail 10 строк, имеющих самую раннюю дату. Чтобы гарантировать удаление только 10 строк, столбец, указанный в инструкции подзапроса выборки (PurchaseOrderID) должен являться первичным ключом таблицы. Использование неключевого столбца в инструкции подзапроса выборки может привести к удалению более чем 10 строк, если указанный столбец содержит повторяющиеся значения.

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

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

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

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

Г. Использование вложенного запроса и расширения языка Transact-SQL FROM

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

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

Д. Использование обобщенного табличного выражения

В следующем примере удаляются только строки, возвращаемые обобщенным табличным выражением.

Указание целевых объектов, не являющихся стандартными таблицами

В примерах из этого раздела показаны методы удаления строк путем указания представления или табличной переменной.

Удаление строк из удаленной таблицы

В примерах из этого раздела показано, как удалять строки из удаленной целевой таблицы с помощью связанного сервера или функции набора строк, ссылающихся на удаленную таблицу.

Переопределение стандартных действий, выполняемых оптимизатором запросов, с помощью подсказок

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

Внимание!
Поскольку оптимизатор запросов SQL Server обычно выбирает наилучший план выполнения запроса, подсказки рекомендуется использовать только опытным разработчикам и администраторам баз данных в качестве последнего средства.

В следующем примере задается табличная подсказка READPAST. После задания READPAST пропускаются блокировки на уровнях строки и страницы, из-за чего компонент Database Engine не считывает строки и страницы, заблокированные другими транзакциями. Дополнительные сведения см. в разделе Табличные подсказки (Transact-SQL).

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

Сбор результатов инструкции DELETE

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

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

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

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

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

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

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

См. также

Справочник

CREATE TRIGGER (Transact-SQL)

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

SELECT (Transact-SQL)

TRUNCATE TABLE (Transact-SQL)

UPDATE (Transact-SQL)

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

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

Общие сведения о FILESTREAM