How to prevent accidental data loss from executing a query in SQL Server aka “Practicing safe coding”

We sometimes may find ourselves in a stressful situation at work, where for example we need to update or delete some records in our database. We’ve all been there. Right after we click that “Execute” button, we realize we forgot to include a WHERE clause and the entire table is wiped instead of only one row. Although things like this can happen to the best of us, we can plan ahead and take preventative measures to make sure, we don’t get negatively impacted by the consequences of such a mistake.

A good practice would be to always write a SELECT statement with a WHERE clause first and execute it to see the number of rows that will be affected. Then, just execute UPDATE or DELETE statement with the same WHERE clause. But we’ll explore some other tips and tricks for “safe SQL coding”.

Let’s take a look at two options in SQL Server Management Studio (SSMS) to prevent accidental data modification/loss from happening.

Implicit Transactions

Let’s take a look at one option in SQL Server to prevent accidental data modification/loss from happening.

When a connection is operating in implicit transaction mode, the instance of the SQL Server Database Engine automatically starts a new transaction after the current transaction is committed or rolled back.

In SSMS there is an option to set Implicit Transactions “ON” in SQL Server options under Tools>Options>Query Execution>SQL Server>ANSI:

To view the current setting for IMPLICIT_TRANSACTIONS, run the following query:

DECLARE @IMPLICIT_TRANSACTIONS VARCHAR(3) = 'OFF';  
IF ( (2 & @@OPTIONS) = 2 ) SET @IMPLICIT_TRANSACTIONS = 'ON';  
SELECT @IMPLICIT_TRANSACTIONS AS IMPLICIT_TRANSACTIONS;

Quick tip: After setting IMPLICIT_TRANSACTIONS ON, always open a new query window as the setting is applied only for newly open connections, and not for the current one

Let’s delete some data from Sales.Customer table in AdventureWorks2014 database. Open a new query, and run only the SELECT statement first to see what we are about to delete:

Now, run also the DELETE statement, and note that CustomerID 1 is deleted:

If the Implicit Transactions option is switched on, and the query window is simply closed, any open transactions will be rolled back (SSMS issues an IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION statement in the background). The obvious downside of this method is that a user might forget to add a COMMIT to apply the changes at the end. The worst-case scenario could be if changes are committed by someone else who is working on the same database in the same environment, and who will add COMMIT at the end of every script by default.

There are two ways to check if any uncommitted transactions are pending. One is simply by executing the @@TRANCOUNT statement which will return an integer of BEGIN TRANSACTION statements that have occurred on the current connection:

Quick tip: The SELECT @@TRANCOUNT statement returns the number of active transactions for current connection only

Another way is by exiting SSMS in which case a prompt window will appear informing the user that there are some uncommitted transactions pending:

At this point, the user can either commit these transactions or roll them back. Either way, this method is not particularly popular no practical.

DML Triggers

Another method is by creating DML prevention triggers on important tables. These triggers fire when a user tries to modify data through a data manipulation language (DML) event such as INSERT, UPDATE, or DELETE statements on a table.

As an example, let’s create a simple delete trigger on Sales.Customer table that will prevent the user from deleting all rows in the table:

USE AdventureWorks2014
GO

CREATE TRIGGER [Sales].[uNoDeleteAll] 
ON [Sales].[Customer] 
FOR DELETE AS 
BEGIN
     DECLARE @Count int
     SET @Count = @@ROWCOUNT;
         
     IF @Count >= (SELECT SUM(row_count)
         FROM sys.dm_db_partition_stats 
         WHERE OBJECT_ID = OBJECT_ID('Sales.Customer' ) 
         AND index_id = 1)
     BEGIN
         RAISERROR('Cannot delete all rows',16,1) 
         ROLLBACK TRANSACTION
         RETURN;
     END
END
GO

Previously, there was no triggers defined on the Sales.Customer table. After refreshing the triggers, the new one is listed:

The good thing about this trigger is that it will allow the user to delete a single row or even more as long as there is a WHERE clause:

However, if the WHERE clause is not specified in the statement the trigger will fire and prevent this action from happening:

Although this method looks promising, it’s far away from ideal solution, as creating a prevention trigger for each table in a database could be a long process depending on database complexity and number of objects.

Execution alerts

ApexSQL Complete is a productivity add-in that integrates seamlessly into both SSMS and VS, and helps users write code faster with its neat features, and much more.

Introduced in ApexSQL Complete R7, the Execution alerts feature has the main goal to provide users ability to define rules for query execution that can prevent data modification/loss.

ApexSQL Complete comes with five built-in alerts and they are located under the Execution alerts tab in the Options dialog:

Each of the built-in rules covers a specific case scenario with a different set of rules.

If we execute the DELETE statement without a WHERE clause as before, the warning message will pop-up. At this point, the user has an option to simply click the Cancel button, and stop the query from executing and more importantly from wiping all records in the Sales.Customer table:

If a query is more complex and has many lines of code, the Execution alert message will display the exact line number within the code where the rule is triggered:

This way, the user knows exactly where to look in the code. On an execution of a query, multiple rules can be triggered simultaneously, showing the line numbers for each rule.

Quick tip: If Line numbers option is not turned on in SSMS, press Ctrl + G to open Go To Line dialog box, and enter a particular line number to go to in T-SQL code

Note that no specific setup from the user was needed for using the DELETE statement with no WHERE clause. This case scenario is covered by one of the built-in rules in ApexSQL Complete.

The user has ability to create new rules, or edit existing ones, even the built-in rules. In other words, the user has ability to customize type, condition and value for all alerts:

Unlike triggers in the previous example, the user doesn’t have to create them on each object individually. By default, all rules are set to all databases on all servers.

Execution alert rules can be shared between developers by exporting them from one machine to another. This is easily done by using the export/import option under the Execution alerts tab in the Options dialog:

To copy Execution alert rules from one machine to another, simply click the Export button and the Save As dialog will appear. By default, the location where the Alerts.zip file will be saved is: C:\Users\<user_name>\Documents\ApexSQL\ApexSQLComplete, but it can be changed for example to a network shared folder where it will be available to everyone in a company:

From there, on a machine where ApexSQL Complete is already installed, just use the Import button, locate and open the previously saved Alerts.zip file.

For more information on SQL coding execution alerts please see the article: Execution alerts

Test mode

Another neat ApexSQL Complete feature is the Test mode which allows executing a query without making any changes to a database.

Before executing a query, make sure that Test mode is enabled by clicking the Test mode button from the SSMS toolbar:

Another way to enable the Test mode is through ApexSQL main menu, as well as by using the combination of keys (Ctrl+Alt+Q) on the keyboard:

Quick tip: If ApexSQL Complete toolbar is not visible in SSMS, go to View -> Toolbars and make sure that ApexSQL Complete is checked in the sub menu

When Test mode is enabled, SSMS’s status bar is colored red. This means that any executed query will not make any changes to a database’s actual structure:

The Test mode feature adds the BEGIN TRANSACTION and the ROLLBACK TRANSACTION statements in the code, and rolls back the transaction to the beginning to roll back all modifications made from the beginning of the transaction.

As before, when the DELETE statement with no WHERE clause is executed in the Test mode, the Result grid shows that 19820 rows are ”deleted”:

However, a simple SELECT statement returns all data from the table and shows that nothing is actually affected:

As we said in the very beginning of this article, instead of writing a SELECT query to see the number of rows that will be affected, you could use the Test mode when executing important queries.

Useful links:

• SET IMPLICIT_TRANSACTIONS (Transact-SQL)
• CREATE TRIGGER (Transact-SQL)
• BEGIN TRANSACTION (Transact-SQL)
• ROLLBACK TRANSACTION (Transact-SQL)