1

I am starting a transaction and executing multiple truncate table statements but if last fails then it rolls back all truncate. I am trying to depict the same behavior on SQL Server command prompt.

I am trying to execute following SQL statements in SQL Server 2012:

create table pkt1(id integer)
GO

create table pkt2(id integer)
GO

insert into pkt1 values(10)
insert into pkt1 values(20)

insert into pkt2 values(10)
insert into pkt2 values(20)

select * from pkt1

Output:

id
------
10
20


select * from pkt2

Result:

id
------
10
20


begin transaction
   truncate table pkt1;
   truncate table pkt2;
   truncate table pkt3;
   commit;

Error:

Msg 4701, Level 16, State 1, Server PNI6W11198, Line 4
Cannot find the object "pkt3" because it does not exist or you do not have permissions.

select * from pkt2

Output:

id
------
10
20

select * from pkt1

Output:

id
-----
10
20

TRUNCATE is supposed to be Transact SQL and should commit earlier truncate for pkt1 and pkt2. But it does rollback for all.

Is this expected behavior?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
TechGuy
  • 27
  • 7
  • Truncate can be rolled back IF it is contained in the Transaction scope and the session is not closed. – Nandish B Nov 15 '17 at 12:05
  • I believe it is because the error happen before the commit. You've started a transaction block which means that nothing gets committed until commit is called. see https://stackoverflow.com/questions/1522931/truncate-table-within-transaction – Jaques Nov 15 '17 at 12:05
  • 1
    ***YES*** this is expected behavior - that is how a **transcation** works - and is supposed to work. Nothing wrong here (except possibly your expectations) – marc_s Nov 15 '17 at 12:29
  • I thought being TRUNCATE a Transact SQL it would behave like a DDL and would do Auto commit after each sql execution – TechGuy Nov 16 '17 at 05:24

1 Answers1

0

Table pkt3 does not exist so that script fails.

Your transaction brackets all three truncate statements : one fails - they all fail

Peter Smith
  • 5,528
  • 8
  • 51
  • 77