Database reckless modifications. ROLLBACK and COMMIT cure.
One day you might be forced to run some T-SQL scripts on a production database and accidentally delete/update all records in a table instead of specified in a where statement. It happened to me in the past it might happen to you as well. Usually, you think that the world is over. Then you remember about backup, but still, there is a possibility that some data would be lost forever. The best solution is as usual prevention of a disaster.
There is a reason why SSMS Tools pack by default creates the new queries:
BEGIN TRAN
...
ROLLBACK
Use transaction for all your modifications. Inside a transaction, you can test if your update/delete does exactly what you want it to do.
Here is a little illustration:
CREATE TABLE TestTable
(
Id int identity primary key ,
[Description] varchar(max)
)
GO
BEGIN TRAN
insert into TestTable
values
('Test1'),
('Test2'),
('Test3')
select * from TestTable -- First query, returns all records
delete TestTable
where Id=2
select * from TestTable -- Second query, returns two records
ROLLBACK -- COMMIT
select * from TestTable -- Third query, no records because of ROLLBACK
Results:
Query 1:
| 1 | Test1 |
|---|---|
| 2 | Test2 |
| 3 | Test3 |
Query 2:
| 1 | Test1 |
|---|---|
| 3 | Test3 |
Query 3:
After you are done with your testing, just change ROLLBACK to COMMIT.
Update:
BEGIN TRAN and ROLLBACK protects you from logical mistakes and syntax errors. However, some exceptions stop execution leaving the transaction open. For example:
BEGIN TRAN
CREATE TABLE Clients
(
Id INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50)
)
INSERT INTO Clients VALUES (1,'Viktar','Karpach')
INSERT INTO Clients_No_Such_Table VALUES (2,'Joe', 'Doe')
INSERT INTO Clients VALUES (3,'Mike', 'Skott')
SELECT * FROM Clients
ROLLBACK
Results:
(1 row(s) affected)
Msg 208, Level 16, State 1, Line 11
Invalid object name ‘Clients_No_Such_Table’.
As you can see SELECT statement was never executed:
SELECT * FROM Clients
SELECT @@TRANCOUNT
Results:
| Id | FirstName | LastName |
|---|---|---|
| 1 | Viktar | Karpach |
| (No column name) |
|---|
| 1 |
This SQL Server behavior can be fixed by using SET XACT_ABORT ON, so a default query snippet becomes:
SET XACT_ABORT ON
BEGIN TRAN
...
ROLLBACK
Or you can use a longer version snippet with BEGIN TRY / END TRY:
BEGIN TRY
BEGIN TRANSACTION
SET NOCOUNT ON
...
--ROLLBACK
COMMIT TRAN -- Transaction Success!
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRAN --RollBack in case of Error
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
-- Use RAISERROR inside the CATCH block to return
-- error information about the original error that
-- caused execution to jump to the CATCH block.
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
END CATCH