"Diffence between Delete And truncate in SQL Server"

Delete :

1. Delete is DML command  .

2. We can use where condition in Delete.

3. Delete retain the identity.

4. Slower then truncate because it keep logs

5.Triggers get fired in DELETE command

6. DELETE you can rollback data .

7. It deletes specified data if where condition exists

----------------------------------------------------------------------------

Truncatre:

1. Delete is DDL Command.

2. We can not use where condition in truncate .

3. If the table contains an identity column, the counter for that column is reset to the seed value that is defined for the column

4. Faster in performance wise, 

5. Triggers not fired in truncate command

6. It cannot rollback data(but it is possible).

7. It delete all data.

----------------------------------------------------------------------------

Question :TRUNCATE is much faster than DELETE.

Reason:When you type DELETE.all the data get copied into the Rollback Tablespace first.then delete operation get performed.Thatswhy when you type ROLLBACK after deleting a table ,you can get back the data(The system get it for you from the Rollback Tablespace).All this process take time.But when you type TRUNCATE,it removes data directly without copying it into the Rollback Tablespace.Thatswhy TRUNCATE is faster.Once you Truncate you cann't get back the data.

----------------------------------------------------------------------------------------------

Question : How to rollback the data when delete command execute?

Answer:

CREATE PROCEDURE [usp_delete_Pataint_master]
 (@PataintID [int],
 @BranchID [int]

)
AS
 BEGIN
 SET NOCOUNT ON
  BEGIN TRAN

  DELETE [dbo].[Pataint_master]
  WHERE ([BranchID] = @BranchID
  AND [PataintID]  = @PataintID)
  IF @@ERROR = 0
   COMMIT TRAN
  ELSE
   ROLLBACK TRAN
  RETURN (@@ERROR)
 END

GO