TRUNCATE와 DELETE FROM의 장단점

TRUNCATE와 DELETE FROM의 장단점

---

누군가 다음 두 가지 진술을 사용하여 장단점에 대한 간략한 개요를 줄 수 있습니까?

TRUNCATE TABLE dbo.MyTable

vs

DELETE FROM dbo.MyTable

모든 것을 말하고 끝낼 때 둘 다 똑같은 일을하는 것 같습니다. 그러나 둘 사이에 차이점이 있어야합니다.

---

TRUNCATE롤백 데이터를 생성하지 않으므로 번개가 빠릅니다. 테이블에서 사용하는 데이터 페이지를 할당 해제합니다.

그러나 트랜잭션 중이고이 삭제를 "실행 취소"하는 기능을 원하는 경우 DELETE FROM롤백 기능을 제공하는 을 사용해야 합니다.

편집 : 위의 내용은 SQL Server에 대해 올바르지 않습니다 (그러나 Oracle에는 적용됨). SQL Server에서는 트랜잭션 내부에 있고 트랜잭션이 커밋되지 않은 경우 자르기 작업을 롤백 할 수 있습니다. SQL Server 관점에서 DELETE FROM과 TRUNCATE의 주요 차이점은 다음 과 같습니다 . "DELETE 문은 한 번에 하나씩 행을 제거하고 삭제 된 각 행의 트랜잭션 로그에 항목을 기록합니다. TRUNCATE TABLE은 데이터 페이지 할당을 해제하여 데이터를 제거합니다. 테이블 데이터를 저장하고 트랜잭션 로그에 페이지 할당 취소 만 기록하는 데 사용됩니다. "

즉, 페이지 할당 해제 만 트랜잭션 로그에 기록되고 DELETE FROM 각 행 삭제가 기록되기 때문에 TRUNCATE 동안 로깅이 적습니다. 이것이 TRUNCATE가 번개처럼 빠른 이유 중 하나입니다.

또한 MSDN 링크에서 외래 키 제약 조건에 의해 참조되거나 인덱싱 된 뷰에 참여하거나 트랜잭션 복제 또는 병합 복제를 사용하여 게시 된 테이블을자를 수 없다는 점에 유의하십시오.

편집 2 : 또 다른 요점은 TRUNCATE TABLE이 귀하의 신원을 초기 시드로 재설정하는 반면 DELETE FROM은 중단 된 지점에서 계속 증가한다는 것입니다. 참조 : Ben Robinson의 대답.

---

다른 답변에 언급되지 않은 또 다른 중요한 점은이 TRUNCATE TABLE것 재설정 하여 초기 씨앗에 정체성을 하는 반면, DELETE FROM이 off 상태에서 증가에 수행한다.

---

보안 관점과의 또 다른 차이점은 TRUNCATE에는 테이블에 대한 ALTER 권한이 필요한 반면 DELETE에는 해당 테이블에 대한 (드럼 롤) DELETE 권한 만 필요하다는 것입니다.

---

TRUNCATE TABLE트랜잭션을 기록하지 않습니다. 즉, 큰 테이블의 경우 번개가 빠릅니다. 단점은 작업을 취소 할 수 없다는 것입니다.

DELETE FROM삭제되는 각 행을 트랜잭션 로그에 기록하므로 작업에 시간이 걸리고 트랜잭션 로그가 크게 증가합니다. 장점은 필요한 경우 작업을 취소 할 수 있다는 것입니다.

---

SQL 서버에서 삭제 대 자르기 개요

전체 기사의 경우이 연결 후 가져 오기 : SQL Server에서 Vs Truncate 삭제

/*Truncate - Syntax*/
TRUNCATE TABLE table_name

/*Delete - Syntax*/
DELETE FROM table_name
WHERE some_condition

---

작업이 명시 적 트랜잭션에서 실행 된 경우에만 Delete 및 Truncate를 롤백 할 수 있다고 생각합니다. 그렇지 않으면 제거 된 데이터를 복구하기 위해 복원을 수행해야합니다.

---

근본적인 차이점은 기록 방식에 있습니다. DELETE 및 TRUNCATE는 다르게 기록되지만 둘 다 정확히 동일한 방식으로 롤백 할 수 있습니다. 데이터를 변경하는 모든 작업이 기록됩니다. SQL Server에는 로그되지 않은 작업과 같은 것이 없습니다.

---

매우 중요하고 (imo) 다른 답변에서 언급되지 않은 한 가지는 TRUNCATESchema Stability lock 이 필요 Sch-S하지만 DELETE행 잠금을 사용 한다는 것 입니다. 다음을 살펴 보겠습니다.

BEGIN TRANSACTION;

BEGIN TRY
    -- Truncate below will take LCK_M_SCH_S lock for TABLE_A
    TRUNCATE TABLE TABLE_A

    -- Lets say the query below takes 5 hours to execute
    INSERT INTO
        TABLE_A
    SELECT
        *
    FROM
        GIANT_TABLE (NOLOCK)
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
    THROW
END CATCH

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;

Now assume that after 1-2 minutes of the start of this query, let's say we tried to execute the following:

SELECT COUNT(*) FROM TABLE_A (NOLOCK)

Notice that I used NOLOCK clause. What do you think will happen now? This query will wait 5 hours. Why? Because NOLOCK clause needs Sch-S lock on TABLE_A but that TRUNCATE clause has Sch-S on it already. Since we didn't commit the transaction yet, the lock is still on even after that TRUNCATE clause. Sch-S lock on a table basically means that either TABLE_A is being altered by adding/removing columns etc. or it's being truncated. You even can't execute something like below:

SELECT object_id('TABLE_A')

This will stuck 5 hours too. However, if you replace that TRUNCATE with DELETE FROM, you'll see that there will be no Sch-S lock on the table and the queries above will not get stucked.

---

Another difference between DELETE vs TRUNCATE is behaviour when table is corrupted.

For instance:

DELETE FROM table_name;

Will end up with error:

Msg 3314, Level 21, State 3, Line 1

During undoing of a logged operation in database '...', an error occurred at log record ID (). Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database.

Msg 0, Level 20, State 0, Line 0

A severe error occurred on the current command. The results, if any, should be discarded.

While TRUNCATE will work:

TRUNCATE TABLE table_name;
-- Command(s) completed successfully.

---

게다가 모든 해답은, 또 다른 점은 고려해야 할 Truncate트리거되지 않습니다 delete trigger테이블을하지만, delete문은 트리거 delete trigger각 행에 대해 테이블을.

---

truncate는 로깅을 수행하지 않고 삭제는 수행하므로 레코드가 많으면 트랜잭션 로그가 엄청납니다.

참고 URL : https://stackoverflow.com/questions/3256242/pros-cons-of-truncate-vs-delete-from