MSSQL TRUNCATE DELETE 차이점 및 사용법

TRUNCATE TABLE 명령문은 테이블의 전체 또는 테이블의 지정된 파티션의 모든 데이터를 빠르게 삭제할 때 주로 사용합니다.

TRUNCATE 명령어와 DELETE 차이점

Truncate 명령어 특징.

• Truncate는 DDL 명령어이다.
• TRUNCATE 명령어는 DELETE 명령보다 더 빠르게 행을 삭제할 수 있습니다.
• 자동증가 열 IDENTITY를 초기화할 수 있다.
• 테이블 구조는 변경되지 않습니다. 즉 column 정보, 제약 조건, 인덱스 변경되지 않고 data만 삭제됩니다.
• table의 데이터 저장소의 페이지 취소만을 트랜잭션 로그에 저장하기 때문에 delete 문의 비해서 공간을 덜 사용합니다.
• where절처럼 조건절 사용할 수 없습니다.

Delete만 특징.

• Delete는 DML 문입니다.
• record의 행 개수가 많으면 속도가 느려질 수 있습니다.
• 조건(where) 절을 사용하여 특정 행만을 삭제할 수 있습니다.
• 자동증가 column의 identity 값을 초기화하지 못합니다.
• 초기화할 때는 DBCC CHECKIDENT 명령어를 사용합니다.
• delete 문은 행을 한 번에 하나씩 제거하고 삭제된 각 행에 대해 트랜잭션 로그를 저장하므로 Truncate 비교하면 많은 용량이 필요합니다.

TRUNCATE 사용법

TRUNCATE 사용하여 table 데이터 삭제하기.

• TRUNCATE 사용 소스.

--TRUNCATE 사용법. 
-- cretable table
DROP TABLE IF EXISTS InfoForTruncate;
CREATE TABLE InfoForTruncate (
	UserId int,
	UserNm varchar(255) 
); 
-- insert sample data 
INSERT INTO dbo.InfoForTruncate(UserId,UserNm) 
VALUES (272, N'Ken')
      ,(273, N'Brian')
      ,(274, N'Stephen')
      ,(275, N'Michael')
     ,(276, N'Linda');
-- check data
SELECT * FROM InfoForTruncate; 
-- excute TRUNCATE  TABLE 
TRUNCATE TABLE InfoForTruncate;
-- check data
SELECT * FROM InfoForTruncate;

• TRUNCATE 설명입니다.

TRUNCATE 사용법.

TRANSACTION 사용하여 rollback 하기.

• TRANSACTION 사용 소스.

--TRANSACTION 사용하여 rollback 하기
DROP TABLE IF EXISTS TruncateForTran;
CREATE TABLE TruncateForTran ( 
	UserId  int,
	UserNm varchar(255)  
) ON [PRIMARY];

INSERT INTO dbo.TruncateForTran(UserId,UserNm) 
	VALUES (272, N'Ken') ,(273, N'Brian')
;
-- TRANSACTION start
BEGIN TRANSACTION;
TRUNCATE TABLE TruncateForTran;

-- ROLLBACK  check.
ROLLBACK;
SELECT * FROM TruncateForTran;

-- COMMIT  check.
COMMIT;
SELECT * FROM TruncateForTran;

IDENTITY 초기화 확인.

• IDENTITY 초기화 소스.

--IDENTITY 초기화 확인. 
DROP TABLE IF EXISTS TruncateForIDENTITY;
CREATE TABLE TruncateForIDENTITY (
	IdKey   int  IDENTITY (1, 1)  PRIMARY KEY,
	UserId  int,
	UserNm varchar(255)  
) ON [PRIMARY];

INSERT INTO dbo.TruncateForIDENTITY(UserId,UserNm) 
	VALUES (272, N'Ken') ,(273, N'Brian')
 ;

TRUNCATE TABLE InfoForTruncate;

INSERT INTO dbo.TruncateForIDENTITY(UserId,UserNm) 
	VALUES (272, N'Ken') ,(273, N'Brian')
 ;

 -- check data
SELECT * FROM InfoForTruncate;

TRUNCATE 사용 시 제한 사항

• TRUNCATE 작업 시 제약 조건에 의해 FOREIGN KEY 참조됩니다.
• 자신을 참조하는 외래 키가 있는 테이블 행을 삭제할 수 있습니다.
• TRUNCATE 작업에서 개별 행 삭제를 기록하지 않으므로 트리거를 사용할 수 없습니다.
• TRUNCATE TABLE는 문 내에서 EXPLAIN 허용하지 않습니다.