Nie jest to dla wszystkich takie oczywiste więc postaram się trochę sprawę przybliżyć, czym różnią się polecenia TRUNCATE TABLE od DELETE
Tworzymy poligon
CREATE DATABASE test
go
USE test
go
CREATE TABLE dbo.tabelaTRUNCATE
(
idd INT NOT NULL IDENTITY (1, 1),
pole VARCHAR(50)
);
CREATE TABLE dbo.tabelaDELETE
(
idd INT NOT NULL IDENTITY (1, 1),
pole VARCHAR(50)
);
go
INSERT dbo.tabelaTRUNCATE (pole)
SELECT 'x'
go
INSERT dbo.tabelaDELETE (pole)
SELECT 'x'
go
1.
do wykonania DELETE potrzebne jest uprawnienie do usuwania z tabeli
do wykonania TRUNCATE TABLE potrzebne jest prawo ALTER TABLE
do wykonania TRUNCATE TABLE potrzebne jest prawo ALTER TABLE
Tworzymy sobie usera z małymi prawami
CREATE LOGIN slaby WITH PASSWORD=N'test', CHECK_POLICY=OFF
GO
USE test
GO
CREATE USER slaby FOR LOGIN slaby
GO
GRANT DELETE ON dbo.tabelaDELETE TO slaby
go
GRANT DELETE ON dbo.tabelaTRUNCATE TO slaby
Wchodzimy w jego skórę
EXECUTE AS LOGIN ='slaby'
Usuwamy
DELETE FROM dbo.tabelaDELETE
--------------------------------------
(1 row(s) affected)
TRUNCATE TABLE dbo.tabelaTRUNCATE
--------------------------------------
Msg 1088, Level 16, State 7, Line 1
Cannot find the object "tabelaTRUNCATE" because it does not exist or you do not have permissions.
2.
polecenia DELETE można używać razem z filtrami
polecenie TRUNCATE TABLE usuwa wszystkie dane z tabeli
DELETE FROM dbo.tabelaDELETE
WHERE pole LIKE 'x'
--------------------------------------
(0 row(s) affected)
TRUNCATE TABLE dbo.tabelaTRUNCATE
WHERE pole LIKE 'x'
--------------------------------------
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'WHERE'.
3.
Polecenie DELETE nie zmienia identity dla klucza
Polecenie TRUNCATE TABLE „zeruje” identity klucza
DBCC CHECKIDENT('dbo.tabelaTRUNCATE')
--------------------------------------
Checking identity information: current identity value '2', current column value '2'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Checking identity information: current identity value 'NULL', current column value 'NULL'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
4.
Polecenia TRUNCATE TABLE nie można wykonać na tabeli z kluczem obcym (foreign key constraints)
CREATE TABLE dbo.klucz(
idDELETE INT,
idTRUNCATE INT)
GO
CREATE UNIQUE NONCLUSTERED INDEX ix_d ON dbo.tabelaDELETE(idd)
GO
CREATE UNIQUE NONCLUSTERED INDEX ix_d ON dbo.tabelaTRUNCATE(idd)
GO
ALTER TABLE dbo.klucz ADD CONSTRAINT
FK_d2 FOREIGN KEY(idDELETE)
REFERENCES dbo.tabelaDELETE(idd)
GO
ALTER TABLE dbo.klucz ADD CONSTRAINT
FK_t2 FOREIGN KEY(idTRUNCATE)
REFERENCES dbo.tabelaTRUNCATE(idd)
GO
DELETE FROM dbo.tabelaDELETE
--------------------------------------
(0 row(s) affected)
TRUNCATE TABLE dbo.tabelaTRUNCATE
--------------------------------------
Msg 4712, Level 16, State 1, Line 1
Cannot truncate table 'dbo.tabelaTRUNCATE' because it is being referenced by a FOREIGN KEY constraint.
ALTER TABLE dbo.klucz
DROP CONSTRAINT FK_d2
ALTER TABLE dbo.klucz
DROP CONSTRAINT FK_t2
GO
DROP TABLE dbo.klucz
5.
Polecenie TRUNCATE TABLE nie uruchamia trigerra for delete ustawionego na tabeli
CREATE TRIGGER d1 ON dbo.tabelaDELETE
FOR DELETE
AS
print 'skasowane!'
go
CREATE TRIGGER t1 ON dbo.tabelaTRUNCATE
FOR DELETE
AS
print 'skasowane!'
GO
DELETE FROM dbo.tabelaDELETE
--------------------------------------
skasowane!
(0 row(s) affected)
TRUNCATE TABLE dbo.tabelaTRUNCATE
--------------------------------------
Command(s) completed successfully.
DROP TRIGGER t1
DROP TRIGGER d1
6.
Polecenie DELETE jest logowane per wiersz przez co trwa dużo dłużej
Polecenie TRUNCATE TABLE nie usuwa wierszy oznacza je w tabeli jako dealokowane i serwer może używać stron danych na których były dane z tabeli.
INSERT dbo.tabelaTRUNCATE (pole)
SELECT 'x'
go 1000
INSERT dbo.tabelaDELETE (pole)
SELECT 'x'
go 1000
SET STATISTICS TIME ON
GO
SET STATISTICS IO ON
GO
DELETE FROM dbo.tabelaDELETE
--------------------------------------
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'tabelaDELETE'. Scan count 1, logical reads 5004, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 80 ms.
(1000 row(s) affected)
TRUNCATE TABLE dbo.tabelaTRUNCATE
--------------------------------------
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
SET STATISTICS TIME OFF
GO
SET STATISTICS IO OFF
GO
INSERT dbo.tabelaTRUNCATE (pole)
SELECT 'x'
go 10
INSERT dbo.tabelaDELETE (pole)
SELECT 'x'
go 10
czyścimy zbędne wpisy w dzienniku logu
CHECKPOINT
TRUNCATE TABLE dbo.tabelaTRUNCATE
Oglądamy można DBCC LOG ('test', 2)
lub
SELECT Operation,Context,AllocUnitName,[Lock Information],Description FROM fn_dblog (NULL, NULL);
--------------------------------------
Operation Context AllocUnitName Lock Information Description
---------------------- ------------ ---------------------- ------------------------------------------------------------------------------------------------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LOP_BEGIN_CKPT LCX_NULL NULL NULL
LOP_END_CKPT LCX_NULL NULL NULL log_minRecoveryLsn 00000070:0000017a:000a;log_replbeginlsn 00000000:00000000:0000;log_replnextlsn 00000000:00000000:0000;log_distbackuplsn 00000000:00000000:0000;log_distlastlsn 00000000:00000000:0000
LOP_BEGIN_XACT LCX_NULL NULL NULL TRUNCATE TABLE;0x0105000000000005150000007014810339fe43c622803220c70a0000
LOP_LOCK_XACT LCX_NULL NULL HoBt 0:ACQUIRE_LOCK_SCH_M OBJECT: 17:2105058535:0
LOP_LOCK_XACT LCX_NULL NULL HoBt 0:ACQUIRE_LOCK_SCH_M METADATA: database_id = 17 SEQUENCE($seq_type = 0, object_id = 2105058535)
LOP_MODIFY_ROW LCX_IAM dbo.tabelaTRUNCATE HoBt 72057594039697408:ACQUIRE_LOCK_X RID: 17:1:169:0
LOP_MODIFY_ROW LCX_PFS dbo.tabelaTRUNCATE HoBt 6488064:ACQUIRE_LOCK_X PAGE: 17:1:168 Deallocated 0001:000000a8
LOP_MODIFY_ROW LCX_PFS Unknown Alloc Unit HoBt 6488064:ACQUIRE_LOCK_X PAGE: 17:1:169 Deallocated 0001:000000a9
LOP_COUNT_DELTA LCX_CLUSTERE sys.sysallocunits.clus NULL
LOP_COUNT_DELTA LCX_CLUSTERE sys.sysrowsets.clust NULL
LOP_COUNT_DELTA LCX_CLUSTERE sys.sysrscols.clst NULL
LOP_COUNT_DELTA LCX_CLUSTERE sys.sysrscols.clst NULL
LOP_HOBT_DDL LCX_NULL NULL NULL Action 2 on HoBt 0xd:100, partition 0x0, rowset 72057594038779904.
LOP_MODIFY_ROW LCX_CLUSTERE sys.sysallocunits.clus HoBt 458752:ACQUIRE_LOCK_IX OBJECT: 17:7:0 ;ACQUIRE_LOCK_X KEY: 17:458752 (000058e2cb84)
LOP_HOBT_DDL LCX_NULL NULL NULL Action 2 on HoBt 0xd:100, partition 0x0, rowset 72057594038779904.
LOP_MODIFY_ROW LCX_CLUSTERE sys.sysrowsets.clust HoBt 327680:ACQUIRE_LOCK_IX OBJECT: 17:5:0 ;ACQUIRE_LOCK_X KEY: 17:327680 (0000de034451)
LOP_IDENT_SENTVAL LCX_NULL NULL NULL
LOP_MODIFY_ROW LCX_CLUSTERE sys.syscolpars.clst HoBt 281474979397632:ACQUIRE_LOCK_IX OBJECT: 17:41:0 ;ACQUIRE_LOCK_X KEY: 17:281474979397632 (e800ca0eb53a)
LOP_COMMIT_XACT LCX_NULL NULL NULL
(19 row(s) affected)
Teraz dla delete
CHECKPOINT
DELETE FROM dbo.tabelaDELETE
SELECT Operation,Context,AllocUnitName,[Lock Information],Description FROM fn_dblog (NULL, NULL);
--------------------------------------
Operation Context AllocUnitName Lock Information Description
---------------------- ------------ ---------------------- -------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LOP_BEGIN_CKPT LCX_NULL NULL NULL
LOP_END_CKPT LCX_NULL NULL NULL log_minRecoveryLsn 00000070:0000018f:003a;log_replbeginlsn 00000000:00000000:0000;log_replnextlsn 00000000:00000000:0000;log_distbackuplsn 00000000:00000000:0000;log_distlastlsn 00000000:00000000:0000
LOP_BEGIN_XACT LCX_NULL NULL NULL DELETE;0x0105000000000005150000007014810339fe43c622803220c70a0000
LOP_DELETE_ROWS LCX_HEAP dbo.tabelaDELETE HoBt 72057594038845440:ACQUIRE_LOCK_IX OBJECT: 17:2121058592:0 ;ACQUIRE_LOCK_IX PAGE: 17:1:200;ACQUIRE_LOCK_X RID: 17:1:200:0
LOP_DELETE_ROWS LCX_HEAP dbo.tabelaDELETE HoBt 72057594038845440:ACQUIRE_LOCK_IX OBJECT: 17:2121058592:0 ;ACQUIRE_LOCK_IX PAGE: 17:1:200;ACQUIRE_LOCK_X RID: 17:1:200:1
LOP_DELETE_ROWS LCX_HEAP dbo.tabelaDELETE HoBt 72057594038845440:ACQUIRE_LOCK_IX OBJECT: 17:2121058592:0 ;ACQUIRE_LOCK_IX PAGE: 17:1:200;ACQUIRE_LOCK_X RID: 17:1:200:2
LOP_DELETE_ROWS LCX_HEAP dbo.tabelaDELETE HoBt 72057594038845440:ACQUIRE_LOCK_IX OBJECT: 17:2121058592:0 ;ACQUIRE_LOCK_IX PAGE: 17:1:200;ACQUIRE_LOCK_X RID: 17:1:200:3
LOP_DELETE_ROWS LCX_HEAP dbo.tabelaDELETE HoBt 72057594038845440:ACQUIRE_LOCK_IX OBJECT: 17:2121058592:0 ;ACQUIRE_LOCK_IX PAGE: 17:1:200;ACQUIRE_LOCK_X RID: 17:1:200:4
LOP_DELETE_ROWS LCX_HEAP dbo.tabelaDELETE HoBt 72057594038845440:ACQUIRE_LOCK_IX OBJECT: 17:2121058592:0 ;ACQUIRE_LOCK_IX PAGE: 17:1:200;ACQUIRE_LOCK_X RID: 17:1:200:5
LOP_DELETE_ROWS LCX_HEAP dbo.tabelaDELETE HoBt 72057594038845440:ACQUIRE_LOCK_IX OBJECT: 17:2121058592:0 ;ACQUIRE_LOCK_IX PAGE: 17:1:200;ACQUIRE_LOCK_X RID: 17:1:200:6
LOP_DELETE_ROWS LCX_HEAP dbo.tabelaDELETE HoBt 72057594038845440:ACQUIRE_LOCK_IX OBJECT: 17:2121058592:0 ;ACQUIRE_LOCK_IX PAGE: 17:1:200;ACQUIRE_LOCK_X RID: 17:1:200:7
LOP_DELETE_ROWS LCX_HEAP dbo.tabelaDELETE HoBt 72057594038845440:ACQUIRE_LOCK_IX OBJECT: 17:2121058592:0 ;ACQUIRE_LOCK_IX PAGE: 17:1:200;ACQUIRE_LOCK_X RID: 17:1:200:8
LOP_DELETE_ROWS LCX_HEAP dbo.tabelaDELETE HoBt 72057594038845440:ACQUIRE_LOCK_IX OBJECT: 17:2121058592:0 ;ACQUIRE_LOCK_IX PAGE: 17:1:200;ACQUIRE_LOCK_X RID: 17:1:200:9
LOP_SET_FREE_SPACE LCX_PFS Unknown Alloc Unit NULL
LOP_COMMIT_XACT LCX_NULL NULL NULL
(15 row(s) affected)
przy 100 rekordach
truncate 19 wpisów
delete 105 wpisów
przy 1000 rekordach
truncate 23 wpisów
delete 1014 wpisów
7.
Przy poleceniu TRUNCATE TABLE nie można korzystać z wirtualnego widoku deleted
CREATE TABLE dbo.tabelaHISTORIA
(
idd INT ,
pole VARCHAR(50)
);
INSERT dbo.tabelaDELETE (pole)
SELECT 'x'
INSERT dbo.tabelaTRUNCATE(pole)
SELECT 'x'
GO
DELETE FROM dbo.tabelaDELETE
OUTPUT DELETED.* INTO dbo.tabelaHISTORIA
--------------------------------------
(1 row(s) affected)
SELECT * FROM dbo.tabelaHISTORIA
idd pole
----------- --------------------------------------------------
2 x
TRUNCATE TABLE dbo.tabelaTRUNCATE
OUTPUT DELETED.* INTO dbo.tabelaHISTORIA
--------------------------------------
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'OUTPUT'.
8.
Podczas TRUNCATE TABLE nie jest wyliczany żaden plan wykonania
SET SHOWPLAN_TEXT ON
GO
DELETE FROM dbo.tabelaDELETE
--------------------------------------
StmtText
-------------------------------
DELETE FROM dbo.tabelaDELETE
(1 row(s) affected)
StmtText
--------------------------------------------------------------------------------
|--Table Delete(OBJECT:([test].[dbo].[tabelaDELETE]), OBJECT:([test].[dbo].[tabelaDELETE].[ix_d]))
|--Top(ROWCOUNT est 0)
|--Index Scan(OBJECT:([test].[dbo].[tabelaDELETE].[ix_d]), ORDERED FORWARD)
(3 row(s) affected)
TRUNCATE TABLE dbo.tabelaTRUNCATE
-------------------------------
StmtText
------------------------------------
TRUNCATE TABLE dbo.tabelaTRUNCATE
(1 row(s) affected)
SET SHOWPLAN_TEXT OFF
9.
Polecenie DELETE zakłada blokady na wiersz
Polecenie TRUNCATE zawsze zakłada blokadę na tabelę i strony danych
Patrz wyniki zapytania
SELECT Operation,Context,AllocUnitName,[Lock Information],Description FROM fn_dblog (NULL, NULL);
W przypadku TRUNCATE TABLE zakładana jest blokada na wyłączność (X) na stronach 168,169, delete zgłasza intent lock (IX) dla tabeli i dla stron a blokady na wyłączność zakłada na poziomie wiersza (RID)od 0 do 9
Często pojawia się opinania, że TRUNCATE TABLE jest szybszy ponieważ nie jest logowany w dzienniku transakcji.
TRUNCATE TABLE jest logowane ale zapisywane są wyłącznie adresy stron które zostały zwolnione
TRUNCATE TABLE jest logowane ale zapisywane są wyłącznie adresy stron które zostały zwolnione
Podobnie jak operacje masowe nie można wycofać części polecenia
INSERT dbo.tabelaTRUNCATE(pole)
SELECT 'x'
GO
begin tran x
TRUNCATE TABLE dbo.tabelaTRUNCATE
select * from dbo.tabelaTRUNCATE
rollback tran x
GO
select * from dbo.tabelaTRUNCATE
--------------------------------------
(1 row(s) affected)
idd pole
----------- --------------------------------------------------
(0 row(s) affected)
idd pole
----------- --------------------------------------------------
1 x
(1 row(s) affected)
Brak komentarzy:
Prześlij komentarz