DELETE(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 common_table_expression
  DELETE 문 범위 내에 정의되고 공통 테이블 식이라고도 하는 명명된 임시 결과 집합을 지정합니다. 결과 집합은 SELECT 문에서 생성됩니다. 자세한 내용은 WITH common_table_expression(Transact-SQL)을 참조하십시오.

• TOP ( expression) [ PERCENT ]
  삭제될 임의 행의 개수 또는 백분율(%)을 지정합니다. expression은 숫자이거나 행의 비율일 수 있습니다. DELETE와 함께 사용된 TOP 식에서 참조된 행은 어떠한 순서로도 정렬되지 않습니다.

  INSERT, UPDATE, MERGE 및 DELETE 문에서는 괄호를 사용하여 TOP의 expression을 구분해야 합니다. 자세한 내용은 TOP(Transact-SQL)을 참조하십시오.

• FROM
  DELETE 키워드와 대상 개체 사이에서 선택적으로 사용할 수 있는 키워드입니다.

• table_alias
  행을 삭제할 테이블 또는 뷰를 나타내는 FROM table_source 절에 지정되는 별칭입니다.

• server_name
  테이블이나 뷰가 위치한 연결된 서버의 이름입니다. server_name 은 연결된 서버 이름으로 지정되거나 OPENDATASOURCE 함수로 지정될 수 있습니다.

  server_name을 연결된 서버로 지정할 경우에는 database_name 및 schema_name이 필요합니다. OPENDATASOURCE를 사용하여 server_name을 지정할 경우 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 함수입니다. 공급자에 필요한 기능에 대한 자세한 내용은 OLE DB 공급자를 위한 UPDATE 및 DELETE 요구 사항을 참조하십시오.

• WITH (table_hint_limited [... n] )
  대상 테이블에 허용되는 하나 이상의 테이블 힌트를 지정합니다. WITH 키워드와 괄호가 필요합니다. NOLOCK 및 READUNCOMMITTED는 허용되지 않습니다. 테이블 힌트에 대한 자세한 내용은 테이블 힌트(Transact-SQL)를 참조하십시오.

• @table\_variable
  테이블 변수를 지정합니다.

• <OUTPUT_Clause>
  삭제된 행 또는 삭제된 행 기반의 식을 삭제 작업의 일부로 반환합니다. 로컬 분할 뷰, 분산형 분할 뷰, 원격 테이블 또는 원격 뷰를 대상으로 하는 DML 문에서는 OUTPUT 절이 지원되지 않습니다. 자세한 내용은 OUTPUT 절(Transact-SQL)을 참조하십시오.

• FROM table_source
  제거할 행을 식별하기 위해 대상 table_or view_name을 <table_source>에 조인하는 데 사용할 수 있는 추가 FROM 절을 지정합니다. DELETE에 대한 이 Transact-SQL 확장을 WHERE 절에서 하위 쿼리 대신 사용할 수 있습니다.

  자세한 내용은 FROM(Transact-SQL)을 참조하십시오.

• WHERE
  삭제될 행의 개수를 제한하는 조건을 지정합니다. WHERE 절을 지정하지 않으면 DELETE는 테이블의 모든 행을 제거합니다.

  WHERE 절에 지정된 내용에 따라 두 가지 형식의 삭제 작업이 있습니다.

  • 검색 결과 삭제는 삭제할 행을 한정하는 검색 조건을 지정합니다. 예를 들어 WHERE column_name = value과 같이 검색 조건을 지정합니다.

  • 위치 지정 삭제는 커서를 지정하는 CURRENT OF 절을 사용합니다. 이 경우 커서의 현재 위치에서 삭제 작업이 발생합니다. 이것은 WHERE search_condition 절을 사용하여 삭제될 행을 한정하는 검색 결과 DELETE 문보다 정확한 방법입니다. 검색 결과 DELETE 문은 검색 조건이 한 행을 고유하게 식별하지 못할 경우 여러 행을 삭제할 수 있습니다.

• search_condition
  삭제될 행을 제한하는 조건을 지정합니다. 검색 조건에 포함시킬 수 있는 조건자의 개수에는 제한이 없습니다. 자세한 내용은 검색 조건(Transact-SQL)을 참조하십시오.

• CURRENT OF
  지정한 커서의 현재 위치에서 DELETE가 수행되도록 지정합니다.

• GLOBAL
  cursor_name이 전역 커서를 참조하도록 지정합니다.

• cursor_name
  인출이 수행되는 열린 커서의 이름입니다. 이름이 cursor_name인 전역 커서와 로컬 커서가 모두 있는 경우 이 인수는 GLOBAL이 지정되면 전역 커서를 참조하고 그렇지 않으면 로컬 커서를 참조합니다. 커서는 업데이트를 허용해야 합니다.

• cursor_variable_name
  커서 변수의 이름입니다. 커서 변수는 업데이트를 허용하는 커서를 참조해야 합니다.

• OPTION (query_hint [ ,... n] )
  데이터베이스 엔진에서 최적화 프로그램 힌트를 사용하여 문을 처리하는 방법을 사용자 지정한다는 것을 나타내는 키워드입니다. 자세한 내용은 쿼리 힌트(Transact-SQL)를 참조하십시오.

최상의 방법

테이블의 모든 행을 삭제하려면 TRUNCATE TABLE을 사용합니다. TRUNCATE TABLE은 DELETE보다 더 빠르고 시스템 및 트랜잭션 로그 리소스를 더 적게 사용합니다.

삭제된 행 수를 클라이언트 응용 프로그램으로 반환하려면 @@ROWCOUNT 함수를 사용합니다. 자세한 내용은 @@ROWCOUNT(Transact-SQL)를 참조하십시오.

호환성 지원

SQL Server의 다음 버전에서는 SET ROWCOUNT 옵션을 사용해도 DELETE 문에 영향을 주지 않습니다. 향후 개발 작업에서는 DELETE 문에 SET ROWCOUNT 옵션을 사용하지 않도록 하고 현재 이 옵션을 사용하는 응용 프로그램은 TOP 구문을 사용하도록 수정하십시오.

오류 처리

TRY…CATCH 구문에 문을 지정하여 DELETE 문에 대한 오류 처리를 구현할 수 있습니다. 자세한 내용은 Transact-SQL에서 TRY...CATCH 사용을 참조하십시오.

DELETE 문이 트리거를 위반하거나 FOREIGN KEY 제약 조건이 있는 다른 테이블의 데이터에 의해 참조되는 행을 제거하려고 하면 DELETE 문은 실패할 수 있습니다. DELETE가 여러 행을 제거하는 경우 제거되는 행 중 하나가 트리거 또는 제약 조건을 위반하면 이 문이 취소되고 오류가 반환되며 행이 제거되지 않습니다.

식 계산 중 DELETE 문에서 산술 오류(오버플로, 0으로 나누기, 도메인 오류 등)가 발생하면 데이터베이스 엔진에서는 SET ARITHABORT가 ON으로 설정된 것처럼 이러한 오류를 처리합니다. 나머지 일괄 처리가 취소되고 오류 메시지가 반환됩니다.

상호 운용성

수정할 개체가 테이블 변수인 경우 사용자 정의 함수의 본문에 DELETE를 사용할 수 있습니다.

FILESTREAM 열이 들어 있는 행을 삭제하면 해당 내부 파일 시스템 파일도 삭제됩니다. 기본 파일은 FILESTREAM 가비지 수집기를 통해 제거됩니다. 자세한 내용은 Transact-SQL을 사용하여 FILESTREAM 데이터 관리를 참조하십시오.

INSTEAD OF 트리거가 정의된 뷰를 직접 또는 간접으로 참조하는 DELETE 문에 FROM 절을 지정할 수 없습니다. INSTEAD OF 트리거에 대한 자세한 내용은 CREATE TRIGGER(Transact-SQL)를 참조하십시오.

제한 사항

TOP을 DELETE와 함께 사용하는 경우 참조되는 행은 어떤 순서로도 정렬되지 않으며 이 문에서 ORDER BY 절을 직접 지정할 수 없습니다. TOP을 사용하여 시간 순서로 행을 삭제해야 하는 경우에는 하위 SELECT 문에서 ORDER BY 절을 지정하는 방식으로 TOP을 사용해야 합니다. 이 항목 뒤에 나오는 예 섹션을 참조하십시오.

분할된 뷰에 대해서는 DELETE 문에 TOP을 사용할 수 없습니다.

SET ROWCOUNT 옵션의 설정은 원격 테이블과 분할된 원격 및 로컬 뷰에 대한 DELETE 문에서는 무시됩니다.

잠금 동작

힙에서 행을 삭제할 때 데이터베이스 엔진이 작업에 대해 행 또는 페이지 잠금을 사용할 수 있습니다. 이 경우 삭제 작업에서 비운 페이지가 힙에 할당된 상태로 남아 있습니다. 빈 페이지의 할당이 취소되지 않으면 데이터베이스의 다른 개체가 해당 공간을 다시 사용할 수 없습니다. 힙에서 행을 삭제하고 페이지 할당을 취소하려면 다음 방법 중 하나를 사용하십시오.

• DELETE 문에 TABLOCK 힌트를 지정합니다. TABLOCK 힌트를 사용하면 삭제 작업이 행 또는 페이지 잠금 대신 공유 잠금을 수행하므로 페이지 할당을 취소할 수 있습니다. TABLOCK 힌트에 대한 자세한 내용은 테이블 힌트(Transact-SQL)를 참조하십시오.

• 테이블에서 모든 행이 삭제되는 경우 TRUNCATE TABLE을 사용합니다.

• 행을 삭제하기 전에 힙에 클러스터형 인덱스를 만듭니다. 행이 삭제되고 나면 클러스터형 인덱스를 삭제할 수 있습니다. 이 방법은 앞의 두 방법보다 시간이 오래 걸리며 임시 리소스를 더 많이 사용합니다.

잠금에 대한 자세한 내용은 데이터베이스 엔진에서의 잠금을 참조하십시오.

로깅 동작

DELETE 문은 모두 기록됩니다.

사용 권한

대상 테이블에 DELETE 권한이 필요합니다. 문에 WHERE 절이 포함되어 있을 경우 SELECT 권한도 필요합니다.

DELETE 권한은 sysadmin 고정 서버 역할, db_owner 및 db_datawriter 고정 데이터베이스 역할, 테이블 소유자의 멤버에게 기본적으로 부여됩니다. sysadmin, db_owner 및 db_securityadmin 역할의 멤버와 테이블 소유자는 다른 사용자에게 권한을 위임할 수 있습니다.

예

기본 구문

이 섹션의 예는 필요한 최소한의 구문을 사용하여 DELETE 문의 기본 기능을 보여 줍니다.

다음 예에서는 삭제될 행 수를 제한하는 WHERE 절을 사용하지 않았기 때문에 SalesPersonQuotaHistory 테이블에서 모든 행을 삭제합니다.

USE AdventureWorks;
GO
DELETE FROM Sales.SalesPersonQuotaHistory;
GO

삭제할 행 지정

이 섹션의 예에서는 삭제될 행 수를 제한하는 방법을 보여 줍니다.

1. WHERE 절을 사용하여 행 수 제한

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

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

3. 커서를 사용하여 현재 행 삭제

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

4. 하위 쿼리 및 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

5. 공통 테이블 식 사용

표준 테이블 이외의 대상 개체 지정

이 섹션의 예에서는 뷰 또는 테이블 변수를 지정하여 행을 삭제하는 방법을 보여 줍니다.

원격 테이블에서 행 삭제

이 섹션의 예에서는 원격 테이블 참조에 연결된 서버 또는 행 집합 함수를 사용하여 원격 대상 테이블에서 행을 삭제하는 방법을 보여 줍니다.

힌트를 사용하여 쿼리 최적화 프로그램의 기본 동작 무시

이 섹션의 예에서는 DELETE 문을 처리할 때 테이블 힌트 및 쿼리 힌트를 사용하여 쿼리 최적화 프로그램의 기본 동작을 임시로 무시하는 방법을 보여 줍니다.

주의
SQL Server 쿼리 최적화 프로그램은 일반적으로 쿼리에 대해 최적의 실행 계획을 선택하므로 힌트는 숙련된 개발자나 데이터베이스 관리자가 최후의 수단으로만 사용하는 것이 좋습니다.

다음 예에서는 READPAST 테이블 힌트를 지정합니다. READPAST를 지정하면 행 수준 및 페이지 수준 잠금을 모두 건너뛰므로 데이터베이스 엔진이 다른 트랜잭션에서 잠근 행과 페이지를 읽지 않습니다. 자세한 내용은 테이블 힌트(Transact-SQL)를 참조하십시오.

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

DELETE 문의 결과 캡처

이 섹션의 예에서는 OUTPUT 절을 사용하여 DELETE 문의 영향을 받는 각 행의 정보 또는 각 행을 기반으로 하는 식을 반환하는 방법을 보여 줍니다. 이 결과를 응용 프로그램에 반환하여 확인 메시지, 보관 및 다른 응용 프로그램 요구 사항을 충족하는 데 사용할 수 있습니다.

1. DELETE에 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

2. DELETE 문에 OUTPUT 및 from_table_name 사용

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

참고 항목

참조

개념