DELETE (Transact-SQL)

Изменения: 14 апреля 2006 г.

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

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

Синтаксис

[ WITH <common_table_expression> [ ,...n ] ]
DELETE 
    [ TOP ( expression ) [ PERCENT ] ] 
    [ FROM ] 
        { table_name [ WITH ( <table_hint_limited> [ ...n ] ) ]
        | view_name 
        | rowset_function_limited 
        | table_valued_function
    }
    [ <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
  Имя таблицы, из которой удаляются строки.

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

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

• view_name
  Имя представления, из которого удаляются строки.

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

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

• table_valued_function
  Может быть возвращающей табличное значение функцией. Дополнительные сведения о возвращающих табличное значение функциях см. в разделе Возвращающие табличное значение пользовательские функции.

• <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 для удаленных таблиц и локальных и удаленных секционированных представлений.

При необходимости удаления всех строк в таблице следует использовать инструкцию 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 AdventureWorks;
GO
DELETE FROM Sales.SalesPersonQuotaHistory;
GO

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

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

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

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

Следующий пример удаляет одну строку из таблицы 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

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

Следующий пример показывает использование расширения языка Transact-SQL для удаления записей из базовой таблицы, основанной на соединяющих или взаимосвязанных вложенных запросах. Первая инструкция DELETE показывает основанное на вложенных запросах решение, совместимое с SQL 2003, а вторая инструкция 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 с предложением TOP

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

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

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

Следующий пример удаляет все строки таблицы Sales.ShoppingCartItem. Предложение OUTPUT DELETED.* INTO @MyTableVar указывает, что результаты инструкции DELETE, т.е. все столбцы удаленных строк, должны быть возвращены в переменной @MyTableVartable. Далее приведены две инструкции SELECT, которые возвращают значения переменной @MyTableVar и результаты операции удаления в таблицу ShoppingCartItem.

USE AdventureWorks;
GO
DELETE Sales.ShoppingCartItem
    OUTPUT DELETED.* ;

--Verify all rows in the table have been deleted.
SELECT COUNT(*) AS [Rows in Table] FROM Sales.ShoppingCartItem;
GO

Ниже приведены результирующие наборы инструкций SELECT:

ShoppingCartItemID  ShoppingCartID  Quantity  ProductID 
------------------  --------------  --------  ---------
2                   14951           3         862
5                   20621           7         874
4                   20621           4         881

(3 row(s) affected)

Rows in Table 
------------- 
0

(1 row(s) affected)

Ж. Использование предложения 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)

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

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

Справка и поддержка

Получение помощи по SQL Server 2005

Журнал изменений

Версия	Журнал
14 апреля 2006 г.	Новое содержимое В раздел «Примечания» добавлен раздел «Удаление строк из кучи».