Friday, June 3, 2011

DELETE and TRUNCATE behavior

We will create a student table then apply Delete and Truncate within Transaction and rollback. Leave your comment about the behavior and/or output you expect.
--Create student table
CREATE TABLE [DBO].[TTB_STUDENT]
(
ID INT IDENTITY(1, 1) PRIMARY KEY
,
NAME VARCHAR(100
) NULL,
AGE INT
NULL,
ACTIVE CHAR(1
))

--Insert some records
INSERT INTO [DBO].[TTB_STUDENT]
VALUES
('Mark', 32, 'Y'
),
(
'Elaine', 32, 'Y'
),
(
'Elton', 32, 'Y'
),
(
'John', 32, 'Y'
),
(
'Smith', 32, 'Y'
)

--Delete records using DELETE
--Rollback to bring back the records
--You can because it write to the log and
--can be rolled back
BEGIN TRANSACTION
DELETE
[DBO].[TTB_STUDENT]
ROLLBACK

--Select table
SELECT * FROM [DBO].[TTB_STUDENT]
Here is the output of the above select - 
image


--Do the same but use TRUNCATE TABLE
--Note that it does not write to log by definition
BEGIN TRANSACTION
TRUNCATE TABLE
[DBO].[TTB_STUDENT]
ROLLBACK

--What do you expect ??
SELECT * FROM [DBO].[TTB_STUDENT]

2 comments:

  1. Get the values back. same as delete statement

    ReplyDelete
  2. Yes, I had different view about truncate and expecting 0 rows.

    ReplyDelete