Monday, May 17, 2010

Delete operation in SQL Server

How does Delete operation works..

Delete operation is one of the slowest operation in Sql Server..
Why?
Because all constraint are checked, than a Lock is requested, further escalated, to log the operation /records before the actual operation gets completes logically..
Physically still there is miles to go before the page gets actually de allocated after actual check pointing occurs..

Today we where trying to delete 5 records from one of the master table which has constraint with more than 300 tables..
Initially we started deleting one by one, it took around 6 minutes for 1 record, than thought why not delete using  in clause and do you know the time taken!!
It was exactly the multiple of 6 and the number of keys in the in clause..


Truncate is faster than Delete and most of us know its not logged..
Now the Question:- Is Truncate a Log operation?

Answer is yes..
Truncate is a logged operation. As Truncate is a DDL operation the allocated pages will just be de-allocated and the de-allocation of the pages will get logged.one can rollback the truncate operation using explicit transaction.

Example
CREATE TABLE [dbo].[emp_tab1](
[ename] [varchar](25) NULL,
[ecode] [varchar](9) NULL,
[ejob] [varchar](15) NULL,
[esal] [int] NULL,
[edept] [int] NULL,
[ehiredate] [datetime] NULL,
[ecity] [varchar](20) NULL
) ON [PRIMARY]

INSERT INTO [Faculty ].[dbo].[emp_tab1]
([ename]
,[ecode]
,[ejob]
,[esal]
,[edept]
,[ehiredate]
,[ecity])
VALUES
(
,
,
,
,
,
,)
GO


begin transaction

truncate table emp_tab1
select * from emp_tab1
rollback transaction
select * from emp_tab1





Does your delete operation really deletes pages too..
Try the real test below..

1 comment:

Anonymous said...

Hi Anjani
Even I experienced the same thhing while deleting the using the IN clause.I think thats a good find Anjani.Thanks for sharing this useful information.Keep it up!!

Pankaj

Recent Posts