TRUNCATE TABLE(Transact-SQL)

적용 대상:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics분석 플랫폼 시스템(PDW)Microsoft Fabric의 웨어하우스Microsoft Fabric 미리 보기의 SQL 데이터베이스

개별 행 삭제를 로깅하지 않고 테이블 또는 테이블의 지정된 파티션에서 모든 행을 제거합니다. TRUNCATE TABLE 는 절이 DELETE 없는 WHERE 문과 유사합니다. 그러나 TRUNCATE TABLE 속도가 빠르며 더 적은 시스템 및 트랜잭션 로그 리소스를 사용합니다.

Transact-SQL 구문 표기 규칙

Syntax

SQL Server, Azure SQL Database, Fabric SQL 데이터베이스 구문

TRUNCATE TABLE
    { database_name.schema_name.table_name | schema_name.table_name | table_name }
    [ WITH ( PARTITIONS ( { <partition_number_expression> | <range> }
    [ , ...n ] ) ) ]
[ ; ]

<range> ::=
<partition_number_expression> TO <partition_number_expression>

Microsoft Fabric, Azure Synapse Analytics 및 병렬 데이터 웨어하우스의 구문입니다.

TRUNCATE TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }
[ ; ]

Arguments

database_name

데이터베이스의 이름입니다.

schema_name

테이블이 속한 스키마의 이름입니다.

table_name

잘리거나 모든 행을 제거할 테이블의 이름입니다. table_name은 리터럴이어야 합니다. table_name 함수 또는 변수일 OBJECT_ID() 수 없습니다.

WITH ( PARTITIONS ( { <partition_number_expression> | <레인지> } [ , ... n ] ) )

적용 대상: SQL Server 2016(13.x) 이상 버전

자를 파티션이나 모든 행이 제거되는 파티션을 지정합니다. 테이블이 분할되지 않으면 인수에서 WITH PARTITIONS 오류가 발생합니다. 절이 WITH PARTITIONS 제공되지 않으면 전체 테이블이 잘립니다.

<partition_number_expression>은 다음과 같은 방법으로 지정할 수 있습니다.

• 파티션의 번호를 지정합니다. 예: WITH (PARTITIONS (2))

• 여러 개별 파티션의 파티션 번호를 쉼표로 구분하여 지정합니다. 예: WITH (PARTITIONS (1, 5))

• 범위와 개별 파티션을 모두 지정합니다. 예: WITH (PARTITIONS (2, 4, 6 TO 8))

• <range> 는 다음과 같이 단어 TO로 구분된 파티션 번호로 지정할 수 있습니다. WITH (PARTITIONS (6 TO 8))

분할된 테이블을 자르려면 테이블과 인덱스를 정렬해야 합니다(동일한 파티션 함수에 분할).

Remarks

문 DELETE 에 TRUNCATE TABLE 비해 다음과 같은 장점이 있습니다.

• 트랜잭션 로그 공간을 덜 사용합니다.

  이 문은 DELETE 행을 한 번에 하나씩 제거하고 삭제된 각 행의 트랜잭션 로그에 항목을 기록합니다. TRUNCATE TABLE 는 테이블 및 인덱스 데이터를 저장하는 데 사용되는 데이터 페이지를 할당 취소하여 데이터를 제거하고 트랜잭션 로그에 페이지 할당 취소만 기록합니다.

• 일반적으로 적은 수의 잠금이 사용됩니다.

  행 잠금을 DELETE 사용하여 문을 실행하면 테이블의 각 행이 삭제를 위해 잠깁니다. TRUNCATE TABLE 항상 테이블(스키마(SCH-M) 잠금 포함) 및 페이지를 잠그지만 각 행은 잠급니다.

• 예외 없이 테이블 또는 해당 인덱스에 0페이지가 남아 있습니다.

  DELETE 문이 실행된 후에도 테이블에 빈 페이지가 포함될 수 있습니다. 예를 들어 힙의 빈 페이지는 배타적(LCK_M_X) 테이블 잠금이 없으면 할당을 취소할 수 없습니다. 삭제 작업에서 테이블 잠금을 사용하지 않는 경우 테이블(힙)에 빈 페이지가 많이 포함될 수 있습니다. 인덱스의 경우 문은 DELETE 빈 페이지를 남겨 둘 수 있습니다. 그런 다음 백그라운드 정리 프로세스에서 이러한 페이지의 할당을 취소합니다.

TRUNCATE TABLE 는 테이블에서 모든 행을 제거하지만 테이블 구조와 해당 열, 제약 조건, 인덱스 등은 그대로 유지됩니다. 테이블 정의 및 테이블의 데이터를 제거하려면 DROP TABLE 문을 사용하세요.

테이블에 ID 열이 포함되어 있으면 해당 열의 카운터는 열에 대한 초기값으로 다시 설정됩니다. 초기값이 정의되지 않은 경우 기본값 1 이 사용됩니다. ID 카운터를 유지하려면 대신 사용합니다 DELETE .

TRUNCATE TABLE 트랜잭션 내에서 작업을 롤백할 수 있습니다.

Fabric SQL 데이터베이스에서 테이블을 잘면 해당 테이블에 대한 Fabric OneLake에서 미러된 모든 데이터가 삭제됩니다.

Deferred deallocation

128개 이상의 익스텐트를 사용하는 테이블이 잘리면 데이터베이스 엔진은 트랜잭션이 커밋될 때까지 실제 페이지 할당 취소 및 관련 잠금을 연기합니다. 잘림은 논리적 단계와 물리적 단계의 두 가지 개별 단계에서 발생합니다. 논리 단계에서 테이블 및 해당 인덱스에 사용되는 기존 할당 단위는 할당 취소로 표시되고 트랜잭션이 커밋될 때까지 잠깁니다. 물리적 단계에서 백그라운드 프로세스는 할당 취소로 표시된 페이지를 제거합니다. 즉, 릴리스된 TRUNCATE TABLE 공간을 새 할당에 즉시 사용할 수 없습니다.

가속 데이터베이스 복구를 사용하는 경우 잘림은 익스텐트 수에 관계없이 별도의 논리 및 물리적 단계를 사용합니다.

Limitations

다음과 같은 테이블에는 사용할 TRUNCATE TABLE 수 없습니다.

• 제약 조건에 의해 FOREIGN KEY 참조됩니다. 자신을 참조하는 외래 키가 있는 테이블을 잘라낼 수 있습니다.

• 인덱싱된 뷰에 참여합니다.

• 트랜잭션 복제 또는 병합 복제에 의해 게시됩니다.

• 시스템 버전 임시 테이블입니다.

• 제약 조건에 EDGE 의해 참조됩니다.

이러한 특성이 하나 이상 있는 테이블의 경우 대신 문을 DELETE 사용합니다.

TRUNCATE TABLE 작업에서 개별 행 삭제를 기록하지 않으므로 트리거를 활성화할 수 없습니다. 자세한 내용은 CREATE TRIGGER(Transact-SQL)를 참조하세요.

Azure Synapse Analytics 및 분석 플랫폼 시스템(PDW)의 경우:

• TRUNCATE TABLE 는 문 내에서 EXPLAIN 허용되지 않습니다.

• TRUNCATE TABLE 는 트랜잭션 내에서 실행할 수 없습니다.

Permissions

최소한 ALTER에 대한 권한이 필요합니다. TRUNCATE TABLE 사용 권한은 기본적으로 테이블 소유자, 고정 서버 역할의 sysadmin 멤버 및 db_ownerdb_ddladmin 고정 데이터베이스 역할로 설정되며 전송할 수 없습니다. 하지만 저장 프로시저와 같은 모듈 내에 TRUNCATE TABLE 문을 통합한 뒤 EXECUTE AS 절을 사용하여 적절한 권한을 모듈에 허용할 수 있습니다.

Examples

A. 테이블 자르기

다음 예에서는 JobCandidate 테이블의 모든 데이터를 제거합니다. SELECT 문이 TRUNCATE TABLE 문 앞과 뒤에 포함되어 결과를 비교합니다.

USE AdventureWorks2022;

SELECT COUNT(*) AS BeforeTruncateCount
FROM HumanResources.JobCandidate;

TRUNCATE TABLE HumanResources.JobCandidate;

SELECT COUNT(*) AS AfterTruncateCount
FROM HumanResources.JobCandidate;

B. 테이블 파티션 자르기

적용 대상: SQL Server 2016(13.x) 이상 버전

다음 예에서는 분할된 테이블의 지정된 파티션을 자릅니다. WITH (PARTITIONS (2, 4, 6 TO 8)) 구문은 파티션 번호 2, 4, 6, 7, 8이 잘리도록 합니다.

TRUNCATE TABLE PartitionTable1
WITH (PARTITIONS (2, 4, 6 TO 8));
GO

C. 잘린 작업 롤백

다음 예제에서는 트랜잭션 내의 TRUNCATE TABLE 작업을 롤백할 수 있음을 보여 줍니다.

1. 세 개의 행이 있는 테스트 테이블을 만듭니다.

   USE [tempdb];
   
   CREATE TABLE TruncateTest (ID INT IDENTITY (1, 1) NOT NULL);
   GO
   INSERT INTO TruncateTest DEFAULT VALUES;
   GO 3
   

2. 잘리기 전에 데이터를 확인합니다.

   SELECT ID FROM TruncateTest;
   

3. 트랜잭션 내에서 테이블을 잘라내고 행 수를 확인합니다.

   BEGIN TRANSACTION;
   
   TRUNCATE TABLE TruncateTest;
   
   SELECT ID FROM TruncateTest;
   

   테이블이 비어 있는 것을 볼 수 있습니다.

4. 트랜잭션을 롤백하고 데이터를 확인합니다.

   ROLLBACK TRANSACTION;
   
   SELECT ID FROM TruncateTest;
   

   세 행이 모두 표시됩니다.

5. 테이블을 정리합니다.

   DROP TABLE TruncateTest;
   

Related content

• DELETE (Transact-SQL)
• DROP TABLE(Transact-SQL)
• CREATE TABLE(Transact-SQL) IDENTITY(속성)