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 -
--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]
Get the values back. same as delete statement
ReplyDeleteYes, I had different view about truncate and expecting 0 rows.
ReplyDelete