Best practices to delete a set of tables in sql 2008

Question

Basically I want to keep the transaction very simple but I should be able to rollback if any error occurs in the later part. Something like:

BEGIN TRANSACTION

  DELETE SET 1(this will delete first set of table)
  COMMIT

  DELETE SET 2 (will delete second set of table)

If any error occurs while deleting set 2 I should be able to rollback set 1 transaction as well. Let me know if we have any options to do like this. Appreciate for your help.

Answer 1

If any error occurs while deleting set 2 i should be able to rollback set 1 transaction as well.Let me know if we have any options to do like this. Appreciate for your help.

Then, why don't you do this?

BEGIN TRY
   BEGIN TRANSACTION    -- Start the transaction

   DELETE SET 1(this will delete first set of table)
   DELETE SET 2 (will delete second set of table)

   -- If we reach here, success!
   COMMIT
END TRY
BEGIN CATCH
  -- Whoops, there was an error
  IF @@TRANCOUNT > 0
     ROLLBACK

  -- Raise an error with the details of the exception
  DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
  SELECT @ErrMsg = ERROR_MESSAGE(),
         @ErrSeverity = ERROR_SEVERITY()

  RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH

Read here for a complete explanation.

Answer 2

If you mean deleting tables as in DELETE TABLE (I.e. the table, not the content), you are out of luck - DDL is not transacted.