TRUNCATE TABLE(Transact-SQL)

적용 대상: Microsoft Fabric의 SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System(PDW) 웨어하우스

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

Transact-SQL 구문 표기 규칙

구문

SQL Server 및 Azure SQL Database 구문

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

인수

database_name

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

schema_name

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

table_name

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

WITH ( PARTITIONS ( { <partition_number_expression> | <range> } [ , ...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))

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

설명

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

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

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

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

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

• 빈 페이지는 예외 없이 테이블에 남습니다.

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

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

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

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

제한 사항

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

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

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

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

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

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

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

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

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

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

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

큰 테이블 자르기

Microsoft SQL Server에는 삭제할 모든 익스텐트에 대한 동시 잠금이 없는 한 128개를 초과하는 익스텐트를 갖고 있는 테이블을 삭제하거나 잘라내는 기능이 추가되었습니다.

사용 권한

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

예제

A. 테이블 자르기

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

USE AdventureWorks2022;
GO

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

TRUNCATE TABLE HumanResources.JobCandidate;
GO

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

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];
   GO
   CREATE TABLE TruncateTest (ID INT IDENTITY (1, 1) NOT NULL);
   GO
   INSERT INTO TruncateTest DEFAULT VALUES;
   GO 3
   

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

   SELECT * FROM TruncateTest;
   GO
   

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

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

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

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

   ROLLBACK TRANSACTION;
   GO
   
   SELECT * FROM TruncateTest;
   GO
   

   세 행이 모두 표시됩니다.

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

   DROP TABLE TruncateTest;
   GO
   

관련 콘텐츠

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